Encontrar transacciones de larga duración 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;

Qué hace

Muestra las sesiones cuya transacción abierta tiene más de 5 minutos o que están inactivas dentro de una transacción; con la antigüedad de la transacción, el tiempo en el estado actual, el XID asignado y la antigüedad del xmin que retienen.

Cuándo usarlo

Cuando crece el bloat, VACUUM informa de 'dead row versions cannot be removed yet', durante esperas de bloqueo o cuando se sospecha una fuga en el connection pool.

Precaución

Las transacciones largas (sobre todo REPEATABLE READ/SERIALIZABLE o las que mantienen cursores abiertos) frenan el horizonte de limpieza de VACUUM (xmin horizon) mediante backend_xmin, causando bloat y envejecimiento de XID en toda la base de datos. 'idle in transaction (aborted)' es una sesión que tuvo un error y espera un ROLLBACK. Solución permanente: idle_in_transaction_session_timeout por rol y límites de transacción correctos en la aplicación.