Re: Writing WAL files

[Peter J. Holzer]

On 2020-10-05 11:29:04 -0600, Michael Lewis wrote:
> 
> I suggest that in PG12 you can monitor the
> "lag" of a standby server more directly by looking at columns
> write_lag,
> flush_lag, replay_lag in the pg_stat_replication view. 
> 
> 
> And are those things updated when there are no changes to the master
> database?

Probably not, as there is nothing to replicate, so no new data it
provided.


> If you setup a scripted process to update a single row with a timestamptz on
> the source/primary every minute, then you have a very simple consistent change
> and also a way to check on the replica what is current time vs
> last_scripted_update_time if you will and know the approx lag. It would seem
> like a simple albeit hacky solution to you wanting a file every X minutes
> regardless of server activity.

It also has the advantage that you don't have to wait for the WAL file
to be written. You can just check whether the change appears on the
replicas. About 2 years ago I wrote a Nagios/Icinga check that does
that: Update a timestamp in a table on the master, then connect to all
the replicas and wait for the change to show up on them. It then reports
the lag for each replica and a final status (OK, WARNING, CRITICAL)
based on the maximal lag.

I think I wrote it because the PostgreSQL version we were using at the
time didn't have the lag columns yet, but it does have the advantage of
providing an end to end check (do I really get the correct value?), not
the database's idea of whether replication is working.

(The check is written in Go and buried in a svn repo at work, but I
could publish it if there is interest)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: What's your experience with using Postgres in IoT-contexts?

[Peter J. Holzer]

On 2020-10-05 11:20:20 +0200, Thorsten Schöning wrote:
> Does anyone actually use Postgres with ARM based low performance
> hardware and only 256 MiB of RAM?
[...]
> Background:
> 
> I have some server side backend exposing web services and storing data
> in Postgres. The stored data are telegrams from smart metering
> devices, lots of them, mostly small. The provided web services take
> care of generating reports etc. based on those telegrams.
[...]
> In the easiest case, the newly created device would only need to store
> some hundreds of telegrams per day in some known time window and
> forward those telegrams afterwards. In most cases it would be those of
> the same day only, but might be those of the last X few days as well
> in case of problems. In the long term, though, that device should be
> able to store telegrams for years and might generate reports on it's
> own as well.

Do you plan to move some of that reporting to the IoT devices? (Maybe
equip them with a display with a dashboard, or something like that)

Because for simply storing a few hundred records which are written and
read sequentially, an RDBMS of any kind seems overkill. I'd simply write
them to files.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: What's your experience with using Postgres in IoT-contexts?

[Thorsten Schöning]

Guten Tag Peter J. Holzer,
am Samstag, 10. Oktober 2020 um 10:56 schrieben Sie:

> Do you plan to move some of that reporting to the IoT devices? (Maybe
> equip them with a display with a dashboard, or something like that)

Not necessarily with a display, but something like a dashboard for a
web-UI on the device itself definitely needs to be kept in mind.
Though, some display has been considered as well.

> Because for simply storing a few hundred records which are written and
> read sequentially, an RDBMS of any kind seems overkill. I'd simply write
> them to files.

That's still considered as well and what's done right now. But at some
point we need to maintain individual logical devices where those
telegrams come from anyway. It as well has been considered to store
only metadata in the database and telegrams in files, but as telegrams
are small mostly, this doesn't seem worth it. And because telegrams
are small, we would need some kind of container format anyway to not
run out of inodes and stuff like that too easily.

Mit freundlichen Grüßen,

Thorsten Schöning

-- 
Thorsten Schöning   E-Mail: thorsten.schoen...@am-soft.de
AM-SoFT IT-Systeme  http://www.AM-SoFT.de/

Telefon...05151-  9468- 55
Fax...05151-  9468- 88
Mobil..0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

Re: How to migrate column type from uuid to serial

[Peter J. Holzer]

On 2020-10-07 20:10:34 +0530, Hemil Ruparel wrote:
> Sorry if this is silly but if it is a 128 bit number, why do we need 32
> characters to represent it? Isn't 8 bits one byte?

Yes, 8 bits are 1 byte. But that's 256 different values, so to display
them in 1 character you would need 256 different characters. That's not
possible in ASCII (ASCII has only 94 graphic characters), and even if
you included accented characters and other alphabets (like Greek or
Cyrillic) it would be hard to read. So the decision was to display each
byte as a pair of two hexadecimal digits (because 16 * 16 = 256). They
could also have used 3 decimal digits (000 - 255) for each byte, but
that would have wasted even more space, or they could have used base 32
or 64 for the whole number, but that would make conversion harder.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: What's your experience with using Postgres in IoT-contexts?

[Peter J. Holzer]

On 2020-10-10 11:22:42 +0200, Thorsten Schöning wrote:
> Guten Tag Peter J. Holzer,
> am Samstag, 10. Oktober 2020 um 10:56 schrieben Sie:
> 
> > Do you plan to move some of that reporting to the IoT devices? (Maybe
> > equip them with a display with a dashboard, or something like that)
> 
> Not necessarily with a display, but something like a dashboard for a
> web-UI on the device itself definitely needs to be kept in mind.
> Though, some display has been considered as well.

Yeah, then it makes sense to use a database.


> > Because for simply storing a few hundred records which are written and
> > read sequentially, an RDBMS of any kind seems overkill. I'd simply write
> > them to files.
> 
> That's still considered as well and what's done right now. But at some
> point we need to maintain individual logical devices where those
> telegrams come from anyway. It as well has been considered to store
> only metadata in the database and telegrams in files,

I wasn't thinking of storing files in addition to the database, but
ditching the database completely. Since what you were describing is a
simple queue of telegrams waiting to be uploaded to a server and that
queue was expected to be relatively short (a few hundred to a few
thousand entries) I would do it like this:

Each telegram is written to a unique file in a directory "upload-queue". 
Periodically (or triggered by inotify) the uploader checks if there is
something to upload. After it has successfully uploaded a telegram, it
deletes the file.

Very simple and robust.

> And because telegrams are small, we would need some kind of container
> format anyway to not run out of inodes and stuff like that too easily.

Not necessarily. You would have to allocate the appropriate number of
inodes, of course. Using one file per telegram wastes some space, but
for a few thousand telegrams that may not matter[1]. Indeed, with ext4 you
could make the inodes large enough that all or most of your telegrams
fit inside the inode and don't need an extra data block at all.

But that's just if you *only* need a simple queue. If you want to keep
your data in a database for other reasons, you can use it for the queue
as well.

hp

[1] I have no idea what the smallest flash chips are these days.

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: How to migrate column type from uuid to serial

[Peter J. Holzer]

On 2020-10-10 11:31:23 +0200, Peter J. Holzer wrote:
> On 2020-10-07 20:10:34 +0530, Hemil Ruparel wrote:
> > Sorry if this is silly but if it is a 128 bit number, why do we need 32
> > characters to represent it? Isn't 8 bits one byte?
> 
> Yes, 8 bits are 1 byte. But that's 256 different values, so to display
> them in 1 character you would need 256 different characters. That's not
> possible in ASCII (ASCII has only 94 graphic characters), and even if
> you included accented characters and other alphabets (like Greek or
> Cyrillic) it would be hard to read.

I'm showing my European bias here. 

I should have thought of Korean. The Hangul script is syllabic with a
very straightforward and easy to learn structure. Wikipedia tells me
that they have 19 consonants and 21 vowels, so you could just pick 16
consonants and 16 vowels to construct 256 syllables. That would even
make UUIDs pronounceable.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: What's your experience with using Postgres in IoT-contexts?

[Thorsten Schöning]

Guten Tag Peter J. Holzer,
am Samstag, 10. Oktober 2020 um 12:00 schrieben Sie:

> Not necessarily. You would have to allocate the appropriate number of
> inodes, of course. Using one file per telegram wastes some space, but
> for a few thousand telegrams that may not matter[1]. Indeed, with ext4 you
> could make the inodes large enough that all or most of your telegrams
> fit inside the inode and don't need an extra data block at all.

Thanks for suggesting what I had noted at least worth discussing as
well already and what "other interested parties" said to be a really
stupid idea. Doesn't seem that stupid to us two obviously, especially
if ext4 is already used anyway. ;-)

But it's really not only about a simple queue: I want to have long
term storage and access to individual telegrams according different
criteria like individual devices, when received etc.

Mit freundlichen Grüßen,

Thorsten Schöning

-- 
Thorsten Schöning   E-Mail: thorsten.schoen...@am-soft.de
AM-SoFT IT-Systeme  http://www.AM-SoFT.de/

Telefon...05151-  9468- 55
Fax...05151-  9468- 88
Mobil..0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

Re: How to migrate column type from uuid to serial

[Hemil Ruparel]

oh. I get it now. Thanks


On Sat, Oct 10, 2020 at 3:41 PM Peter J. Holzer  wrote:

> On 2020-10-10 11:31:23 +0200, Peter J. Holzer wrote:
> > On 2020-10-07 20:10:34 +0530, Hemil Ruparel wrote:
> > > Sorry if this is silly but if it is a 128 bit number, why do we need 32
> > > characters to represent it? Isn't 8 bits one byte?
> >
> > Yes, 8 bits are 1 byte. But that's 256 different values, so to display
> > them in 1 character you would need 256 different characters. That's not
> > possible in ASCII (ASCII has only 94 graphic characters), and even if
> > you included accented characters and other alphabets (like Greek or
> > Cyrillic) it would be hard to read.
>
> I'm showing my European bias here.
>
> I should have thought of Korean. The Hangul script is syllabic with a
> very straightforward and easy to learn structure. Wikipedia tells me
> that they have 19 consonants and 21 vowels, so you could just pick 16
> consonants and 16 vowels to construct 256 syllables. That would even
> make UUIDs pronounceable.
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

Re: Writing WAL files

[Cory Nemelka]

On Sat, Oct 10, 2020 at 3:41 AM Peter J. Holzer  wrote:

> On 2020-10-05 11:29:04 -0600, Michael Lewis wrote:
> >
> > I suggest that in PG12 you can monitor the
> > "lag" of a standby server more directly by looking at columns
> > write_lag,
> > flush_lag, replay_lag in the pg_stat_replication view.
> >
> >
> > And are those things updated when there are no changes to the master
> > database?
>
> Probably not, as there is nothing to replicate, so no new data it
> provided.
>
>
> > If you setup a scripted process to update a single row with a
> timestamptz on
> > the source/primary every minute, then you have a very simple consistent
> change
> > and also a way to check on the replica what is current time vs
> > last_scripted_update_time if you will and know the approx lag. It would
> seem
> > like a simple albeit hacky solution to you wanting a file every X minutes
> > regardless of server activity.
>
> It also has the advantage that you don't have to wait for the WAL file
> to be written. You can just check whether the change appears on the
> replicas. About 2 years ago I wrote a Nagios/Icinga check that does
> that: Update a timestamp in a table on the master, then connect to all
> the replicas and wait for the change to show up on them. It then reports
> the lag for each replica and a final status (OK, WARNING, CRITICAL)
> based on the maximal lag.
>
> I think I wrote it because the PostgreSQL version we were using at the
> time didn't have the lag columns yet, but it does have the advantage of
> providing an end to end check (do I really get the correct value?), not
> the database's idea of whether replication is working.
>
> (The check is written in Go and buried in a svn repo at work, but I
> could publish it if there is interest)
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

I would be interested in the Nagios/Icinga check you wrote.

Strange behavior

[Olivier Leprêtre]

Hi,



I’m surprised by this behavior I noticed in pgadmin3 and postgresql 9.6



Suppose those two tables

create table test1 (v1 text)

create table test2 (v2 text)

insert into test1 (v1) values ('A')

insert into test2 (v2) values ('B')



query select v1 from test2 return v2 column doesn’t exists. Right.



But the following query does not return any error but only an empty result
set.

select v1 from test1 where v1 not in (select v1 from test2)



In other words, a wrong query returns a valid result. This happens because
v1 is a column from test1, (select vx from test2) will return an error as
expected.



Did I missed something ?



Thanks,



Olivier



--
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus

Re: Strange behavior

[Francisco Olarte]

Olivier:

On Sat, Oct 10, 2020 at 6:13 PM Olivier Leprêtre  wrote:

> I’m surprised by this behavior I noticed in pgadmin3 and postgresql 9.6
...

> select v1 from test1 where v1 not in (select v1 from test2)


This is called a correlated subquery ( google and search for it, it is
even in wikipedia ). It has many uses.

Basically, it refers to the v1 from the outside query.

Get in the habit of using (potentially aliased ) column names whenever
you have any moderately complex query, i.e. if the inner v1 would have
been v2 ( due to a typo ), writing your query as :

select t1.v1 from test1 as t1 wher t1.v1 not in ( select t2.v1 from
test2 as t2 )

Would have caught it.

Francisco Olarte.

Re: Strange behavior

[David G. Johnston]

On Sat, Oct 10, 2020 at 9:13 AM Olivier Leprêtre 
wrote:

This has nothing to do with pgAdmin, or any other client interface.

In other words, a wrong query returns a valid result. This happens because
> v1 is a column from test1, (select vx from test2) will return an error as
> expected.
>

https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F
I am pondering trying to get the FAQ entry incorporated into the actual
documentation.

David J.

Re: Strange behavior

[Tom Lane]

"David G. Johnston"  writes:
> I am pondering trying to get the FAQ entry incorporated into the actual
> documentation.

Not sure how much it'd help, but we have warnings against mistakes
that are far less common than this one, so sure why not.

Taking a quick gander at the docs, it seems like the thing to do would be
to add something to the head text in 9.23. Subquery Expressions, and then
remove any duplicative statements from the individual 9.23.x subsections.

regards, tom lane

Re: Gurjeet Singh Index Adviser User Interface

[Jony Cohen]

Hi Yessica,
Postgres 8.3 is very old, If you are using that simply because of the index 
advisor there are a few more “modern” options.

I at the time wrote such a tool for Postgres 9.4 (it has many features that the 
one by Gurjeet didn’t have like CTE and partial indexes support)
Alas I didn’t have a chance to update it to 9.6 & 10 as there were many 
breaking changes.
https://github.com/cohenjo/pg_idx_advisor 


And there is even a newer module to generate hypothetical indexes to allow you 
to test various options before building the actual index: 
https://github.com/HypoPG/hypopg  

You are missing so much functionality and features that any improvements you 
get by a better index recommendation will still be slower then running a new 
version with less optimal indexes.

I would consider using a new version.

Kind Regards,
Jony

> On 10 Oct 2020, at 3:19, Yessica Brinkmann  
> wrote:
> 
> I will greatly appreciate a help please with this topic. I really need a lot 
> to be able to use this interface to be able to test my thesis well. I really 
> don't quite understand what installing postgresql-server-dev-X.Y refers to. 
> And I really don't really know how to install it too. If you can give me a 
> guide at least about this please? And in which version should I install it? I 
> am using Postgresql 8.3.23, I really use this version because the Index 
> Adviser only works with this version of Postgresql. And also I do not 
> understand well if installing that already solves everything or should I 
> install an additional package?
> Best regards,
> Yessica Brinkmann.
> 
> 
>  
> 
>  Libre de virus. www.avg.com 
> 
>  
> El jue., 8 oct. 2020 a las 22:21, Yessica Brinkmann 
> (mailto:brinkmann.yess...@gmail.com>>) escribió:
> I also clarify that I tried to install the libpq-dev package already but 
> could not install it because it depends on a non-installable package called 
> libss10.9.8. And I had to apply apt --fix-broken install to fix the problem.
> 
>  
> 
>  Libre de virus. www.avg.com 
> 
>  
> El jue., 8 oct. 2020 a las 22:08, Yessica Brinkmann 
> (mailto:brinkmann.yess...@gmail.com>>) escribió:
> Goodnight,
> I apologize please that today I was able to test the directory, since I had a 
> health problem in recent days.
> Well, I actually found the files corresponding to the pg_advise_index 
> interface. But I was surprised that the executable is not present, but 
> apparently this interface must be compiled by another part of the index 
> adviser, to be able to execute it. It has a Makefile.
> I tried to compile as follows, and I get the following errors:
> root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
>  USE_PGXS = 1 make
> You need to install postgresql-server-dev-X.Y for building a server-side 
> extension or libpq-dev for building a client-side application.
> make: *** There are no targets. High.
> root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
>  make install
> make: Nothing is done for 'install'.
> I will greatly appreciate a help please, regarding these errors that I got. 
> The truth is that I honestly  don't have much experience using Linux.
> Best regards,
> Yessica brinkmann
> 
>  
> 
>  Libre de virus. www.avg.com 
> 
>  
> 
> El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann 
> (mailto:brinkmann.yess...@gmail.com>>) escribió:
> Goodnight,
> Thank you very much for the answer.
> I followed all the installation instructions: apply patch, compile
> install postgres, etc.
> I just tried everything with the UI ii) Manually (through psql session), 
> which is also mentioned in the readme, and everything works for me. But to 
> better test my thesis, I would also need to use the other user interface 
> mentioned in the readme and that is precisely i) pg_advise_index tool.
> I understand what you are telling me about the directory. I will be testing 
> this way.
> Many thanks.
> Best regards,
> Yessica brinkmann
> 
> El mar., 29 sept. 2020 a las 18:21, Rob Sargent ( >) escribió:
> 
> 
> On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> > I will greatly appreciate a help with this topic please. I really need 
> > to use that in