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