0

In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually using pg_terminate_backend() or set idle_in_transaction_session_timeout to 5min in case of high TPS DB.

I think there is no meaning to ideal any query more than 24hr. But there I found it is more than 7 days.

Query below:

SELECT current_setting('transaction_isolation');

Query Output: read committed

How to debug in those type of issue to identify actual cause of 'idle in transaction'?

4
  • 1
    It is the fault of the application, which does not close its transactions. Simple as that. Use idle_in_transaction_session_timeout. 5 minutes might be a good starting point. Commented Apr 17 at 10:04
  • The query is not running. The reported query text is for the last query which had run, before the connection went idle. But now that it is idle, no query is running.
    – jjanes
    Commented Apr 17 at 16:02
  • Yes jjanes it is not running but holding process as a idle in transaction , so there is no meaning of its existence. Commented Apr 17 at 17:20
  • It is not "it" (the query or the database) that is holding the transaction open. It is the fault of the client that "forgot" to end the transaction. Commented Apr 18 at 5:41

1 Answer 1

0

There are two things here that need to happen:

  1. You need to set your idle_in_transaction_session_timeout to a reasonable value. That greatly depends on the application that uses the database. As Laurenz commented, 5 minutes seems reasonable start.
  2. You have to contact the developers of that application, mention the query in question. They will eventually run out of resources on their side if they do not close connections.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.