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/
---------------------------(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
