Re: [GENERAL] NOTIFY command impact

[Benoit Lobréau]

Hi !

Indeed :)
Thanks for taking the time to explain !

Benoit.

Re: [GENERAL] NOTIFY command impact

[Rob Brucks]

Hi Benoit,

Your SQL works fine as long as there are never any communication problems 
between the master and slave.  But if your slave loses communication with the 
master, then the SQL you provided will not report any lag despite the potential 
that the slave could be very far behind the master.

This is because that SQL is asking the slave how far it is behind the master 
based on logs it has received from the master. However if the slave has not 
received logs from the master it cannot tell if the master has simply gone idle 
or if it has lost communication and is unable to send updates.

By introducing a mechanism to manually force the replication stream to be sent 
periodically, which is what I use the NOTIFY command to do on the master, I can 
ensure that the slave is updating pg_last_xact_replay_timestamp at least as 
often as the NOTIFY is executed on the master (say every 30 seconds) and is 
successfully replayed on the slave.  This enables an accurate measurement of 
replication lag whatever the circumstances.

If the master has simply gone idle because there is no update activity, the 
NOTIFY command will still force the replication stream to replay on the slave 
every 30 seconds and the replay timestamp will be updated.

But if communication to the master is lost, for whatever reason, the replay 
timestamp will not be updated on the slave and then lag can be accurately 
measured and alerted.

I hope this helps!

--Rob

From: Benoit Lobréau <benoit.lobr...@gmail.com>
Date: Thursday, February 23, 2017 at 9:23 AM
To: "David G. Johnston" <david.g.johns...@gmail.com>
Cc: Rob Brucks <rob.bru...@rackspace.com>, Tom Lane <t...@sss.pgh.pa.us>, 
François Beaulieu <fr...@tzone.org>, Adrian Klaver <adrian.kla...@aklaver.com>, 
"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] NOTIFY command impact

Hi,

I might have missed something.
This should be enough to solve the problem no ?

SELECT
CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
END
AS replication_lag;

Benoit.

Re: [GENERAL] NOTIFY command impact

[Benoit Lobréau]

Hi,

I might have missed something.
This should be enough to solve the problem no ?

SELECT
CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
END
AS replication_lag;

Benoit.

Re: [GENERAL] NOTIFY command impact

[David G. Johnston]

On Wed, Feb 22, 2017 at 3:05 PM, Rob Brucks 
wrote:

> 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.
>

​The only concern related to "xid/transactions" that I can think of is:

​https://www.postgresql.org/docs/current/static/routine-vacuuming.html

"To avoid this, it is necessary to vacuum every table in every database at
least once every two billion transactions."

Probably not appreciably but you will hit the 2B mark more quickly if you
are burning off transaction ids in this manner.

David J.

Re: [GENERAL] NOTIFY command impact

[Rob Brucks]

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"  wrote:

=?utf-8?Q?Fran=C3=A7ois_Beaulieu?=  writes:
>> On Feb 21, 2017, at 6:03 PM, Tom Lane  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

Re: [GENERAL] NOTIFY command impact

[Tom Lane]

=?utf-8?Q?Fran=C3=A7ois_Beaulieu?=  writes:
>> On Feb 21, 2017, at 6:03 PM, Tom Lane  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

Re: [GENERAL] NOTIFY command impact

[François Beaulieu]

> On Feb 21, 2017, at 6:03 PM, Tom Lane  wrote:
> 
> Rob Brucks  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

Re: [GENERAL] NOTIFY command impact

[Rob Brucks]

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"  wrote:

Rob Brucks  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

Re: [GENERAL] NOTIFY command impact

[François Beaulieu]

> On Feb 21, 2017, at 6:03 PM, Tom Lane  wrote:
> 
> Rob Brucks  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

Re: [GENERAL] NOTIFY command impact

[David G. Johnston]

On Tue, Feb 21, 2017 at 3:47 PM, Rob Brucks 
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.

Re: [GENERAL] NOTIFY command impact

[Tom Lane]

Rob Brucks  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

Re: [GENERAL] NOTIFY command impact

[Adrian Klaver]

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 "".

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  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.kla...@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.johns...@gmail.com>
> *Date: *Tuesday, February 21, 2017 at 3:38 PM
> *To: *Adrian Klaver <adrian.kla...@aklaver.com>
> *Cc: *Rob Brucks <rob.bru...@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@aklaver.com





--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] NOTIFY command impact

[Rob Brucks]

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.johns...@gmail.com>
Date: Tuesday, February 21, 2017 at 4:45 PM
To: Rob Brucks <rob.bru...@rackspace.com>
Cc: Adrian Klaver <adrian.kla...@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.bru...@rackspace.com<mailto:rob.bru...@rackspace.com>> wrote:
But I wonder if that file is only used to store notify commands during 
shutdown/startup?
​
huh?​

David J.

Re: [GENERAL] NOTIFY command impact

[David G. Johnston]

On Tue, Feb 21, 2017 at 3:43 PM, Rob Brucks 
wrote:

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

David J.

Re: [GENERAL] NOTIFY command impact

[Rob Brucks]

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 "".

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.kla...@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.johns...@gmail.com>
> *Date: *Tuesday, February 21, 2017 at 3:38 PM
> *To: *Adrian Klaver <adrian.kla...@aklaver.com>
> *Cc: *Rob Brucks <rob.bru...@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@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

Re: [GENERAL] NOTIFY command impact

[Adrian Klaver]

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.johns...@gmail.com>
*Date: *Tuesday, February 21, 2017 at 3:38 PM
*To: *Adrian Klaver <adrian.kla...@aklaver.com>
*Cc: *Rob Brucks <rob.bru...@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@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

Re: [GENERAL] NOTIFY command impact

[Rob Brucks]

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.johns...@gmail.com>
Date: Tuesday, February 21, 2017 at 3:38 PM
To: Adrian Klaver <adrian.kla...@aklaver.com>
Cc: Rob Brucks <rob.bru...@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.kla...@aklaver.com<mailto:adrian.kla...@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.

Re: [GENERAL] NOTIFY command impact

[Rob Brucks]

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"  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.kla...@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

Re: [GENERAL] NOTIFY command impact

[David G. Johnston]

On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver 
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.

Re: [GENERAL] NOTIFY command impact

[Adrian Klaver]

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.kla...@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