Re: Sending notifications from the master to the standby

On Tue, Jan 10, 2012 at 5:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joachim Wieland <joe(at)mcknight(dot)de> writes:
>> [ send NOTIFYs to slaves by means of: ]

Good idea.

> I wonder whether it'd be practical to not involve WAL per se in this
> at all, but to transmit NOTIFY messages by having walsender processes
> follow the notify stream (as though they were listeners) and send the
> notify traffic as a separate message stream interleaved with the WAL
> traffic.  We already have, as of a few days ago, the concept of
> additional traffic in the walsender stream besides the WAL data itself,
> so adding notify traffic as another message type should be
> straightforward.

Also good idea.

> It might be a bit tricky to get walreceivers to inject
> the data into the slave-side ring buffer at the right time, ie, not
> until after the commit a given message describes has been replayed;
> but I don't immediately see a reason to think that's infeasible.

When transaction commits it would use full-size commit records and set
a (new) flag in xl_xact_commit.xinfo to show the commit is paired with
notify traffic.

Get messages in walreceiver.c XLogWalRcvProcessMsg() and put them in a
shared hash table. Messages would need to contain xid of notifying
transaction and other info needed for LISTEN.

When we hit xact.c xact_redo_commit() on standby we'd check for
messages in the hash table if the notify flag is set and execute the
normal notify code as if the NOTIFY had run locally on the standby. We
can sweep the hash table clean of any old messages each time we run
ProcArrayApplyRecoveryInfo()

Add new message type to walprotocol.h. Message code 'L' appears to be
available.

Suggest we add something to initial handshake from standby to say
"please send me notify traffic", which we can link to a parameter that
defines size of standby_notify_buffer. We don't want all standbys to
receive such traffic unless they really want it and pg_basebackup
probably doesn't want it either.

If you wanted to get really fancy you could send only some of the
traffic to each standby based on a hash or roundrobin algorithm, so we
can spread the listeners across multiple standbys.

I'll be your reviewer, if you want.

> Going in this direction would mean that slave-side LISTEN only works
> when using walsender/walreceiver, and not with old-style log shipping.
> But personally I don't see a problem with that.  If you're trying to
> LISTEN you probably want pretty up-to-date data anyway.

Which fits the expected use case also.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services