Is there any way to log or find last executed statement from a session that terminated because of idle in transaction timeout? We only have slow statement logging and that did not capture it and we don't want to enable all statement logging as well as this will have bad impact on performance.
2 Answers
You get the last executed query for live transactions from pg_stat_activity (incl. transactions that sit idle in transaction):
SELECT backend_xid, backend_start, xact_start, query_start, query -- more?
FROM pg_catalog.pg_stat_activity
WHERE state = 'idle in transaction'
AND xact_start < now() - interval '3 sec' -- ①
ORDER BY xact_start;
① Some interval much shorter than your timeout, but long enough to filter the bulk of shorter transactions. In chose 3 sec for your case with short transactions and a sharp idle_in_transaction_session_timeout of only 10 sec. Each can be Can be substantially longer in other setups.
Run a cron job polling the above query into a log table in intervals slightly below that setting minus ①. The latest entry for your backend_xid will show the query you are looking for.
Since you are running this in relatively high frequency, and you don't need some columns the view joins in, you might squeeze out some fractions of a ms by calling the underlying function pg_stat_get_activity() directly. ORDER BY seems expendable, too:
SELECT backend_xid, backend_start, xact_start, query_start, query
FROM pg_catalog.pg_stat_get_activity(null)
WHERE state = 'idle in transaction'
AND xact_start < now() - interval '3 sec';
The calling role needs the necessary privileges to see the query, of course.
-
1We set 10secs for idle in transaction timeout ,and I have such a logging of
pg_stat_activityevery min but still i couldn't get it so far. I don't want to decrease the interval from 1 min, so I was looking for any alternatives method if exists.goodfella– goodfella2025-04-30 05:46:43 +00:00Commented Apr 30 at 5:46 -
@goodfella If your timeout is at 10 sec, then logging every min won't do, obviously. Try filtering
WHERE xact_start < now() - interval '3 sec', and logging every 6 sec. (Or increase that sharp timeout and the rest by a bit.) Also, you might have disclosed that in the question.Erwin Brandstetter– Erwin Brandstetter2025-04-30 09:57:35 +00:00Commented Apr 30 at 9:57 -
Logging
pg_stat_activityis the only possible solution here?goodfella– goodfella2025-05-02 01:39:09 +00:00Commented May 2 at 1:39 -
1@goodfella: That's the solution I can think of.Erwin Brandstetter– Erwin Brandstetter2025-05-02 04:12:41 +00:00Commented May 2 at 4:12
In PostgreSQL, if a session is terminated due to idle_in_transaction_timeout, and you're not logging all statements, it's tricky to pinpoint the last executed statement from that session, especially if it wasn't slow enough to be caught by log_min_duration_statement.
Enable log_min_duration_statement to a low but non-zero threshold
log_min_duration_statement = 1000 # logs statements that take >= 1 second
-
3Most of our queries executes in less than 100ms probably this one too. Changing statement logging from 1 sec to 100ms will impact performance for days as the timeout incident rarely happens.goodfella– goodfella2025-04-30 05:55:30 +00:00Commented Apr 30 at 5:55
idle_in_transaction_session_timeout. I want to find what statement is executed before it got terminated