How about synchronous notifications?

At 11:46 AM 8/24/2010, Craig Ringer wrote:
>On 24/08/2010 11:06 AM, A.M. wrote:
>>
>>On Aug 23, 2010, at 10:18 PM, Craig Ringer wrote:
>>
>>>On 08/24/2010 06:43 AM, Bruce Momjian wrote:
>>>>A.M. wrote:
>>>>>There is a new pg_notify function in pgsql 9.0 but no pg_listen
>>>>>equivalent? Why? It sure would be handy to pass quoted strings...
>>>>
>>>>Notify sends the notify;  there is no place to send a 'listen' payload.
>>>
>>>I assume what they want is the ability to filter notifications, so
>>>they only get notifications with a certain payload.
>>>
>>>Seems to me that in that case you should just be using different
>>>notify values (possibly using the two-argument form of pg_notify)
>>>so you can listen on different things depending on what you are interested in.
>>
>>Actually, my use case was for parameterized queries and pl
>>functions where it's much easier to use quoted strings for the
>>notification name as well as the payload- it would just be a
>>convenience, really.
>
>So what you really want is the ability to pg_listen and pg_notify on
>a *name* instead of a numeric key?

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.

Regards,

Link.