Re: NOTIFY command impact

=?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