Show blocking Postgres processes and kill them

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 | in transaction
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.