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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
