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
