Lang laufende und 'idle in transaction'-Transaktionen finden

Befehl

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;

Was es tut

Zeigt Sessions, deren offene Transaktion älter als 5 Minuten ist oder die inaktiv in einer Transaktion verharren; mit Transaktionsalter, Zeit im aktuellen Zustand, zugewiesener XID und dem Alter des gehaltenen xmin.

Wann einsetzen

Wenn Bloat wächst, VACUUM 'dead row versions cannot be removed yet' meldet, bei Sperrwartezuständen oder wenn ein Leck im Connection Pool vermutet wird.

Vorsicht

Lange Transaktionen (besonders REPEATABLE READ/SERIALIZABLE oder solche mit offenen Cursors) halten über backend_xmin den Bereinigungshorizont von VACUUM (xmin horizon) zurück und verursachen datenbankweit Bloat und XID-Alterung. 'idle in transaction (aborted)' ist eine Session, die auf einen Fehler gestoßen ist und auf ROLLBACK wartet. Dauerhafte Lösung: idle_in_transaction_session_timeout je Rolle und korrekte Transaktionsgrenzen in der Anwendung.