Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group