¿Cómo detectar la consulta que tiene el bloqueo en Postgres?

Desde la versión 9.6 esto es mucho más fácil ya que se introdujo la función pg_blocking_pids() para encontrar las sesiones que están bloqueando a otra sesión.

Entonces puedes usar algo así:

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;

De este excelente artículo sobre bloqueos de consultas en Postgres, se puede obtener la consulta bloqueada, la consulta bloqueadora y su información de la siguiente consulta.

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

SELECT * from lock_monitor;

Como la consulta es larga pero útil, el autor del artículo ha creado una vista para simplificar su uso.

Esta modificación de la respuesta de a_horse_with_no_name te dará la última consulta (o la actual, si aún está activa) de la sesión bloqueante además de solo las sesiones bloqueadas:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Esto te ayuda a entender qué operaciones están interfiriendo entre sí (incluso si el bloqueo proviene de una consulta anterior), ayudándote a comprender el impacto de terminar una sesión y a encontrar formas de prevenir bloqueos en el futuro.

Cómo mostrar todas las consultas bloqueadas.

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;

Puedes matar una consulta bloqueada usando el siguiente comando.

SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid);

Puedes terminar todas las consultas bloqueadas usando esto.

SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid)
FROM( 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) a

Postgres tiene un catálogo de sistema muy completo expuesto a través de tablas SQL. El recolector de estadísticas de PG es un subsistema que admite la recopilación e informe de información sobre la actividad del servidor.

Ahora, para averiguar los PIDs de bloqueo simplemente puedes consultar pg_stat_activity.

select pg_blocking_pids(pid) as blocked_by
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Para obtener la consulta correspondiente al PID de bloqueo, puedes hacer una auto-unión o utilizarlo como cláusula WHERE en una subconsulta.

SELECT query
FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0);

Nota: Dado que pg_blocking_pids(pid) devuelve un Integer[], necesitas unnest antes de usarlo en una cláusula WHERE pid IN.

Buscar consultas lentas puede ser tedioso a veces, así que ten paciencia. ¡Feliz caza!