NOTIFY command impact

I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk to pgsql, the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wired to both). [sorry if Wired is not the correct term, that is what I was assigned to do… so I am asking here].

URL?

I have an example of coding this in python, but only does like 5 commands. w/o documentation of where they found the specs.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/21/2017 01:07 PM, Rob Brucks wrote:

Hi All,

I just wanted to check with you guys to make sure that constantly
issuing "NOTIFY" commands without corresponding "LISTEN" commands will
not cause any harm or excessive work for the PostgreSQL cluster. Nothing
that would put my cluster at risk.

The reason I ask is because I was trying to implement a reliable method
of monitoring replication lag for streaming replication on 9.2+ systems
using the following SQL on slaves:

select extract(epoch from now() - pg_last_xact_replay_timestamp());

This SQL provides me with a time-based measure of replication lag
instead of a byte-based measure. Time-based lag measurement is more
meaningful for us in time-sensitive applications.

During my testing I noticed that if the database went "quiet" (no update
activity on the master) for a period of time, then the last replay
timestamp remained unchanged. Having little or no update activity
after-hours is very common on our smaller systems.

This made the monitoring of replication lag inconsistent because,
despite the slave being "caught up" with the master, it was reporting an
increasing time lag. And I didn't want our DBAs to get false alerts
from our monitoring.

So I went on the hunt for a method of forcing replay to occur without
actually performing any database updates. I also did not want to grant
any kind of update capability on the database to my monitoring role, for
tighter security.

I discovered that the monitoring role, despite not having any update
permissions, could successfully issue a "NOTIFY" command to a bogus
channel and that this command actually forced the log to replay on the
slave, updating the replay timestamp. This seems like a viable solution
to my problem.

My plan is to have the monitoring role issue a "NOTIFY" every 30 seconds
to ensure the timestamp is updated at least that frequently. But there
will not be an associated "LISTEN" for these notifications.

However, I don't want to cause any problems for the PostgreSQL cluster
itself by having messages inserted with nobody to listen for them, which
is why I'm posting here.

Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?

Depending on how long 'long term' is:

https://www.postgresql.org/docs/9.6/static/sql-notify.html

"There is a queue that holds notifications that have been sent but not
yet processed by all listening sessions. If this queue becomes full,
transactions calling NOTIFY will fail at commit. The queue is quite
large (8GB in a standard installation) and should be sufficiently sized
for almost every use case. However, no cleanup can take place if a
session executes LISTEN and then enters a transaction for a very long
time. Once the queue is half full you will see warnings in the log file
pointing you to the session that is preventing cleanup. In this case you
should make sure that this session ends its current transaction so that
cleanup can proceed."

Thank you,

Rob Brucks

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ozz Nixon <ozznixon@gmail.com> writes:

I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk to pgsql, the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wired to both). [sorry if Wired is not the correct term, that is what I was assigned to do… so I am asking here].
URL?

https://www.postgresql.org/docs/current/static/protocol.html

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2/21/2017 1:12 PM, Ozz Nixon wrote:

I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk to pgsql, the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wired to both). [sorry if Wired is not the correct term, that is what I was assigned to do… so I am asking here].

URL?

I have an example of coding this in python, but only does like 5 commands. w/o documentation of where they found the specs.

dare I ask why you can't use libpq or another existing language binding
? writing a custom binding ties you down to a lifetime of tracking
version updates...

if you really must, start here,
https://www.postgresql.org/docs/current/static/protocol.html

then look at the source to libpq, and/or pg-jdbc, and/or PyGreSQL and/or
Psycopg to work out implementation details.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/21/2017 01:07 PM, Rob Brucks wrote:

Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?

Depending on how long 'long term' is:

https://www.postgresql.org/docs/9.6/static/sql-notify.html

"There is a queue that holds notifications that have been sent but not yet
processed by all listening sessions

​Its not clear in the OP that this is the case (it seems to be) but the
documentation is non-specific as to what happens when "# of listeners" = 0;
I suspect that said messages are created and then immediately discarded -
though apparently they do make it over to the standby server​ too - and
likely also immediately discarded there as well.

David J.

In my case the monitoring user will be connecting, issuing the notify, then immediately disconnecting.

And we don't have any systems using listen/notify.

So I'm hoping there won't be a problem.

That's why I'm asking ☺

--Rob

On 2/21/17, 3:17 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:

On 02/21/2017 01:07 PM, Rob Brucks wrote:

Hi All,

I just wanted to check with you guys to make sure that constantly
issuing "NOTIFY" commands without corresponding "LISTEN" commands will
not cause any harm or excessive work for the PostgreSQL cluster. Nothing
that would put my cluster at risk.

The reason I ask is because I was trying to implement a reliable method
of monitoring replication lag for streaming replication on 9.2+ systems
using the following SQL on slaves:

select extract(epoch from now() - pg_last_xact_replay_timestamp());

This SQL provides me with a time-based measure of replication lag
instead of a byte-based measure. Time-based lag measurement is more
meaningful for us in time-sensitive applications.

During my testing I noticed that if the database went "quiet" (no update
activity on the master) for a period of time, then the last replay
timestamp remained unchanged. Having little or no update activity
after-hours is very common on our smaller systems.

This made the monitoring of replication lag inconsistent because,
despite the slave being "caught up" with the master, it was reporting an
increasing time lag. And I didn't want our DBAs to get false alerts
from our monitoring.

So I went on the hunt for a method of forcing replay to occur without
actually performing any database updates. I also did not want to grant
any kind of update capability on the database to my monitoring role, for
tighter security.

I discovered that the monitoring role, despite not having any update
permissions, could successfully issue a "NOTIFY" command to a bogus
channel and that this command actually forced the log to replay on the
slave, updating the replay timestamp. This seems like a viable solution
to my problem.

My plan is to have the monitoring role issue a "NOTIFY" every 30 seconds
to ensure the timestamp is updated at least that frequently. But there
will not be an associated "LISTEN" for these notifications.

However, I don't want to cause any problems for the PostgreSQL cluster
itself by having messages inserted with nobody to listen for them, which
is why I'm posting here.

Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?

Depending on how long 'long term' is:

https://www.postgresql.org/docs/9.6/static/sql-notify.html

"There is a queue that holds notifications that have been sent but not
yet processed by all listening sessions. If this queue becomes full,
transactions calling NOTIFY will fail at commit. The queue is quite
large (8GB in a standard installation) and should be sufficiently sized
for almost every use case. However, no cleanup can take place if a
session executes LISTEN and then enters a transaction for a very long
time. Once the queue is half full you will see warnings in the log file
pointing you to the session that is preventing cleanup. In this case you
should make sure that this session ends its current transaction so that
cleanup can proceed."

Thank you,

Rob Brucks

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I did find a post a while back saying they were discarded, but I wanted to double-check.

I performed some tests to see if listens worked AFTER the notify was issued, they were not. This leads me to believe that the messages are discarded when a listen does not yet exist.

--Rob

From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tuesday, February 21, 2017 at 3:38 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Rob Brucks <rob.brucks@rackspace.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver <adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>> wrote:
On 02/21/2017 01:07 PM, Rob Brucks wrote:

Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?

Depending on how long 'long term' is:

https://www.postgresql.org/docs/9.6/static/sql-notify.html

"There is a queue that holds notifications that have been sent but not yet processed by all listening sessions

​Its not clear in the OP that this is the case (it seems to be) but the documentation is non-specific as to what happens when "# of listeners" = 0; I suspect that said messages are created and then immediately discarded - though apparently they do make it over to the standby server​ too - and likely also immediately discarded there as well.

David J.

On 02/21/2017 02:19 PM, Rob Brucks wrote:

I did find a post a while back saying they were discarded, but I wanted
to double-check.

I performed some tests to see if listens worked AFTER the notify was
issued, they were not. This leads me to believe that the messages are
discarded when a listen does not yet exist.

Seems the thing to do would be to monitor the size of :

$PG_DATA/pg_notify/

--Rob

*From: *"David G. Johnston" <david.g.johnston@gmail.com>
*Date: *Tuesday, February 21, 2017 at 3:38 PM
*To: *Adrian Klaver <adrian.klaver@aklaver.com>
*Cc: *Rob Brucks <rob.brucks@rackspace.com>,
"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
*Subject: *Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 02/21/2017 01:07 PM, Rob Brucks wrote:

Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?

Depending on how long 'long term' is:

https://www.postgresql.org/docs/9.6/static/sql-notify.html

"There is a queue that holds notifications that have been sent but
not yet processed by all listening sessions

​Its not clear in the OP that this is the case (it seems to be) but the
documentation is non-specific as to what happens when "# of listeners" =
0; I suspect that said messages are created and then immediately
discarded - though apparently they do make it over to the standby server
​ too - and likely also immediately discarded there as well.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

That's a great thought!

Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing: one 8k file named "0000".

If that's truly where notifications are stored, then it looks like I should be good, at least for storage. But I wonder if that file is only used to store notify commands during shutdown/startup?

Or if there are any considerations for memory usage…

--Rob

On 2/21/17, 4:38 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:

On 02/21/2017 02:19 PM, Rob Brucks wrote:

I did find a post a while back saying they were discarded, but I wanted
to double-check.

I performed some tests to see if listens worked AFTER the notify was
issued, they were not. This leads me to believe that the messages are
discarded when a listen does not yet exist.

Seems the thing to do would be to monitor the size of :

$PG_DATA/pg_notify/

--Rob

*From: *"David G. Johnston" <david.g.johnston@gmail.com>
*Date: *Tuesday, February 21, 2017 at 3:38 PM
*To: *Adrian Klaver <adrian.klaver@aklaver.com>
*Cc: *Rob Brucks <rob.brucks@rackspace.com>,
"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
*Subject: *Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 02/21/2017 01:07 PM, Rob Brucks wrote:

Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?

Depending on how long 'long term' is:

https://www.postgresql.org/docs/9.6/static/sql-notify.html

"There is a queue that holds notifications that have been sent but
not yet processed by all listening sessions

​Its not clear in the OP that this is the case (it seems to be) but the
documentation is non-specific as to what happens when "# of listeners" =
0; I suspect that said messages are created and then immediately
discarded - though apparently they do make it over to the standby server
​ too - and likely also immediately discarded there as well.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 21, 2017 at 3:43 PM, Rob Brucks <rob.brucks@rackspace.com>
wrote:

But I wonder if that file is only used to store notify commands during
shutdown/startup?

​
huh?​

David J.

If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup?

Or is the message just discarded?

--Rob

From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tuesday, February 21, 2017 at 4:45 PM
To: Rob Brucks <rob.brucks@rackspace.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 3:43 PM, Rob Brucks <rob.brucks@rackspace.com<mailto:rob.brucks@rackspace.com>> wrote:
But I wonder if that file is only used to store notify commands during shutdown/startup?
​
huh?​

David J.

On 02/21/2017 02:43 PM, Rob Brucks wrote:

That's a great thought!

Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing: one 8k file named "0000".

If that's truly where notifications are stored, then it looks like I should be good, at least for storage. But I wonder if that file is only used to store notify commands during shutdown/startup?

From the source of async.c:

During start or reboot, clean out the pg_notify directory.

I would guess the 0000 file is like a WAL file it is a pre-initialized
file filled with 0(?)'s

Or if there are any considerations for memory usage…

--Rob

On 2/21/17, 4:38 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:

On 02/21/2017 02:19 PM, Rob Brucks wrote:

I did find a post a while back saying they were discarded, but I wanted
to double-check.

I performed some tests to see if listens worked AFTER the notify was
issued, they were not. This leads me to believe that the messages are
discarded when a listen does not yet exist.

Seems the thing to do would be to monitor the size of :

$PG_DATA/pg_notify/

--Rob

*From: *"David G. Johnston" <david.g.johnston@gmail.com>
*Date: *Tuesday, February 21, 2017 at 3:38 PM
*To: *Adrian Klaver <adrian.klaver@aklaver.com>
*Cc: *Rob Brucks <rob.brucks@rackspace.com>,
"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
*Subject: *Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 02/21/2017 01:07 PM, Rob Brucks wrote:

Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?

Depending on how long 'long term' is:

https://www.postgresql.org/docs/9.6/static/sql-notify.html

"There is a queue that holds notifications that have been sent but
not yet processed by all listening sessions

​Its not clear in the OP that this is the case (it seems to be) but the
documentation is non-specific as to what happens when "# of listeners" =
0; I suspect that said messages are created and then immediately
discarded - though apparently they do make it over to the standby server
​ too - and likely also immediately discarded there as well.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rob Brucks <rob.brucks@rackspace.com> writes:

If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup?
Or is the message just discarded?

NOTIFY data is not saved across a shutdown or crash.

(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 21, 2017 at 3:47 PM, Rob Brucks <rob.brucks@rackspace.com>
wrote:

If a notify is sent and then PG is immediately shut down, wouldn't PG want
to save that message for processing after startup?

Or is the message just discarded?

​Adrian gave details but trying to figure out notification semantics across
a system reboot seems like a headache for little benefit.​

Its likely assumed that upon first connection the client would eagerly load
the relevant data - capturing the static state of the system at that time -
and then only perform additional queries upon notification.

David J.

On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Rob Brucks <rob.brucks@rackspace.com> writes:

If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup?
Or is the message just discarded?

NOTIFY data is not saved across a shutdown or crash.

(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)

regards, tom lane

Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message:

"ERROR: cannot execute LISTEN during recovery"

-=Frank

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Rob Brucks <rob.brucks@rackspace.com> writes:

If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup?
Or is the message just discarded?

NOTIFY data is not saved across a shutdown or crash.

(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)

regards, tom lane

Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message:

"ERROR: cannot execute LISTEN during recovery"

-=Frank

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Tom,

Can you answer my original question too:

If I am issuing NOTIFY commands every 30 seconds (and immediately committing) and there are no listeners, will that have a negative impact on the cluster?

I'm using the NOTIFY to force streaming replication to update the "pg_last_xact_replay_timestamp" on the slaves so we can more accurately monitor replication lag on quiet systems.

Thanks,
Rob

On 2/21/17, 5:03 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Rob Brucks <rob.brucks@rackspace.com> writes:

If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup?
Or is the message just discarded?

NOTIFY data is not saved across a shutdown or crash.

(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

=?utf-8?Q?Fran=C3=A7ois_Beaulieu?= <frank@TZoNE.ORG> writes:

On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)

Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message:
"ERROR: cannot execute LISTEN during recovery"

Hmm ... you know what, my remark above is full of it. NOTIFY traffic
*doesn't* go into the WAL stream. I think I was remembering some
discussions about how that would be a good idea so that you could put
listeners on slaves; but that hasn't actually happened yet, as a look
through async.c will show.

After thinking a bit more, I believe what the OP is seeing is that
NOTIFY does result in an XID being assigned to the transaction (so
that the message it sticks into the pg_notify queue can be correctly
labeled). That therefore results in a transaction commit message
being sent to WAL, even though this transaction did nothing that would
actually change any persistent database state.

There are other ways of forcing XID assignment without doing much
real work, but this one is probably about as cheap as any.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you Tom.

So, long-term, you don't see any negative impacts to the master cluster?

I just don't want to implement this as a streaming "push" mechanism and then have my cluster crash in 12 months because it hit some obscure problem with notifications.

This turned out to be a really simple/elegant way to force WAL stream movement without having to modify the database in any way, since I am heavily restricting the permissions granted to my monitoring role.

And, on a side note, it would be EXTREMELY useful to include a default monitoring role in PG that has permission to query all columns of all pg_stat* views on any DB, yet has no ability to modify *anything* (not even create any objects of its own). I won't grant super-user to my monitoring role since it is administered remotely, it is too much of a security risk.

Thanks!
Rob

On 2/22/17, 3:19 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

=?utf-8?Q?Fran=C3=A7ois_Beaulieu?= <frank@TZoNE.ORG> writes:

On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)

Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message:
"ERROR: cannot execute LISTEN during recovery"

Hmm ... you know what, my remark above is full of it. NOTIFY traffic
*doesn't* go into the WAL stream. I think I was remembering some
discussions about how that would be a good idea so that you could put
listeners on slaves; but that hasn't actually happened yet, as a look
through async.c will show.

After thinking a bit more, I believe what the OP is seeing is that
NOTIFY does result in an XID being assigned to the transaction (so
that the message it sticks into the pg_notify queue can be correctly
labeled). That therefore results in a transaction commit message
being sent to WAL, even though this transaction did nothing that would
actually change any persistent database state.

There are other ways of forcing XID assignment without doing much
real work, but this one is probably about as cheap as any.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general