Thread: feature request: consume asynchronous notification via a function
Hackers, Currently the only way that I know of to consume async notifications via SQL (as opposed to a client application) is via dblink_get_notify. This method isn't very good; it requires some extra support coding, eats a connection and a backend, and doesn't have any timeout facilities. The lack a good facility to do this will become more troublesome if/when Peter's recent fantastic work to implement stored procedures in the database gets accepted; asynchronous notifications could be a more efficient mechanic for backend processes to signal each other than the current method of signalling via fields in a table. A good interface might look something like: pg_get_notifications( TimeOut INT DEFAULT 0, notify_name OUT TEXT, payload OUT TEXT, pid OUT INT) RETURNS SETF RECORD AS... The function would return immediately by default, or until TimeOut seconds transpired. We'd still have to poll internally, so that signals could be checked etc, but this would be a nice way to consume notifications without any dependencies -- what do you think? merlin
On Fri, Nov 17, 2017 at 9:49 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > Currently the only way that I know of to consume async notifications > via SQL (as opposed to a client application) is via dblink_get_notify. > This method isn't very good; it requires some extra support coding, > eats a connection and a backend, and doesn't have any timeout > facilities. The lack a good facility to do this will become more > troublesome if/when Peter's recent fantastic work to implement stored > procedures in the database gets accepted; asynchronous notifications > could be a more efficient mechanic for backend processes to signal > each other than the current method of signalling via fields in a > table. > > A good interface might look something like: > pg_get_notifications( > TimeOut INT DEFAULT 0, > notify_name OUT TEXT, > payload OUT TEXT, > pid OUT INT) RETURNS SETF RECORD AS... > > The function would return immediately by default, or until TimeOut > seconds transpired. We'd still have to poll internally, so that > signals could be checked etc, but this would be a nice way to consume > notifications without any dependencies -- what do you think? I think that wouldn't work very well, because I think we must have a snapshot open in order to run pg_get_notifications(), and that means we're holding back the system-wide xmin. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Nov 21, 2017 at 7:59 AM, Robert Haas <robertmhaas@gmail.com> wrote: > On Fri, Nov 17, 2017 at 9:49 AM, Merlin Moncure <mmoncure@gmail.com> wrote: >> Currently the only way that I know of to consume async notifications >> via SQL (as opposed to a client application) is via dblink_get_notify. >> This method isn't very good; it requires some extra support coding, >> eats a connection and a backend, and doesn't have any timeout >> facilities. The lack a good facility to do this will become more >> troublesome if/when Peter's recent fantastic work to implement stored >> procedures in the database gets accepted; asynchronous notifications >> could be a more efficient mechanic for backend processes to signal >> each other than the current method of signalling via fields in a >> table. >> >> A good interface might look something like: >> pg_get_notifications( >> TimeOut INT DEFAULT 0, >> notify_name OUT TEXT, >> payload OUT TEXT, >> pid OUT INT) RETURNS SETF RECORD AS... >> >> The function would return immediately by default, or until TimeOut >> seconds transpired. We'd still have to poll internally, so that >> signals could be checked etc, but this would be a nice way to consume >> notifications without any dependencies -- what do you think? > > I think that wouldn't work very well, because I think we must have a > snapshot open in order to run pg_get_notifications(), and that means > we're holding back the system-wide xmin. I am very much looking at the new stored procedure functionality and imaging a loop like this: LOOP FOR r IN SELECT * FROM pg_get_notifications(30) LOOP PERFORM do_stuff(r); END LOOP; COMMIT; -- advance xmin etc END LOOP; ...I'm obviously speculatively thinking ahead to Peter's stored procedure work seeing the light of day (which, based on the utility vs the simplicity of the patch and how it works in testing I'm very optimistic about). The above would provide real time response to certain actions I do now with polling, typically in bash. Without stored procedures, I agree that this would be a foot gun. merlin
On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure <mmoncure@gmail.com> wrote: >> I think that wouldn't work very well, because I think we must have a >> snapshot open in order to run pg_get_notifications(), and that means >> we're holding back the system-wide xmin. > > I am very much looking at the new stored procedure functionality and > imaging a loop like this: > > LOOP > FOR r IN SELECT * FROM pg_get_notifications(30) > > LOOP > PERFORM do_stuff(r); > END LOOP; > > COMMIT; -- advance xmin etc > END LOOP; > > ...I'm obviously speculatively thinking ahead to Peter's stored > procedure work seeing the light of day (which, based on the utility vs > the simplicity of the patch and how it works in testing I'm very > optimistic about). The above would provide real time response to > certain actions I do now with polling, typically in bash. Without > stored procedures, I agree that this would be a foot gun. Yeah, if you keep the timeout fairly short, it would probably work OK (with Peter's stuff). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> I am very much looking at the new stored procedure functionality and
>> imaging a loop like this:
>>
>> LOOP
>> FOR r IN SELECT * FROM pg_get_notifications(30)
>> LOOP
>> PERFORM do_stuff(r);
>> END LOOP;
>> COMMIT; -- advance xmin etc
>> END LOOP;
> Yeah, if you keep the timeout fairly short, it would probably work OK
> (with Peter's stuff).
Traditionally, NOTIFY messages are delivered to the client only between
transactions, so that there is no question about whether the
message-delivery should roll back if the surrounding transaction aborts.
It's not very clear to me what the behavior of pg_get_notifications()
inside a transaction ought to be. Is it OK if it's a volatile function
and the messages are just gone once the function has returned them,
even if you fail to do anything about them because your transaction
fails later?
(I'd be against having a function that returns more than one at a time,
in any case, as that just complicates matters even more.)
regards, tom lane
On Tue, Nov 21, 2017 at 12:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure <mmoncure@gmail.com> wrote: >>> I am very much looking at the new stored procedure functionality and >>> imaging a loop like this: >>> >>> LOOP >>> FOR r IN SELECT * FROM pg_get_notifications(30) >>> LOOP >>> PERFORM do_stuff(r); >>> END LOOP; >>> COMMIT; -- advance xmin etc >>> END LOOP; > >> Yeah, if you keep the timeout fairly short, it would probably work OK >> (with Peter's stuff). > > Traditionally, NOTIFY messages are delivered to the client only between > transactions, so that there is no question about whether the > message-delivery should roll back if the surrounding transaction aborts. > It's not very clear to me what the behavior of pg_get_notifications() > inside a transaction ought to be. Is it OK if it's a volatile function > and the messages are just gone once the function has returned them, > even if you fail to do anything about them because your transaction > fails later? I think destroying upon consumption is OK. There are a lot of mitigation strategies to deal with that issue and NOTIFY is for signalling, not queuing. > (I'd be against having a function that returns more than one at a time, > in any case, as that just complicates matters even more.) ok. merlin
On Tue, Nov 21, 2017 at 2:16 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Tue, Nov 21, 2017 at 12:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >>> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure <mmoncure@gmail.com> wrote: >>>> I am very much looking at the new stored procedure functionality and >>>> imaging a loop like this: >>>> >>>> LOOP >>>> FOR r IN SELECT * FROM pg_get_notifications(30) >>>> LOOP >>>> PERFORM do_stuff(r); >>>> END LOOP; >>>> COMMIT; -- advance xmin etc >>>> END LOOP; >> >>> Yeah, if you keep the timeout fairly short, it would probably work OK >>> (with Peter's stuff). >> >> Traditionally, NOTIFY messages are delivered to the client only between >> transactions, so that there is no question about whether the >> message-delivery should roll back if the surrounding transaction aborts. >> It's not very clear to me what the behavior of pg_get_notifications() >> inside a transaction ought to be. Is it OK if it's a volatile function >> and the messages are just gone once the function has returned them, >> even if you fail to do anything about them because your transaction >> fails later? > > I think destroying upon consumption is OK. There are a lot of > mitigation strategies to deal with that issue and NOTIFY is for > signalling, not queuing. > >> (I'd be against having a function that returns more than one at a time, >> in any case, as that just complicates matters even more.) Hm, a less controversial approach might be to only allow consumption of notifications that were delivered at the start of transaction. Procedures could then issue an intervening 'COMMIT' statement to pick up new notifications. There's be no reason for a timeout argument in that case obviously, so the end user would have to poll in order to pick up the notification, which I don't like. This would be an alternative approach to the way it do it today, which is to poll for a set table flag in a non-serializable transaction, maybe with enough differentiation in use to merit introduction. merlin