Show Blocking Postgres Processes And Kill Them In Postgresql

          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.