How to identify hanging transactions in PostgreSQL?

In PostgreSQL, hanging transactions are those that remain in an active or idle in transaction state for a long time without completing. To identify them, you can use a query to the system catalog pg_stat_activity.

Example query to find such transactions:

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;

Here, we look for transactions that are in the "idle in transaction" or "active" state and have been running for more than 5 minutes. Such transactions can block other operations and cause hangs.

For a more in-depth analysis, you can check for locks via pg_locks and relate them to transactions from pg_stat_activity.