Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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
   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:

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.


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger