Re: Function to get size of notification queue?

On Tue, 16 Jun 2015 at 07:52 Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> It goes back to the adage, 'Everyone wants to be an author but nobody
> wants to write'.

A more accurate version would be "Everyone wants to be an author, some want
to write, but nobody likes being rejected by publishers".

> For posterity, I think your idea is pretty good, especially if the
> current slru based implementation supports it without a lot of extra
> work.

Thank you for saying so, and yes, adding the function is pretty much
trivial. I already have a patch that works, and will submit it once I've
added docs and tests.

Adding a new built-in function is not free though so I think to
> move forwards with this you'd also have to show some more
> justification. Perhaps a real world example demonstrating the problem
> reduced down to an executable case.
>

Well the docs already describe this situation. The notification queue is
finite, listening clients with long-running transactions could cause it to
blow out, and if it does blow out, Bad Things will ensue. At the moment,
there is no good way to find out whether this is happening.

From SQL Commands / NOTIFY / Notes:

"There is a queue that holds notifications that have been sent but not yet
processed by all listening sessions. If this queue becomes full,
transactions calling NOTIFY will fail at commit. The queue is quite large
(8GB in a standard installation) and should be sufficiently sized for
almost every use case. However, no cleanup can take place if a session
executes LISTEN and then enters a transaction for a very long time. Once
the queue is half full you will see warnings in the log file pointing you
to the session that is preventing cleanup. In this case you should make
sure that this session ends its current transaction so that cleanup can
proceed."

So, it's straightorward to simulate the problem scenario. Make two client
connections A and B to the same server. Client A executes "LISTEN a;",
then "BEGIN;". Client B submits some notifications on channel "a", e.g.,
"SELECT pg_notify('a', 'Test queue saturation ' || s::text) FROM
generate_series(1, 10000) s;". The queue will start filling up, and will
never reduce unless and until client A ends its transaction. If client B
keeps on submitting notifications, the queue will eventually fill
completely and then client B's session will ERROR out.

Cheers,
BJ