Re: Stuck in "DELETE waiting"

On Nov 23, 2006, at 16:27 , Tom Lane wrote:

> Alexander Staubo <alex(at)purefiction(dot)net> writes:
>> My application's connections against PostgreSQL 8.1.4 seem to get
>> stuck in deletion operations.
>> ...
>> # select * from pg_locks where pid in (18198, 18204, 18208, 18214,
>> 18216);
>
> You really need to show all of pg_locks, not a subset. In this
> example
> it appears that 18204 and 18198 are waiting for transactions that are
> not part of this subset, so it's hard to say what's happening.

See below. Seems 18204 is waiting for a process that is "idle in
transaction"; makes me wish that PostgreSQL could export more
information about the age of in-progress transactions. I am turning
on logging so I can determine what a future hanging process is doing.

# select * from pg_locks ;
locktype | database | relation | page | tuple | transactionid
| classid | objid | objsubid | transaction | pid | mode
| granted
---------------+----------+----------+------+-------+---------------
+---------+-------+----------+-------------+-------+------------------
+---------
relation | 1231506 | 1231625 | | |
| | | | 2989801133 | 18214 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2989801133 | 18214 | RowExclusiveLock
| t
transactionid | | | | | 2991244269
| | | | 2991244269 | 22971 | ExclusiveLock
| t
transactionid | | | | | 2990486433
| | | | 2990486433 | 18207 | ExclusiveLock
| t
relation | 1231506 | 1231650 | | |
| | | | 2989544980 | 18202 | AccessShareLock
| t
relation | 1231506 | 1231594 | | |
| | | | 2991244269 | 22971 | AccessShareLock
| t
transactionid | | | | | 2989710024
| | | | 2989710024 | 18204 | ExclusiveLock
| t
relation | 1231506 | 1231864 | | |
| | | | 2990486433 | 18207 | AccessShareLock
| t
relation | 1231506 | 10342 | | |
| | | | 2991277798 | 23406 | AccessShareLock
| t
relation | 1231506 | 1231864 | | |
| | | | 2991277508 | 22991 | AccessShareLock
| t
relation | 1231506 | 1231864 | | |
| | | | 2991277508 | 22991 | RowExclusiveLock
| t
transactionid | | | | | 2991277508
| | | | 2991277508 | 22991 | ExclusiveLock
| t
relation | 1231506 | 1231571 | | |
| | | | 2991244269 | 22971 | AccessShareLock
| t
relation | 1231506 | 2840720 | | |
| | | | 2991168469 | 18198 | AccessShareLock
| t
relation | 1231506 | 1231650 | | |
| | | | 2990741550 | 18199 | AccessShareLock
| t
relation | 1231506 | 1231650 | | |
| | | | 2990486433 | 18207 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2989544980 | 18202 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2989544980 | 18202 | RowExclusiveLock
| t
relation | 1231506 | 2840720 | | |
| | | | 2989804263 | 18216 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2990523423 | 18208 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2990523423 | 18208 | RowExclusiveLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2989804263 | 18216 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2989804263 | 18216 | RowExclusiveLock
| t
transactionid | | | | | 2989544980
| | | | 2989710024 | 18204 | ShareLock
| f
tuple | 1231506 | 1231625 | 1607 | 63 |
| | | | 2989710024 | 18204 | ExclusiveLock
| t
relation | 1231506 | 1231864 | | |
| | | | 2990741550 | 18199 | AccessShareLock
| t
relation | 1231506 | 1231864 | | |
| | | | 2989544980 | 18202 | AccessShareLock
| t
tuple | 1231506 | 1231625 | 1607 | 63 |
| | | | 2990523423 | 18208 | ExclusiveLock
| f
transactionid | | | | | 2990486433
| | | | 2991168469 | 18198 | ShareLock
| f
transactionid | | | | | 2989804263
| | | | 2989804263 | 18216 | ExclusiveLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2990741550 | 18199 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2990741550 | 18199 | RowExclusiveLock
| t
relation | 1231506 | 1231678 | | |
| | | | 2991244269 | 22971 | AccessShareLock
| t
tuple | 1231506 | 1231625 | 1607 | 63 |
| | | | 2989801133 | 18214 | ExclusiveLock
| f
transactionid | | | | | 2990741550
| | | | 2990741550 | 18199 | ExclusiveLock
| t
transactionid | | | | | 2991168469
| | | | 2991168469 | 18198 | ExclusiveLock
| t
relation | 1231506 | 1231864 | | |
| | | | 2991244269 | 22971 | AccessShareLock
| t
relation | 1231506 | 1231864 | | |
| | | | 2991244269 | 22971 | RowShareLock
| t
relation | 1231506 | 1231596 | | |
| | | | 2991244269 | 22971 | RowExclusiveLock
| t
transactionid | | | | | 2989801133
| | | | 2989801133 | 18214 | ExclusiveLock
| t
tuple | 1231506 | 1231625 | 2148 | 27 |
| | | | 2991168469 | 18198 | ExclusiveLock
| t
transactionid | | | | | 2989544980
| | | | 2989544980 | 18202 | ExclusiveLock
| t
transactionid | | | | | 2991277798
| | | | 2991277798 | 23406 | ExclusiveLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2991168469 | 18198 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2991168469 | 18198 | RowExclusiveLock
| t
relation | 1231506 | 1231644 | | |
| | | | 2989544980 | 18202 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2990486433 | 18207 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2990486433 | 18207 | RowExclusiveLock
| t
relation | 1231506 | 1231644 | | |
| | | | 2990486433 | 18207 | AccessShareLock
| t
relation | 1231506 | 1231644 | | |
| | | | 2990741550 | 18199 | AccessShareLock
| t
relation | 1231506 | 1231580 | | |
| | | | 2991244269 | 22971 | AccessShareLock
| t
relation | 1231506 | 1231668 | | |
| | | | 2991244269 | 22971 | AccessShareLock
| t
relation | 1231506 | 2840720 | | |
| | | | 2990523423 | 18208 | AccessShareLock
| t
relation | 1231506 | 2840720 | | |
| | | | 2989801133 | 18214 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2989710024 | 18204 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2989710024 | 18204 | RowExclusiveLock
| t
tuple | 1231506 | 1231625 | 1607 | 63 |
| | | | 2989804263 | 18216 | ExclusiveLock
| f
relation | 1231506 | 2840720 | | |
| | | | 2989710024 | 18204 | AccessShareLock
| t
transactionid | | | | | 2990523423
| | | | 2990523423 | 18208 | ExclusiveLock
| t
(59 rows)

Alexander.