notification: pg_notify ?

Jeff Davis asked on -general why NOTIFY doesn't take an optional
argument, specifying a message that is passed to the listening backend.
This feature is supported by Oracle and other databases and I think it's
quite useful, so I've started to implement it. Most of the modifications
have been pretty straight-forward, except for 2 issues:

(1) Processing notifies. Currently, the only data that is passed from
the notifying backend to the listening one is the PID of the notifier,
which is stored in the "notification" column of pg_listener. In order to
pass messages from notifier to listener, I could add another column to
pg_listener, but IMHO that's a bad idea: there is really no reason for
this kind of data to be in pg_listener in the first place. pg_listener
should simply list the PIDs of listening backends, as well as the
conditions upon which they are listening -- any data that is related to
specific notifications should be put elsewhere.

(2) Multiple notifications on the same condition name in a short time
span are delivered as a single notification. This isn't currently a
problem because the NOTIFY itself doesn't carry any data (other than
backend PID), it just informs the listener that an event has occurred.
If we allow NOTIFY to send a message to the listener, this is not good
-- the listener should be notified for each and every notification,
since the contents of the message could be important.

Solution: Create a new system catalog, pg_notify. This should contain 4
columns:

relname: the name of the NOTIFY condition that has been sent
message: the optional message sent by the NOTIFY
sender: the PID of the backend that sent the NOTIFY
receiver: the PID of the listening backend

AFAICT, this should resolve the two issues mentioned above. The actual
notification of a listening backend is still done at transaction commit,
by sending a SIGUSR2: however, all this does is to ask the backend to
scan through pg_notify, looking for tuples containing its PID in
"receiver". Therefore, even if Unix doesn't send multiple signals for
multiple notifications, a single signal should be enough to ensure a
scan of pg_notify, where any additional notifications will be found.

If we continued to add columns to pg_listener, there would be a limit of
1 tuple per listening backend: thus, we would still run into problems
with multiple notifications being ignored.

Can anyone see a better way to do this? Are there any problems with the
implementation I've outlined?

Any feedback would be appreciated.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC