deadlocks in postgresql 7.2.1

Hi,

We use postgresql 7.2.1 (actually the Debian binary packet version
7.2.1-2woody2)
Our database load has more QUERIES than INSERT/DELETES. But we have a
sever problem with the wirting operations.

Once in a while (about 3 times a day) one or more INSERTS/DELETES simply
go into the "waiting" state, and block the whole database. The only way
out is to terminate the client connection (i.e. to abort the blocked
INSERT/DELETE query)

Further investigation with ps -e -o wchan... showed that the backed
process was simply sleeping in "semop".

Output of ps:

762 ? S 0:00 /usr/lib/postgresql/bin/postmaster
764 ? S 0:00 postgres: stats buffer process
765 ? S 0:00 postgres: stats collector process
24872 ? S 0:00 postgres: sd sd 10.2.2.6 idle in transaction
24873 ? R 68:01 postgres: sd sd 10.2.2.6 SELECT
24932 ? S 3:09 postgres: sd sd 10.2.2.6 idle in transaction
24943 ? R 3:02 postgres: sd sd 10.2.2.6 SELECT
25004 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21226 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21228 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21229 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21230 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21231 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21232 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21227 ? S 0:01 postgres: sd sd 10.2.1.5 INSERT waiting
21391 pts/1 S 0:00 grep postg

762 ? S 0:00 /usr/lib/postgresql/bin/postmaster
764 ? S 0:00 postgres: stats buffer process
765 ? S 0:00 postgres: stats collector process
24872 ? S 0:00 postgres: sd sd 10.2.2.6 idle in transaction
24873 ? S 68:02 postgres: sd sd 10.2.2.6 idle in transaction
24932 ? S 3:09 postgres: sd sd 10.2.2.6 idle in transaction
24943 ? S 3:04 postgres: sd sd 10.2.2.6 idle in transaction
25004 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21226 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21228 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21229 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21230 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21231 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21232 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21227 ? S 0:01 postgres: sd sd 10.2.1.5 INSERT waiting

762 ? S 0:00 /usr/lib/postgresql/bin/postmaster
764 ? S 0:00 postgres: stats buffer process
765 ? S 0:00 postgres: stats collector process
10291 ? S 0:05 postgres: sd sd 10.2.1.5 idle in transaction
4303 ? S 0:00 postgres: sd sd 10.2.2.6 idle in transaction
4304 ? S 40:43 postgres: sd sd 10.2.2.6 idle in transaction
4370 ? S 2:54 postgres: sd sd 10.2.2.6 idle in transaction
4384 ? S 3:12 postgres: sd sd 10.2.2.6 idle in transaction
19961 ? S 4:32 postgres: sd sd 10.2.1.5 idle in transaction
19962 ? S 4:40 postgres: sd sd 10.2.1.5 idle in transaction
19963 ? S 4:24 postgres: sd sd 10.2.1.5 DELETE waiting
19964 ? S 4:58 postgres: sd sd 10.2.1.5 idle in transaction
19965 ? S 4:18 postgres: sd sd 10.2.1.5 idle in transaction
19966 ? S 3:49 postgres: sd sd 10.2.1.5 idle in transaction
5574 ? S 2:50 postgres: sd sd 10.2.1.5 idle in transaction
5575 ? S 2:54 postgres: sd sd 10.2.1.5 idle in transaction
8744 pts/0 S 0:00 /usr/lib/postgresql/bin/psql -d sd
8751 ? S 0:00 postgres: sd sd [local] idle
16606 ? S 0:03 postgres: sd sd 10.2.1.5 idle in transaction

To get rid of the problem we tried to upgrade to 7.3.3. But see next
mail for our experiences with 7.3.3.

-Philipp
--
: Dipl-Ing Philipp Reisner Tel +43-1-8178292-50 :
: LINBIT Information Technologies GmbH Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria http://www.linbit.com :