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