Re: Stale entries in pg_stat_activity

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