Re: Stale entries in pg_stat_activity

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Michael Fuhr <mike(at)fuhr(dot)org> writes:
>>>> pg_stat_activity sometimes shows old connections that have already
>>>> terminated.
>>> 
>>> Define "old"?  There's a built-in delay of up to half a second (if
>>> memory serves) in the statistics views.
>> 
>> Fourteen minutes and counting.

> Two hours and counting.  I've also reproduced the problem using
> pgbench against 8.0.0beta3 on FreeBSD 4.10-STABLE.

The other likely theory is that the stats subsystem is under
sufficiently heavy load that it drops a certain proportion of stats
messages (as it is designed to do, rather than causing foreground
activity to block).  If it were to drop a backend-termination message
then of course that connection would remain depicted in pg_stat_activity
until the stats collector figures out that the backend is gone.  Which
IIRC would normally happen when some new backend re-uses its BackendId
slot.

You could check for log messages "statistics buffer is full" as a
partial check of this theory.  This is only partial because it isn't
a direct test of whether messages are being dropped.

			regards, tom lane