<IDLE> and waiting

Hi guys,

I saw a strange behaviour on one of the production boxes. The
pg_stat_activity shows a process as <IDLE> and yet 'waiting' !!! On top of
it (understandably, since its IDLE), there are no entries for this pid in
pg_locks!

Following are the snapshots of the two system views.

procpid | current_query | waiting | duration |
backend_start
---------+-----------------------+---------+------------------+-------------------------------
20762 | <IDLE> | f | | 2008-01-31
13:38:30.848898-08
19776 | <IDLE> | t | 00:38:34.76833 | 2008-01-31
12:51:29.005744-08
20356 | <IDLE> | f | 00:38:29.971425 | 2008-01-31
13:17:37.617497-08
19775 | <IDLE> | f | 00:38:27.187201 | 2008-01-31
12:51:28.999242-08
19774 | <IDLE> | f | 00:38:27.187068 | 2008-01-31
12:51:28.90554-08
20728 | <IDLE> | f | 00:14:03.913027 | 2008-01-31
13:36:11.345822-08
9727 | <IDLE> | f | 00:03:07.444273 | 2008-01-24
22:25:00.289931-08
9684 | <IDLE> | f | 00:00:07.704656 | 2008-01-24
22:22:00.007377-08
19390 | <IDLE> in transaction | f | 00:00:00.027585 | 2008-01-31
12:30:07.999246-08
19389 | <IDLE> in transaction | t | -00:00:00.000255 | 2008-01-31
12:30:07.973868-08

select * from pg_locks where pid in ( 19776, 19389 );

locktype | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid | mode |
granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+------------------+---------
relation | 16584 | 17070 | | |
| | | | 3700350056 | 19389 | RowExclusiveLock | t
relation | 16584 | 17106 | | |
| | | | 3700350056 | 19389 | RowExclusiveLock | t
relation | 16584 | 17068 | | |
| | | | 3700350056 | 19389 | RowExclusiveLock | t
transactionid | | | | | 3700350056
| | | | 3700350056 | 19389 | ExclusiveLock | t
relation | 16584 | 17108 | | |
| | | | 3700350056 | 19389 | RowExclusiveLock | t
(5 rows)

The 'duration' column above is just now()-query_start. These are not
just two instant snapshots, but we could see this output consistently for
quite long.

I tracked the 'waiting' column a little bit in the source code, and saw
that it is actually generated from PgBackendStatus.st_waiting . Is it
possible that, for some reason, postgres forgot to update this for a
backend?

select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.0 (SUSE Linux)

This issue has been seen twice now.

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device