Обсуждение: Determine all listeners subscribed to notifcations and what channels

Поиск
Список
Период
Сортировка

Determine all listeners subscribed to notifcations and what channels

От
Cory Tucker
Дата:
I'm interested in trying to figure out which channels have been subscribed to (using LISTEN).  From what I could tell via a little Googling, there used to be a table named pg_catalog.pg_listener that contained all this information, but that seems to have disappeared somewhere in the 9.x release (I'm using 9.3.x).

Is there a way to find out which channels have listeners?

thanks
--Cory


Re: Determine all listeners subscribed to notifcations and what channels

От
Igor Neyman
Дата:

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Cory Tucker
Sent: Tuesday, February 17, 2015 4:21 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Determine all listeners subscribed to notifcations and what channels

 

I'm interested in trying to figure out which channels have been subscribed to (using LISTEN).  From what I could tell via a little Googling, there used to be a table named pg_catalog.pg_listener that contained all this information, but that seems to have disappeared somewhere in the 9.x release (I'm using 9.3.x).

 

Is there a way to find out which channels have listeners?

 

thanks

--Cory

 

 

Take a look at pg_listening_channels() in PG docs.

 

Regards,

Igor Neyman

Re: Determine all listeners subscribed to notifcations and what channels

От
Tom Lane
Дата:
Cory Tucker <cory.tucker@gmail.com> writes:
> I'm interested in trying to figure out which channels have been subscribed
> to (using LISTEN).  From what I could tell via a little Googling, there
> used to be a table named pg_catalog.pg_listener that contained all this
> information, but that seems to have disappeared somewhere in the 9.x
> release (I'm using 9.3.x).

> Is there a way to find out which channels have listeners?

No, not any more --- that capability was intentionally given up in the
9.0 LISTEN/NOTIFY rewrite.  Not that it wouldn't be nice to have, but
the cost/benefit ratio was pretty awful.

            regards, tom lane


Re: Determine all listeners subscribed to notifcations and what channels

От
Merlin Moncure
Дата:
On Tue, Feb 17, 2015 at 4:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Cory Tucker <cory.tucker@gmail.com> writes:
>> I'm interested in trying to figure out which channels have been subscribed
>> to (using LISTEN).  From what I could tell via a little Googling, there
>> used to be a table named pg_catalog.pg_listener that contained all this
>> information, but that seems to have disappeared somewhere in the 9.x
>> release (I'm using 9.3.x).
>
>> Is there a way to find out which channels have listeners?
>
> No, not any more --- that capability was intentionally given up in the
> 9.0 LISTEN/NOTIFY rewrite.  Not that it wouldn't be nice to have, but
> the cost/benefit ratio was pretty awful.

A userland wrapper could probably approximate this:
*) create a global id for each channel you want to listen on
(basically a table with channel names and a sequence)
*) create a function that looks up the id by channel and sharelocks
the id with an advisory lock, then listens on it
*) the advisory locks will clean themselves up when session ends
*) you can scan pg_locks table for type=advisory lock, pid, and the
ids of interest to get the data you want

merlin