How to detect query which holds the lock in Postgres?

Question

I want to track mutual locks in postgres constantly.

I came across Locks Monitoring article and tried to run the following query:

SELECT bl.pid     AS blocked_pid,      a.usename  AS blocked_user,      kl.pid     AS blocking_pid,      ka.usename AS blocking_user,      a.query    AS blocked_statement FROM  pg_catalog.pg_locks         bl  JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid  JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid  JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid WHERE NOT bl.granted; 

Unfortunately, it never returns non-empty result set. If I simplify given query to the following form:

SELECT bl.pid     AS blocked_pid,      a.usename  AS blocked_user,      a.query    AS blocked_statement FROM  pg_catalog.pg_locks         bl  JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid WHERE NOT bl.granted; 

then it returns queries which are waiting to acquire a lock. But I cannot manage to change it so that it can return both blocked and blocker queries.

Any ideas?

Answer 1

Since 9.6 this is a lot easier as it introduced the function pg_blocking_pids() to find the sessions that are blocking another session.

So you can use something like this:

select pid,         usename,         pg_blocking_pids(pid) as blocked_by,         query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;