Check the wait state for the backend process. I’ve seen this happen when a process ran a per-row trigger that tried to connect to something else, and it filled the ip_conntrack table. So, kernel level wait along with a whole bunch of locks on the table in question.
Running pg_terminate_backend() didn’t work, as the signal queued behind the kernel wait. We had to bounce the database to get rid of the problem. Immediately afterwards we disabled the trigger.
HTH,
Bob Lunney
Hi guys,
I had a question. We're running Postgresql 9.0.7 , and all of a sudden we started seeing unexpected behavior. One table got locked after we ran one simple INSERT on one test item.
Nothing else was running against that table as far as we know, and the query is frozen there now. After some time we tried to terminate it forcefully (pg_terminate_backend), ran that and the return of the command is "true" but the query still stays on, shows up on pg_stat_activity etc.
Reading from the table is fine, but we believe any other inserts / updates are not possible.
Restarting the Postgresql server is not an option at this moment, since it's a production box and another high-demand database is running from that.
Has anyone seen a similar issue (maybe a bug in the 9.0 version ?) where simple statements don't finish and get locked there and can't be killed by the pg_terminate_backed ? What is the cause of this ?
Any reasonable way to find out more details on what caused this, how to prevent it in the future, and how it can be fixed sensitively now ? Thanks !
just fyi, checking the data in the "pg_locks" (for that process that is frozen), shows a bunch of rows all with the same virtualtransactionid. Only one of them has the mode of "ExclusiveLock" (the only record there with a locktype of "virtualxid"), a few have the "RowExclusiveLock" mode and the vast majority have the "AccessShareLock" mode.
Thanks,
Ciprian