Re: feature request: consume asynchronous notification via a function

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