Polling pg_stat_activity vs pg_stat_statements for Idle-in-Transaction Alerting: Which Minimizes Noise?

Detecting Idle‑in‑Transaction Sessions with Minimal Noise

The goal is to raise an alert when a PostgreSQL session remains idle inside a transaction long enough to risk lock contention, while avoiding repeated notifications for the same session.

Two documented techniques exist: periodic polling of pg_stat_activity filtered by state = 'idle in transaction' and query age, or using pg_stat_statements to accumulate total execution time and trigger alerts via a background worker. The interaction of idle_in_transaction_session_timeout with log_min_duration_statement and the state reported in pg_stat_activity is not fully specified—it is unclear whether a session terminated by the timeout is still shown as idle in transaction at the instant of timeout, which could cause polling‑based alerts to fire simultaneously with a timeout‑generated log entry, producing duplicate notifications.

Does pg_stat_activity continue to report state='idle in transaction' for a session that is being ended by idle_in_transaction_session_timeout?
Does combining a timeout‑driven log entry with a polling‑based alert lead to redundant notifications?
Which of the two approaches—polling pg_stat_activity or aggregating with pg_stat_statements—provides reliable detection of lock‑holding idle sessions while minimizing alert noise given this uncertainty?