On Fri, Nov 17, 2017 at 9:49 AM, Merlin Moncure <mmonc...@gmail.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
