Encontrar transações longas e 'idle in transaction'

Comando

SELECT pid,
       usename,
       datname,
       application_name,
       client_addr,
       state,
       now() - xact_start AS xact_age,
       now() - state_change AS in_state_for,
       backend_xid,
       age(backend_xmin) AS xmin_age,
       left(regexp_replace(query, '\s+', ' ', 'g'), 100) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
  AND (now() - xact_start > interval '5 minutes' OR state LIKE 'idle in transaction%')
ORDER BY xact_start;

O que faz

Mostra as sessões cuja transação aberta tem mais de 5 minutos ou que estão ociosas dentro de uma transação; com a idade da transação, o tempo no estado atual, o XID atribuído e a idade do xmin que seguram.

Quando usar

Quando o bloat cresce, o VACUUM reporta 'dead row versions cannot be removed yet', durante esperas de lock ou quando se suspeita de vazamento no pool de conexões.

Atenção

Transações longas (especialmente REPEATABLE READ/SERIALIZABLE ou as que mantêm cursores abertos) seguram o horizonte de limpeza do VACUUM (xmin horizon) via backend_xmin, causando bloat e envelhecimento de XID em todo o banco. 'idle in transaction (aborted)' é uma sessão que teve um erro e está esperando ROLLBACK. Correção permanente: idle_in_transaction_session_timeout por role e limites de transação corretos na aplicação.