Re: pg_listening_channels()

________________________________________
From: Greg Sabino Mullane [greg(at)turnstep(dot)com]
Sent: Wednesday, November 28, 2012 10:26 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: pg_listening_channels()

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Igor Neyman asked:

[Postgres 9.2]
> How (if it's even possible) can I get listening channels
> for all sessions/ connections in the database?

No, this is not possible. You can only see items
from your own session.

- --

IMHO, that's a step back comparing to "older" implementation of LISTEN/NOTIFY.

Of course allowing "payload" to be attached to NOTIFY signal (instead of being passed through some user defined table) is a good thing.
But, I (and probably many others) use LISTEN/NOTIFY mechanism to notify client program connected to postgres database about changes made in the database
Implementation prior to PG 9.0:

INTERESTED_CLIENT: LISTEN my_alert;
SOME_OTHER_PROGRAM: INSERTS/UPDATES/DELETES table_client_interested_in;
ON INSERT/UPDATE/DELETE TRIGGER: check if anyone listens on "my_alert" channel by querying pg_listener table, sends NOTIFY my_alert signal and inserts message into user_message_table;
INTERESTED_CLIENT: upon receiving NOTIFY signal reads message from user_message_table;

With PG 9.0 changes I lost ability to check if anyone is interested in the NOTIFY signal and payload I'm about to send.
Seems like this change was not thought through completely.

Regards,
Igor Neyman