Обсуждение: 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
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
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
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