Re: feature request: consume asynchronous notification via a function
On Tue, Nov 21, 2017 at 2:16 PM, Merlin Moncurewrote: > On Tue, Nov 21, 2017 at 12:20 PM, Tom Lane wrote: >> Robert Haas writes: >>> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure 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
Re: feature request: consume asynchronous notification via a function
On Tue, Nov 21, 2017 at 12:20 PM, Tom Lanewrote: > Robert Haas writes: >> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure 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
Re: feature request: consume asynchronous notification via a function
Robert Haaswrites: > On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure 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
Re: feature request: consume asynchronous notification via a function
On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncurewrote: >> 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
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