Come identificare le transazioni bloccate in PostgreSQL?

In PostgreSQL, le transazioni in sospeso sono quelle che rimangono a lungo in stato di "active" o "idle in transaction" senza terminare. Per identificarle, si può usare una query sul catalogo di sistema pg_stat_activity.

Esempio di query per trovare queste transazioni:

SELECT pid, usename, state, query, xact_start, now() - xact_start AS duration
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;

Qui, si osservano le transazioni che sono in stato "idle in transaction" o "active" e che durano più di 5 minuti. Tali transazioni possono bloccare altre operazioni e causare blocchi.

Per un'analisi più approfondita, si può verificare i blocchi tramite pg_locks e collegarli alle transazioni di pg_stat_activity.