Gnanakumar wrote: > Recently, in our Production server, we found a "single query" being held up > in "<IDLE> in transaction" for more than 19 hours using the following query: > select date_trunc('second', current_timestamp - query_start) as runtime, > 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 hunting. > 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 FROM pg_stat_activity 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! Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin