Re: feature request: consume asynchronous notification via a function

On Tue, Nov 21, 2017 at 2:16 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Nov 21, 2017 at 12:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure <mmoncure(at)gmail(dot)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