Thread: 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.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201211281018
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlC2LRoACgkQvJuQZxSWSsi71ACeOdmbxth+tuF45N2DwoxftnWm
EX8An3ZjjKloJo8M1PdyCuoSLFiHV/Kd
=noR6
-----END PGP SIGNATURE-----

________________________________________
From: Greg Sabino Mullane [greg@turnstep.com]
Sent: Wednesday, November 28, 2012 10:26 AM
To: pgsql-general@postgresql.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)
isa good thing. 
But, I (and probably many others) use LISTEN/NOTIFY mechanism to notify client program connected to postgres database
aboutchanges 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
NOTIFYmy_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

Igor Neyman <ineyman@perceptron.com> writes:
> 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.

[ shrug... ] It was debated extensively and the advantages of the new
implementation were deemed to outweigh the disadvantages.

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, November 28, 2012 6:13 PM
> To: Igor Neyman
> Cc: Greg Sabino Mullane; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] pg_listening_channels()
>
> Igor Neyman <ineyman@perceptron.com> writes:
> > 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.
>
> [ shrug... ] It was debated extensively and the advantages of the new
> implementation were deemed to outweigh the disadvantages.
>
>             regards, tom lane

Tom,

Are you saying that these two features: attached payload and being able to find which channels are being listened to -
areincompatible?  That they cannot coexist? 

Regards,
Igor Neyman

Igor Neyman <ineyman@perceptron.com> writes:
>> [ shrug... ] It was debated extensively and the advantages of the new
>> implementation were deemed to outweigh the disadvantages.

> Are you saying that these two features: attached payload and being able to find which channels are being listened to
-are incompatible?  That they cannot coexist? 

It's not about the payload aspect.  We got rid of the use of a table to
store messages-in-transit, which resulted in greatly improved throughput
and lower overhead --- but it also means that there's no exposed
information about which backends are actually paying attention to
specific notify channels.  We could have bolted some overhead back on
to expose that again, but it was judged that too few people had that
requirement to justify imposing such overhead on everybody.  The
infrequency of complaints in the two years since then seems to justify
that choice.

It's not particularly difficult to create your own signaling system
for this purpose, if you think it's worth the trouble --- LISTEN just
doesn't have it built in anymore.  In practice, I'll bet it's not worth
the trouble versus just firing off messages unconditionally.

            regards, tom lane

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


Igor Neyman wrote:
> But, I (and probably many others) use LISTEN/NOTIFY mechanism to notify
> client program connected to postgres database about changes made in the database

Yes, it is commonly used for that.

> 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.

On the contrary, it was very well discussed and designed. Why do you
even care if the anyone is listening or not? Simply remove the
"check if anyone listens" step and send the NOTIFY.

If you really need to know if anyone is listening, set up a two-way
stream of listen/notify.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 201211292331
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlC4NykACgkQvJuQZxSWSsjNBACfXRI+7IIcFl1COSf+Oe7u8kuU
PhUAn26FHWjQa2yJAP7CmAMWNGLVUQyJ
=xZ4p
-----END PGP SIGNATURE-----

> -----Original Message-----
> From: Greg Sabino Mullane [mailto:greg@turnstep.com]
> Sent: Thursday, November 29, 2012 11:34 PM
> To: pgsql-general@postgresql.org
> Subject: Re: pg_listening_channels()
>
>
> On the contrary, it was very well discussed and designed. Why do you
> even care if the anyone is listening or not? Simply remove the "check
> if anyone listens" step and send the NOTIFY.
>

Well, I guess we disagree on this.

Why trashing the system with NOTIFYs no one listens to?
Of course, like Tom Lane suggested, I could create a table similar to now obsolete pg_listener and manage it from the
clientthat LISTENs and gets notifications. 

Also, what sense pg_listening_channels() function makes, if it returns channels that I created (in my current
session/connection)?  
I don't need this function to know whether I issued LISTEN my_channel or not.

Regards,
Igor Neyman