Thread: Who is LISTENing?
Hi pgsql-general, I'm interested in writing a supervisory process that can insure worker processes are running/spawn new ones if not. These workers will mainly be responsible for LISTENing to the db, which is emitting triggered_change_notification s. Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it? Links: http://www.postgresql.org/docs/9.2/interactive/sql-notify.html http://www.postgresql.org/docs/9.2/interactive/tcn.html Regards,
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
body p { margin-bottom: 0cm; margin-top: 0pt; }
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" text="#000000"
bgcolor="#FFFFFF">
On 10/15/2012 06:11 PM, rektide wrote:
<blockquote cite="mid:20121015161138.GA17738@eldergods.com"
type="cite">
Hi pgsql-general,
I'm interested in writing a supervisory process that can insure worker processes are
running/spawn new ones if not. These workers will mainly be responsible for LISTENing to
the db, which is emitting triggered_change_notification s.
Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it?
Links:
http://www.postgresql.org/docs/9.2/interactive/sql-notify.html
http://www.postgresql.org/docs/9.2/interactive/tcn.html
Regards,
You can look in the pg_listener table. The relname is the
Listen/notify code that you call and the listenerpid is the OS pid.
You can see more details of that in the pg_stat_activity
Sim
On Tue, Oct 16, 2012 at 11:01 AM, Sim Zacks <sim@compulab.co.il> wrote:
You can look in the pg_listener table. The relname is the Listen/notify code that you call and the listenerpid is the OS pid. You can see more details of that in the pg_stat_activityOn 10/15/2012 06:11 PM, rektide wrote:Hi pgsql-general, I'm interested in writing a supervisory process that can insure worker processes are running/spawn new ones if not. These workers will mainly be responsible for LISTENing to the db, which is emitting triggered_change_notification s. Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it? Links: http://www.postgresql.org/docs/9.2/interactive/sql-notify.html http://www.postgresql.org/docs/9.2/interactive/tcn.html Regards,
Sim
I guess pg_listener table is deprecated and no longer exist in PG 9.0 onwards.
--Raghav
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
body p { margin-bottom: 0cm; margin-top: 0pt; }
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" text="#000000"
bgcolor="#FFFFFF">
On 10/16/2012 07:49 AM, Raghavendra
wrote:
<blockquote
cite="mid:CA+h6Ahiy7n-FEydc8M3BLPoCunVRV3cmwHvtxXqougDwM3uwgw@mail.gmail.com"
type="cite">
On Tue, Oct 16, 2012 at 11:01 AM, Sim
Zacks <<a moz-do-not-send="true"
href="mailto:sim@compulab.co.il" target="_blank">sim@compulab.co.il>
wrote:
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
On 10/15/2012 06:11 PM, rektide wrote:
Hi pgsql-general,
I'm interested in writing a supervisory process that can insure worker processes are
running/spawn new ones if not. These workers will mainly be responsible for LISTENing to
the db, which is emitting triggered_change_notification s.
Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it?
Links:
http://www.postgresql.org/docs/9.2/interactive/sql-notify.html
http://www.postgresql.org/docs/9.2/interactive/tcn.html
Regards,
You can look in the pg_listener table. The relname is the
Listen/notify code that you call and the listenerpid is the
OS pid. You can see more details of that in the
pg_stat_activity
Sim
I guess pg_listener table is deprecated and no longer exist
in PG 9.0 onwards.
--Raghav
I guess I'm dating myself (still on 8.2)
This discussion indicates that it is not possible.
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
<a
href="http://archives.postgresql.org/pgsql-hackers/2011-06/msg00016.php">http://archives.postgresql.org/pgsql-hackers/2011-06/msg00016.php
On 2012-10-15, rektide <rektide@voodoowarez.com> wrote: > Hi pgsql-general, > > I'm interested in writing a supervisory process that can insure worker processes are > running/spawn new ones if not. These workers will mainly be responsible for LISTENing to > the db, which is emitting triggered_change_notification s. > > Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it? > Notifies are not reliable, what I mean is they are "best effort" this is unlike the other things postgres does, there's no guarantee that you'll get the message, for example the network might go down at the same time as the notifiy is emitted, if that happenss a listening client would miss the notify message and by the time it reconnects the message is gone. If you need reliable mesaging use a mesage queue in a table: Emit a notify when you insert into the queue and the listeners can check the queue when they connect, and again after each notify. OTOH, if best effort is good enough, the table pg_stat_activity will give you the username of each connected client. perhaps ypu can infer from that who was probably listening when you last checked... -- ⚂⚃ 100% natural
On Tue, Oct 16, 2012 at 5:18 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
On 2012-10-15, rektide <rektide@voodoowarez.com> wrote:Notifies are not reliable, what I mean is they are "best effort"
> Hi pgsql-general,
>
> I'm interested in writing a supervisory process that can insure worker processes are
> running/spawn new ones if not. These workers will mainly be responsible for LISTENing to
> the db, which is emitting triggered_change_notification s.
>
> Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it?
>
this is unlike the other things postgres does, there's no guarantee
that you'll get the message, for example the network might go down at
the same time as the notifiy is emitted, if that happenss a listening
client would miss the notify message and by the time it reconnects the
message is gone.
If you need reliable mesaging use a mesage queue in a table:
Emit a notify when you insert into the queue and the listeners can check
the queue when they connect, and again after each notify.
OTOH, if best effort is good enough, the table pg_stat_activity will give
you the username of each connected client. perhaps ypu can infer from that
who was probably listening when you last checked...
One of the goals of pg_message_queue was to create a simple message queue system with listen/notify support (which is missing in pgq btw). It is designed to be reasonably reliable but is still relatively feature poor. It will probably never be big and professional like pgq, but may be very helpful in many cases nonetheless. http://pgxn.org/dist/pg_message_queue/
The basic idea is that it automates some of the basic bailing twine you might want to create in such a solution. Future versions will have more logging, anti-refutation controls, etc.
Note the way we addressed this problem was that listen/notify was optional. An application could be run from cron once a day and process queued items, or it could connect and wait for notifications.
As of 0.1, it supports payload types of text, xml, and bytea. More types coming soon in 0.2.
I would be interested in feedback on this, and if anyone wants to contribute, I certainly will be happy to facilitate.
Best Wishes,
Chris Travers