Stale entries in pg_stat_activity

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Stale entries in pg_stat_activity
Date: 2004-10-17 02:45:10
Message-ID: 20041017024510.GA82115@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

PostgreSQL 8.0.0beta3 (CVS)
Solaris 9

pg_stat_activity sometimes shows old connections that have already
terminated. I've been able to reproduce this somewhat consistently
by doing the following:

createdb test
pgbench -i test
sudo /etc/init.d/postgresql restart
pgbench -S -c 30 -t 20 test
psql -c 'SELECT * from pg_stat_activity'

(Sorry for the long lines, but on a wide terminal the output is
easier to read than if I had used -x to enable expanded output.)

datid | datname | procpid | usesysid | usename | current_query | query_start
-------+---------+---------+----------+----------+-------------------------------------------------+-------------------------------
38894 | test | 28497 | 1 | postgres | <IDLE> | 2004-10-16 20:40:17.247942-06
38894 | test | 28494 | 1 | postgres | <IDLE> | 2004-10-16 20:40:17.247759-06
38894 | test | 28487 | 1 | postgres | <IDLE> | 2004-10-16 20:40:16.185687-06
38894 | test | 28481 | 1 | postgres | <IDLE> | 2004-10-16 20:40:17.120745-06
38894 | test | 28479 | 1 | postgres | <IDLE> | 2004-10-16 20:40:16.185514-06
38894 | test | 28477 | 1 | postgres | <IDLE> | 2004-10-16 20:40:17.119938-06
38894 | test | 28470 | 1 | postgres | select abalance from accounts where aid = 32673 | 2004-10-16 20:40:17.248236-06
38894 | test | 28462 | 1 | postgres | <IDLE> | 2004-10-16 20:40:16.200115-06
38894 | test | 28463 | 1 | postgres | <IDLE> | 2004-10-16 20:40:17.248175-06
(9 rows)

This is a test box with no other activity. ps shows that the listed
processes don't exist and netstat shows no open TCP or Unix socket
connections to the database. Before running pgbench I restart the
backend to be sure it has a clean slate. The number of rows varies
from test to test -- sometimes there are none and I've seen as many
as 19 with the pgbench options shown. Sometimes current_query shows
<IDLE> for all connections and sometimes it shows a query for some
or all of them.

Any ideas?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-10-17 04:01:45 Re: Stale entries in pg_stat_activity
Previous Message Oliver Elphick 2004-10-16 17:35:16 Re: SELECT FOR UPDATE and LIMIT 1 behave oddly