Re: NOTIFY command impact

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

=?utf-8?Q?Fran=C3=A7ois_Beaulieu?= <frank(at)TZoNE(dot)ORG> writes:
>> On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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