> Recently, in our Production server, we found a "single query" being
> in "<IDLE> in transaction" for more than 19 hours using the following
> select date_trunc('second', current_timestamp - query_start) as
> datname as database_name, current_query from pg_stat_activity where
> current_query != '<IDLE>' order by 1 desc
> but we're clueless which was the root cause of this issue and still
> As we know, query output doesn't show up the actual query/statement.
You won't be able to find the cause in PostgreSQL.
The cause is a database session that started a transaction, did some
work and never closed the transaction.
PostgreSQL can help you find out who the offending client is:
SELECT application_name, client_addr, client_hostname, client_port
WHERE procpid = 14740;
(Replace 14740 of the process ID of the "idle in transaction" backend).
Look on the client machine and find the process that holds TCP port
"client_port" open (on Linux you can use "lsof" for that).
Then you have found the culprit!
In response to
pgsql-admin by date
|Next:||From: Plugge, Joe R.||Date: 2012-05-03 16:40:59|
|Subject: DELETE and UPDATE triggers on parent table of partioned table not
|Previous:||From: Jan Lentfer||Date: 2012-05-03 11:15:37|
|Subject: Re: Very long "<IDLE> in transaction" query|