<IDLE> and waiting

["Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>]

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