Construir el árbol de bloqueos (encontrar el bloqueador raíz)

Comando

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;

Qué hace

Sigue recursivamente la cadena de esperas con pg_blocking_pids(); partiendo del bloqueador raíz (que no espera a nadie), muestra cada sesión en espera como un árbol indentado con los PID que la bloquean directamente y la antigüedad de su transacción.

Cuándo usarlo

Cuando la aplicación sufre timeouts masivos o se acumulan colas; para encontrar, entre decenas de sesiones en espera, la única sesión raíz sobre la que hay que intervenir.

Precaución

La raíz suele ser una sesión 'idle in transaction' olvidada que retiene el bloqueo; la columna query muestra la ÚLTIMA sentencia de esa sesión, no la que tomó el bloqueo. Un DDL que espera ACCESS EXCLUSIVE también bloquea los SELECT simples encolados detrás de él y aparece como nodo intermedio. pg_blocking_pids() bloquea brevemente el lock manager; no ejecute esta consulta en un bucle cerrado. Una sesión alcanzable por varias ramas se muestra una sola vez, en su nivel más profundo.