FAQ
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

Search Discussions

  • Heikki Linnakangas at Jun 1, 2011 at 11:27 am

    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
  • Dave Page at Jun 1, 2011 at 12:04 pm

    On Wed, Jun 1, 2011 at 11:27 AM, Heikki Linnakangas 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
  • Andrew Dunstan at Jun 1, 2011 at 12:27 pm

    On 06/01/2011 08:04 AM, Dave Page wrote:
    On Wed, Jun 1, 2011 at 11:27 AM, Heikki Linnakangas
    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
  • Dave Page at Jun 1, 2011 at 12:30 pm

    On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan 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
  • Christopher Browne at Jun 1, 2011 at 1:30 pm

    On Wed, Jun 1, 2011 at 8:29 AM, Dave Page wrote:
    On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan 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?"
  • Steve Singer at Jun 1, 2011 at 2:02 pm

    On 11-06-01 09:30 AM, Christopher Browne wrote:
    On Wed, Jun 1, 2011 at 8:29 AM, Dave Pagewrote:
    On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstanwrote:
    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
  • Dave Page at Jun 2, 2011 at 2:36 pm

    On Wed, Jun 1, 2011 at 3:02 PM, Steve Singer wrote:
    On 11-06-01 09:30 AM, Christopher Browne wrote:

    On Wed, Jun 1, 2011 at 8:29 AM, Dave Pagewrote:
    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
  • Greg Sabino Mullane at Jun 1, 2011 at 12:45 pm

    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
  • Dave Page at Jun 1, 2011 at 1:27 pm

    On Wed, Jun 1, 2011 at 12:45 PM, Greg Sabino Mullane 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
  • Merlin Moncure at Jun 1, 2011 at 1:55 pm

    On Wed, Jun 1, 2011 at 5:09 AM, 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?
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 1, '11 at 10:09a
activeJun 2, '11 at 2:36p
posts11
users7
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase