Build the blocking tree (find the root blocker)

Command

WITH RECURSIVE tree AS (
  SELECT a.pid, a.pid AS root_pid, 0 AS level, ARRAY[a.pid] AS path
  FROM pg_stat_activity a
  WHERE cardinality(pg_blocking_pids(a.pid)) = 0
    AND EXISTS (SELECT 1 FROM pg_stat_activity w WHERE a.pid = ANY (pg_blocking_pids(w.pid)))
  UNION ALL
  SELECT w.pid, t.root_pid, t.level + 1, t.path || w.pid
  FROM tree t
  JOIN pg_stat_activity w ON t.pid = ANY (pg_blocking_pids(w.pid))
  WHERE w.pid <> ALL (t.path)
), node AS (
  SELECT DISTINCT ON (pid) pid, root_pid, level
  FROM tree
  ORDER BY pid, level DESC
)
SELECT repeat('  ', n.level) || n.pid AS pid_tree,
       n.root_pid,
       pg_blocking_pids(n.pid) AS blocked_by,
       a.usename,
       a.state,
       a.wait_event_type || ':' || a.wait_event AS wait,
       now() - a.xact_start AS xact_age,
       left(regexp_replace(a.query, '\s+', ' ', 'g'), 80) AS query
FROM node n
JOIN pg_stat_activity a USING (pid)
ORDER BY n.root_pid, n.level, n.pid;

What it does

Follows the wait chain recursively with pg_blocking_pids(); starting from the root blocker (which waits for nobody), shows every waiting session as an indented tree with the PIDs directly blocking it and its transaction age.

When to use it

When the application sees mass timeouts or queues piling up; to find the single root session that needs intervention among dozens of waiters.

Caution

The root is often a forgotten 'idle in transaction' session holding the lock; the query column shows that session's LAST statement, not the one that took the lock. A DDL waiting for ACCESS EXCLUSIVE also blocks simple SELECTs queued behind it and shows up as an intermediate node. pg_blocking_pids() briefly locks the lock manager; do not run this query in a tight loop. A session reachable through several branches is shown once, at its deepest level.