Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?

Question

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'?

Answer 1

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.