Inspecionar em detalhe as esperas de lock por objeto e modo de lock (pg_locks)

Comando

SELECT DISTINCT
       w.pid AS waiting_pid,
       w.locktype,
       coalesce(w.relation::regclass::text, 'xid ' || w.transactionid, w.virtualxid, w.objid::text) AS target,
       w.mode AS wanted_mode,
       b.pid AS blocker_pid,
       b.mode AS blocker_mode,
       b.granted AS blocker_holds,
       now() - wa.state_change AS waiting_for,
       left(wa.query, 60) AS waiting_query,
       left(ba.query, 60) AS blocker_query
FROM pg_locks w
JOIN pg_locks b
  ON b.pid = ANY (pg_blocking_pids(w.pid))
 AND b.locktype = w.locktype
 AND b.database IS NOT DISTINCT FROM w.database
 AND b.relation IS NOT DISTINCT FROM w.relation
 AND b.page IS NOT DISTINCT FROM w.page
 AND b.tuple IS NOT DISTINCT FROM w.tuple
 AND b.virtualxid IS NOT DISTINCT FROM w.virtualxid
 AND b.transactionid IS NOT DISTINCT FROM w.transactionid
 AND b.classid IS NOT DISTINCT FROM w.classid
 AND b.objid IS NOT DISTINCT FROM w.objid
 AND b.objsubid IS NOT DISTINCT FROM w.objsubid
JOIN pg_stat_activity wa ON wa.pid = w.pid
JOIN pg_stat_activity ba ON ba.pid = b.pid
WHERE NOT w.granted
ORDER BY waiting_for DESC, waiting_pid;

O que faz

Para cada solicitação de lock não concedida (granted = false), mostra o objeto alvo (tabela, transaction id etc.), o modo solicitado, o PID bloqueador e seu modo, se o bloqueador de fato detém o lock e o tempo aproximado de espera.

Quando usar

Para entender qual tabela e qual conflito de modo de lock causam o bloqueio; ex.: um DDL esperando AccessExclusiveLock ou um lock de linha aparecendo como espera de transactionid.

Atenção

Como a correspondência usa pg_blocking_pids(), apenas solicitações realmente conflitantes são listadas. blocker_holds = false significa que o bloqueador ainda não detém o lock, mas está à frente na fila com um modo conflitante (lock queue). locktype = 'transactionid' é um conflito em nível de linha: quem espera aguarda até que termine a transação que modificou a linha; a própria linha não aparece em pg_locks. pg_locks pode ter milhares de linhas em sistemas grandes; não execute isto com frequência. waiting_for é uma aproximação baseada em state_change.