Обсуждение: pg_listener in 9.0
The pg_listener table was removed in 9.0 in the revamp of LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from the table to get information about Slony clusters - for example, the PID of the slon process or to check if a process is listening for a specific notification. This allows the app to indicate to the user if there is something wrong with their replication cluster. I can't find any way to get that information now - any ideas? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 01.06.2011 13:09, Dave Page wrote: > The pg_listener table was removed in 9.0 in the revamp of > LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from > the table to get information about Slony clusters - for example, the > PID of the slon process or to check if a process is listening for a > specific notification. This allows the app to indicate to the user if > there is something wrong with their replication cluster. > > I can't find any way to get that information now - any ideas? Hmm, my first thought was that we should add a view to display that information, but that's not possible, because we don't have that information in shared memory. The information on what channels are being listened on is now backend-local. Does the slon process set application_name? You could query pg_stat_activity with that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Wed, Jun 1, 2011 at 11:27 AM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > On 01.06.2011 13:09, Dave Page wrote: >> >> The pg_listener table was removed in 9.0 in the revamp of >> LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from >> the table to get information about Slony clusters - for example, the >> PID of the slon process or to check if a process is listening for a >> specific notification. This allows the app to indicate to the user if >> there is something wrong with their replication cluster. >> >> I can't find any way to get that information now - any ideas? > > Hmm, my first thought was that we should add a view to display that > information, but that's not possible, because we don't have that information > in shared memory. The information on what channels are being listened on is > now backend-local. > > Does the slon process set application_name? You could query pg_stat_activity > with that. I don't think so (though I might be wrong), but even if it did, it wouldn't tell us what cluster it was running against (we figure that out by looking at what it's listening for). We also do the same check in reverse, to check there is something listening for specific notifications. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 06/01/2011 08:04 AM, Dave Page wrote: > On Wed, Jun 1, 2011 at 11:27 AM, Heikki Linnakangas > <heikki.linnakangas@enterprisedb.com> wrote: >> On 01.06.2011 13:09, Dave Page wrote: >>> The pg_listener table was removed in 9.0 in the revamp of >>> LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from >>> the table to get information about Slony clusters - for example, the >>> PID of the slon process or to check if a process is listening for a >>> specific notification. This allows the app to indicate to the user if >>> there is something wrong with their replication cluster. >>> >>> I can't find any way to get that information now - any ideas? >> Hmm, my first thought was that we should add a view to display that >> information, but that's not possible, because we don't have that information >> in shared memory. The information on what channels are being listened on is >> now backend-local. >> >> Does the slon process set application_name? You could query pg_stat_activity >> with that. > I don't think so (though I might be wrong), but even if it did, it > wouldn't tell us what cluster it was running against (we figure that > out by looking at what it's listening for). We also do the same check > in reverse, to check there is something listening for specific > notifications. > The whole point of the revamp was that pg_listener was a major performance bottleneck and needed to go, and without it being gone we would not have got notification payloads. I suspect you're pretty much out of luck. cheers andrew
On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > > The whole point of the revamp was that pg_listener was a major performance > bottleneck and needed to go, and without it being gone we would not have got > notification payloads. Yeah, I know why it was replaced. That doesn't mean we cannot provide an alternative interface to the same info though (other things might of course). > I suspect you're pretty much out of luck. Not me - our users. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > The pg_listener table was removed in 9.0 in the revamp of > LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from > the table to get information about Slony clusters - for example, the > PID of the slon process or to check if a process is listening for a > specific notification. This allows the app to indicate to the user if > there is something wrong with their replication cluster. > > I can't find any way to get that information now - any ideas? Nope, you are out of luck: the information is locked away and cannot be seen by other processes. I'm sure of this because Bucardo went through the same questioning some time ago. We basically rewrote the app a bit to use the on-disk PID files to replace some of the lost functionality, and sucked up the rest. :) - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201106010838 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk3mNEAACgkQvJuQZxSWSsh8LQCeKD/ot4mvXXd5Lgk4sIHwV0D2 CKsAn3Ub9Bdh0Fuyc0rDZr/OiSD8tkXq =cdCn -----END PGP SIGNATURE-----
On Wed, Jun 1, 2011 at 12:45 PM, Greg Sabino Mullane <greg@turnstep.com> wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > >> The pg_listener table was removed in 9.0 in the revamp of >> LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from >> the table to get information about Slony clusters - for example, the >> PID of the slon process or to check if a process is listening for a >> specific notification. This allows the app to indicate to the user if >> there is something wrong with their replication cluster. >> >> I can't find any way to get that information now - any ideas? > > Nope, you are out of luck: the information is locked away and cannot > be seen by other processes. I'm sure of this because Bucardo > went through the same questioning some time ago. We basically rewrote > the app a bit to use the on-disk PID files to replace some of the > lost functionality, and sucked up the rest. :) :-( -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Jun 1, 2011 at 8:29 AM, Dave Page <dpage@pgadmin.org> wrote: > On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >> >> The whole point of the revamp was that pg_listener was a major performance >> bottleneck and needed to go, and without it being gone we would not have got >> notification payloads. > > Yeah, I know why it was replaced. That doesn't mean we cannot provide > an alternative interface to the same info though (other things might > of course). > >> I suspect you're pretty much out of luck. > > Not me - our users. Note that in Slony 2.1, there's a table called sl_components, which is used to capture the state of the various database connections, checking in as the various threads do their various actions. Also, slon and slonik try to report their respective application, so it can be reported on pg_stat_activity. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
On Wed, Jun 1, 2011 at 5:09 AM, Dave Page <dpage@pgadmin.org> wrote: > The pg_listener table was removed in 9.0 in the revamp of > LISTEN/NOTIFY. In pgAdmin we used to perform a number of selects from > the table to get information about Slony clusters - for example, the > PID of the slon process or to check if a process is listening for a > specific notification. This allows the app to indicate to the user if > there is something wrong with their replication cluster. > > I can't find any way to get that information now - any ideas? Although it might not be helpful in your case, you can emulate certain aspects of this with an advisory lock...you can query the lock table for specific locks, and it goes away when the connection dies. merlin
On 11-06-01 09:30 AM, Christopher Browne wrote: > On Wed, Jun 1, 2011 at 8:29 AM, Dave Page<dpage@pgadmin.org> wrote: >> On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan<andrew@dunslane.net> wrote: >>> The whole point of the revamp was that pg_listener was a major performance >>> bottleneck and needed to go, and without it being gone we would not have got >>> notification payloads. >> Yeah, I know why it was replaced. That doesn't mean we cannot provide >> an alternative interface to the same info though (other things might >> of course). >> >>> I suspect you're pretty much out of luck. >> Not me - our users. > Note that in Slony 2.1, there's a table called sl_components, which is > used to capture the state of the various database connections, > checking in as the various threads do their various actions. > > Also, slon and slonik try to report their respective application, so > it can be reported on pg_stat_activity. Slony 2.1 also sets application_name. If this were a big deal for pgAdmin we could consider backporting the application_name change to 2.0.x for users running against 9.0. Slony also has a table called sl_nodelock that each slon process writes adds a row for on startup. This includes the backend pid() for one of the connections. Slony 1.2, 2.0 and 2.1 all use sl_nodelock
On Wed, Jun 1, 2011 at 3:02 PM, Steve Singer <ssinger@ca.afilias.info> wrote: > On 11-06-01 09:30 AM, Christopher Browne wrote: >> >> On Wed, Jun 1, 2011 at 8:29 AM, Dave Page<dpage@pgadmin.org> wrote: >>> >>> On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan<andrew@dunslane.net> >>> wrote: >>>> >>>> The whole point of the revamp was that pg_listener was a major >>>> performance >>>> bottleneck and needed to go, and without it being gone we would not have >>>> got >>>> notification payloads. >>> >>> Yeah, I know why it was replaced. That doesn't mean we cannot provide >>> an alternative interface to the same info though (other things might >>> of course). >>> >>>> I suspect you're pretty much out of luck. >>> >>> Not me - our users. >> >> Note that in Slony 2.1, there's a table called sl_components, which is >> used to capture the state of the various database connections, >> checking in as the various threads do their various actions. >> >> Also, slon and slonik try to report their respective application, so >> it can be reported on pg_stat_activity. > > Slony 2.1 also sets application_name. > > If this were a big deal for pgAdmin we could consider backporting the > application_name change to 2.0.x for users running against 9.0. > > Slony also has a table called sl_nodelock that each slon process writes adds > a row for on startup. This includes the backend pid() for one of the > connections. Slony 1.2, 2.0 and 2.1 all use sl_nodelock Thanks - I've committed changes that use pg_stat_activity and sl_nodelock to try to figure out what's currently going on. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company