Re: [PATCH] Function to get size of asynchronous notification queue

Hello hackers,

I present a patch to add a new built-in function pg_notify_queue_saturation().

The purpose of the function is to allow users to monitor the health of their notification queue.  In certain cases, a client connection listening for notifications might get stuck inside a transaction, and this would cause the queue to keep filling up, until finally it reaches capacity and further attempts to NOTIFY error out.

The current documentation under LISTEN explains this possible gotcha, but doesn't really suggest a useful way to address it, except to mention that warnings will show up in the log once you get to 50% saturation of the queue.  Unless you happen to be eyeballing the logs when it happens, that's not a huge help.  The choice of 50% as a threshold is also very much arbitrary, and by the time you hit 50% the problem has likely been going on for quite a while.  If you want your nagios (or whatever) to say, alert you when the queue goes over 5% or 1%, your options are limited and awkward.

The patch has almost no new code.  It makes use of the existing logic for the 50% warning.  I simply refactored that logic into a separate function asyncQueueSaturation, and then added pg_notify_queue_saturation to make that available in SQL.

I am not convinced that pg_notify_queue_saturation is the best possible name for this function, and am very much open to other suggestions.

The patch includes documentation, a regression test and an isolation test.

Cheers,

BJ

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers