Contents of pg_listener linger?

[Jeff Boes]

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.
***@qtm.net | --Elbert Hubbard (1856-1915), American author

[Tom Lane]

Post by Jeff Boes
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Jeff Boes]

Post by Tom Lane

Post by Jeff Boes
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

Post by Tom Lane
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 [phone]
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match