Re: 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(dot)linnakangas(at)enterprisedb(dot)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(dot)linnakangas(at)enterprisedb(dot)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(at)dunslane(dot)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(at)turnstep(dot)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(at)turnstep(dot)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(at)pgadmin(dot)org> wrote:
> On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan <andrew(at)dunslane(dot)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(at)pgadmin(dot)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(at)pgadmin(dot)org> wrote:
>> On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan<andrew(at)dunslane(dot)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(at)ca(dot)afilias(dot)info> wrote:
> On 11-06-01 09:30 AM, Christopher Browne wrote:
>>
>> On Wed, Jun 1, 2011 at 8:29 AM, Dave Page<dpage(at)pgadmin(dot)org>  wrote:
>>>
>>> On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan<andrew(at)dunslane(dot)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