Re: feature request: consume asynchronous notification via a function

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company