Have you ever had to kill your Postgres cluster because one hanging client starved other processes until most clients became unresponsive blocking on this one pesky process?
There is a very nice way to show currently blocked queries and the processes those are blocking on slightly adapted from this query posted on the Postgres mailing list. I suggest putting it into a view so you can easily access it when you need it:
CREATE VIEW blocking_procs AS SELECT kl.pid as blocking_pid, ka.usename as blocking_user, ka.current_query as blocking_query, bl.pid as blocked_pid, a.usename as blocked_user, a.current_query as blocked_query, to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.procpid JOIN pg_catalog.pg_locks kl ON bl.locktype = kl.locktype and bl.database is not distinct from kl.database and bl.relation is not distinct from kl.relation and bl.page is not distinct from kl.page and bl.tuple is not distinct from kl.tuple and bl.virtualxid is not distinct from kl.virtualxid and bl.transactionid is not distinct from kl.transactionid and bl.classid is not distinct from kl.classid and bl.objid is not distinct from kl.objid and bl.objsubid is not distinct from kl.objsubid and bl.pid <> kl.pid JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.procpid WHERE kl.granted and not bl.granted ORDER BY a.query_start;
How to test the query on a testing server (not your production DB server)
Connect to your database open a transaction and manually lock a table:
BEGIN;
LOCK your_table;
Leave the transaction and connection open.
Open another client that accesses that data:
# SELECT count(*) from your_table;
It now should be blocked.
View the currently held locks with a third client:
# SELECT * FROM blocking_procs;
blocking_pid | 25842
blocking_user | postgres
blocking_query |
blocked_pid | 25844
blocked_user | postgres
blocked_query | SELECT COUNT(*) FROM "your_table"
age | 00h:00m:23s
It's now possible to kill the offending process holding the lock using:
# SELECT pg_terminate_backend(25842);
This will kill the connection where you've set the lock and the open transaction is rolled back but it seems to leave everything else intact. The second client should now get the response from the server.
Tested on Postgres 9.1.