Thread: Catalog for LISTEN'ed to notification channels?
Hi,
I've looked in the Catalogs, the doc for LISTEN, NOTIFY, even [LibPQ Async Notif][1],
and I don't see anything about that.Can I introspect which "channel(s)" the current (or any other session) is LISTEN'ing to?
Any way to see pending notification(s) in that 8GB queue, from client apps?
pg_notification_queue_usage() return a ratio of "full-ness" of that queue,
but how to find out its actual max-size or how many pending notifications there are?
Or discover client side which session(s) is/are blocking delivery with a long running transaction?
FWIW, sounds to me motification would be more useful is the client could decide
whether to have transaction "interfere" with delivery (the current behavior), to opt-in
to a new "immediate" (or "best effort" as fast as possible) delivery, independent of
transactions. At LISTEN time, or NOTIFY time, not sure. With the current design,
one must almost always use specific connections just for notifications, to ensure
timely discovery.
To be sure, I see value in transactional notifications, especially about persistent
DDL and/or DML changes which could be undone on ROLLBACK.
But I also see notification a form of "extended IPC" mechanism for apps on different
machines which happen to collaborate on data in particular databases. In that context,
immediate / rapid delivery seem a lot more important and non-transactional.
Also, the doc is ambiguous whether the session-with-open-transaction delivery issue
is per-channel, or global to the whole queue. I hope it's the former, since the latter would
be disastrous I guess.
Perhaps the lack of introspection of channel names is a means to have "secret" channels,
to mitigate the delivery issue mentioned above?
Sorry for all the questions, I'm only now getting into notifications, and while the doc
does provide quite a bit of info, a few dark point remains for me, thus this message.
I'm trying to understand what can go wrong, and how to avoid it.
Thanks, --DD
Dominique Devienne <ddevienne@gmail.com> writes:
> Can I introspect which "channel(s)" the current (or any other session) is
> LISTEN'ing to?
The pg_listening_channels() function will show you channel names the
current session is listening to. There's no way to find out about
other sessions, because that state is only backend-local.
regards, tom lane
On Thu, Jun 22, 2023 at 3:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dominique Devienne <ddevienne@gmail.com> writes:
> Can I introspect which "channel(s)" the current (or any other session) is
> LISTEN'ing to?
The pg_listening_channels() function will show you channel names the
current session is listening to. There's no way to find out about
other sessions, because that state is only backend-local.
Thanks Tom. I'll take that. Useful for unit tests at least.
If anyone can share more info or pointers on the design and rationals for
async notification in PostgreSQL, why it is transactional only, with the issue
that ensues, and whether there are any plans about it, I'd appreciate. Thanks, --DD