My application's connections against PostgreSQL 8.1.4 seem to get
stuck in deletion operations.
Some sample ps output:
postgres 18198 10.5 20.7 1072088 863040 ? S 11:59 14:23
postgres: [...] DELETE waiting
postgres 18204 11.5 20.8 1072692 867708 ? S 11:59 15:43
postgres: [...] DELETE waiting
postgres 18208 14.2 22.3 1071968 928656 ? S 11:59 19:23
postgres: [...] DELETE waiting
postgres 18214 7.5 20.3 1072120 845832 ? S 11:59 10:12
postgres: [...] DELETE waiting
postgres 18216 12.8 23.5 1072000 977688 ? S 12:00 17:26
postgres: [...] DELETE waiting
The processes have these locks:
# select * from pg_locks where pid in (18198, 18204, 18208, 18214,
18216);
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 | | | | | 2989710024
| | | | 2989710024 | 18204 | ExclusiveLock
| t
relation | 1231506 | 2840720 | | |
| | | | 2991168469 | 18198 | AccessShareLock
| 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
tuple | 1231506 | 1231625 | 1607 | 63 |
| | | | 2990523423 | 18208 | ExclusiveLock
| f
transactionid | | | | | 2990486433
| | | | 2991168469 | 18198 | ShareLock
| f
transactionid | | | | | 2989804263
| | | | 2989804263 | 18216 | ExclusiveLock
| t
tuple | 1231506 | 1231625 | 1607 | 63 |
| | | | 2989801133 | 18214 | ExclusiveLock
| f
transactionid | | | | | 2991168469
| | | | 2991168469 | 18198 | ExclusiveLock
| t
transactionid | | | | | 2989801133
| | | | 2989801133 | 18214 | ExclusiveLock
| t
tuple | 1231506 | 1231625 | 2148 | 27 |
| | | | 2991168469 | 18198 | ExclusiveLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2991168469 | 18198 | AccessShareLock
| t
relation | 1231506 | 1231625 | | |
| | | | 2991168469 | 18198 | RowExclusiveLock
| 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
(27 rows)
They all seem to be trying to get exclusive locks on page 1607, one
of which was granted to process 18204 but never relinquished. If I
kill the application, the postgres processes stick around forever
until I restart the postmaster process.
My deadlock_timeout setting is set to the default.
Alexander.
