Catalog for LISTEN'ed to notification channels?

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Catalog for LISTEN'ed to notification channels?
Date: 2023-06-22 08:22:41
Message-ID: CAFCRh-9_C0PAAYTsO_acUV07u_U4jNOhz3vHwHrMvVPOTDwAGA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

[1]: https://www.postgresql.org/docs/current/libpq-notify.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message xxai.art 2023-06-22 10:18:00 postgresql 16beta1-alpine3.18 : build plugin , VARSIZE_ANY_EXHDR: symbol not found
Previous Message Andreas Kretschmer 2023-06-22 05:39:19 Re: PostgreSQL Server Hang​