pg_notify but no pg_listen?

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.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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.

--
Craig Ringer

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.

Cheers,
M

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?

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

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.

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

To me what would also be useful would be synchronous notifications.

AFAICS this exists already --- or if it doesn't, that's a client-library
deficiency, not something to solve by inventing more SQL functions.
The form you propose cannot work anyway since NOTIFY events are not
delivered mid-transaction.

regards, tom lane

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.

Here's how you do it: first, make sure you are not within a
transaction or other Pg activity. Get the socket's file handle from
the Pg connection handle. When you're ready to wait for a notify
event, just do a select() system call on that file handle waiting
until there is data to read on that socket.

When you return from the select, just check for the notifications and
you're ready to go. If you did not find a notification, return to the
select() call. Of course, this assumes you've issued the necessary
LISTEN command.

This has worked for me (and is tested well) up thru Pg 8.3. I cannot
imagine it would stop working as the wire line protocol doesn't really
change.

At 07:55 PM 9/22/2010, Vick Khera wrote:

Here's how you do it: first, make sure you are not within a
transaction or other Pg activity. Get the socket's file handle from
the Pg connection handle. When you're ready to wait for a notify
event, just do a select() system call on that file handle waiting
until there is data to read on that socket.

When you return from the select, just check for the notifications and
you're ready to go. If you did not find a notification, return to the
select() call. Of course, this assumes you've issued the necessary
LISTEN command.

This has worked for me (and is tested well) up thru Pg 8.3. I cannot
imagine it would stop working as the wire line protocol doesn't really
change.

How'd one get the socket file handle if using JDBC/ODBC? It seems
possible if using perl DBD-Pg, but I haven't tested that to see if
you can really get out of a transaction.

Given these issues I guess it would be easier to use a separate
messaging server (despite that still not being that easy :) ). This
would have the characteristic of not being DB specific, so apps
wouldn't be locked in to postgresql. Whether this is a benefit or not
depends on your POV ;).

Regards,
Link.

On Wed, Sep 22, 2010 at 9:53 AM, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:

Given these issues I guess it would be easier to use a separate messaging
server (despite that still not being that easy :) ). This would have the
characteristic of not being DB specific, so apps wouldn't be locked in to
postgresql. Whether this is a benefit or not depends on your POV ;).

The AMQP protocol seems to be the way to go for messaging, if that's
where you're heading. If you're not stuck to any legacy, then you
should go where the world is aiming. The only drawback is the
maturity of the tools, but that improves daily.

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