PostgreSQL 9 Administration Cookbook - Second Edition

Knowing who is blocking a query

Once you have found out that a query is blocked, you need to know who or what is blocking them.

Getting ready

Just get a superuser account to run the queries.

How to do it…

Run the following query on PostgreSQL 9.2 or later versions:

SELECT
    w.query AS waiting_query,
    w.pid AS waiting_pid,
    w.usename AS waiting_user,
    l.query AS locking_query,
    l.pid AS locking_pid,
    l.usename AS locking_user,
    t.schemaname || '.' || t.relname AS tablename
 FROM pg_stat_activity w
       JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
       JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
       JOIN pg_stat_activity l ON l2.pid = l.pid
       JOIN pg_stat_user_tables t ON l1.relation = t.relid
 WHERE w.waiting;

This returns the process ...