Re: feature request: consume asynchronous notification via a function

On Tue, Nov 21, 2017 at 7:59 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Nov 17, 2017 at 9:49 AM, Merlin Moncure <mmoncure(at)gmail(dot)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