Postgres Pro
Downloads
Home   >   mailing lists

Thread: Contents of pg_listener linger?

Contents of pg_listener linger?

From
Jeff Boes
Date:
What causes the contents of pg_listener to "linger" long past the lifespan of
the associated database backend? My table has rows that I know must be days old.

--
~~~~~~~~~~~~~~~~| Genius may have its limitations, but stupidity is not
Jeff Boes       | thus handicapped.
jboes@qtm.net   |        --Elbert Hubbard (1856-1915), American author

Re: Contents of pg_listener linger?

From
Tom Lane
Date:
Jeff Boes <jboes@nexcerpt.com> writes:
> What causes the contents of pg_listener to "linger" long past the lifespan of
> the associated database backend? My table has rows that I know must be days old.

PG version?

I have a vague recollection of old bugs that prevented pg_listener rows
from being scavenged during backend exit, but they're all gone AFAIK.
The only remaining condition that would account for this is a crash of
the listening backend, but I trust that's not what you're dealing with.

In any case, if there is a row for a dead backend it should be removed
the first time someone tries to "notify" that condition name and
discovers that he can't signal the dead backend.  If you've got days-old
dead rows they must be for very-infrequently-notified conditions.

            regards, tom lane

Re: Contents of pg_listener linger?

From
Tom Lane
Date:
Jeff Boes <jboes@nexcerpt.com> writes:
> Yup, that seems to be the case. When I "NOTIFY" these, they disappear.
> Any other way to clean them up?

If you're superuser you can delete rows all you want.  You could
probably cons up something that deletes rows with PIDs not found in
the pg_stat_activity view.

I'm more interested in how the rows got there though.  The bugs I was
thinking of were surely stomped long before 7.3.4, so if you're not
suffering crashes then you must have found a new one.  Please keep an
eye on it and see if you can figure out the circumstances in which a row
gets left behind.  Since you know the PIDs of the delinquent backends,
maybe you could look at the last related entries in the postmaster log
(turn on log_pid if you're not using syslog for logging).

            regards, tom lane

Re: Contents of pg_listener linger?

From
Jeff Boes
Date:
Tom Lane wrote:

>Jeff Boes <jboes@nexcerpt.com> writes:
>
>
>>What causes the contents of pg_listener to "linger" long past the lifespan of
>>the associated database backend? My table has rows that I know must be days old.
>>
>>
>
>PG version?
>
>
>
7.3.4

>In any case, if there is a row for a dead backend it should be removed
>the first time someone tries to "notify" that condition name and
>discovers that he can't signal the dead backend.  If you've got days-old
>dead rows they must be for very-infrequently-notified conditions.
>
>            regards, tom lane
>
>
Yup, that seems to be the case. When I "NOTIFY" these, they disappear.
Any other way to clean them up?  The problem is that there's one legit
listener, and umpteen dead ones. I can rig up something to notify all
these but I have to make sure that the legit listener is offline.

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise