¿Cómo identificar transacciones colgadas en PostgreSQL?

En PostgreSQL, las transacciones colgadas son aquellas que permanecen durante mucho tiempo en estado de activo o idle in transaction, sin finalizar. Para identificarlas, se puede usar una consulta al catálogo del sistema pg_stat_activity.

Ejemplo de consulta para buscar estas transacciones:

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;

Aquí, observamos las transacciones que están en estado "idle in transaction" o "active" y que duran más de 5 minutos. Estas transacciones pueden bloquear otras operaciones y causar bloqueos.

Para un análisis más profundo, se puede verificar los bloqueos a través de pg_locks y relacionarlos con las transacciones de pg_stat_activity.