Trouver les transactions longues et 'idle in transaction'

Commande

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;

Ce que fait la commande

Affiche les sessions dont la transaction ouverte a plus de 5 minutes ou qui restent inactives au sein d'une transaction ; avec l'âge de la transaction, le temps passé dans l'état courant, le XID attribué et l'âge du xmin qu'elles retiennent.

Quand l'utiliser

Lorsque le bloat augmente, que VACUUM signale 'dead row versions cannot be removed yet', pendant des attentes de verrous, ou lorsqu'une fuite du pool de connexions est suspectée.

Attention

Les transactions longues (surtout REPEATABLE READ/SERIALIZABLE ou celles qui gardent des curseurs ouverts) retiennent l'horizon de nettoyage de VACUUM (xmin horizon) via backend_xmin, ce qui provoque du bloat et le vieillissement des XID dans toute la base. 'idle in transaction (aborted)' est une session qui a rencontré une erreur et attend un ROLLBACK. Correction permanente : idle_in_transaction_session_timeout par rôle et des limites de transaction correctes dans l'application.