Re: deadlocks in postgresql 7.2.1

Philipp Reisner writes:

> 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
[snip]

All these "idle in transaction" sessions have unfinished transactions that
are probably holding locks that the INSERT is waiting for. If you
constantly have loads of "idle in transaction" sessions, you need to fix
your application.

In 7.3 there is a system table called pg_locks that you can use to
investigate locks. I don't believe there was one in 7.2.

--
Peter Eisentraut peter_e(at)gmx(dot)net