Re: How about synchronous notifications?

On Tue, Sep 21, 2010 at 12:23 PM, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:
> To me what would also be useful would be synchronous notifications.
>
> This would allow many programs to wait for events to happen rather than all
> of them polling the database (and wasting CPU cycles, battery life, etc).
> You could still poll with a suitable timeout if you want.
>
> Example functions:
>
> pg_listen(<text>[, buffersize]);
>
> -- <text> is the channel the session wants to listen to, and buffersize is
> the maximum number of payloads the buffer will queue up (default = 1).
> -- immediately returns true if successful, false if failed.
>
> pg_unlisten(<text>);
> -- this unregisters the session's interest with the channel indicated by
> <text>, and clears the relevant channel's buffer.
> -- immediately returns true if successful, false if failed.
>
> pg_wait(<text> [,<timeout value in milliseconds>]);
> -- this waits on channel <text> for at most <timeout> milliseconds (timeout
> default = NULL) and returns the notification payload.
> -- returns NULL if timed out, or no notifications were sent.
> -- a timeout value of NULL means wait indefinitely till a notification is
> received, 0 means don't wait at all just return what's in the session's
> channel buffer (which would be NULL if there were no notifications).
>
> Example scenario:
>
> session #1:
>
> select pg_listen('channel 2');
>  pg_listen
> -----------
>  t
> (1 row)
>
> SELECT pg_wait('channel 2');
> -- this waits/blocks indefinitely till session #2 below
>
> session #2:
>
> SELECT pg_notify('channel 2','hi there');
>
> session #1:
>
> -- session 1 now unblocks and gives the following result
>
> SELECT pg_wait('channel 2');
>  pg_wait
> ----------
>  hi there
> (1 row)
>
> -- session 1 can now do other stuff here - check various tables for new
> data, etc.
>
> Would this be asking for too much? :)
>
> I asked for something like this about 9 years ago, and was told to look into
> something like pqwait, and waiting on PQsocket fds. But I think that's not
> so simple if you are using stuff like ODBC/DBI/JDBC etc.
>
> Yes it might be more scalable to use an external messaging server for this,
> but it's often just not as convenient or as easy. With this you could have
> many DB clients waiting for events and then checking tables, doing various
> other things only when relevant stuff happens. Developers can then easily
> write event triggered DB stuff, without having to deal with another service,
> or looking for some messaging library for their language of choice, or
> writing it from scratch. Basically if it supports JDBC/ODBC/DBI it will
> work, and work the same way.

They can be effectively rigged. If you want to block and wait in a
single function call, you have to deliver notifications
mid-transaction (which is really, I think, what you are asking for).
This is prohibited strictly speaking but you can work around the issue
via dblink: dblink to self w/query that generates the notification.

As long as you are in read committed mode, the notified client can
respond back with a signal and any response data you want.  Taking
advantage of read committed, you can loop w/sleep and wait for your
signal to be set or until an appropriate timeout occurs.  If you had
the ability to send notifications immediately (which I believe to be
possible within the constraints of the new implementation), you could
do this w/o the dblink step.

merlin