From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | <gnanam(at)zoniac(dot)com>, <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Very long "<IDLE> in transaction" query |
Date: | 2012-05-03 14:06:05 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C207D4FFA7@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Plugge, Joe R. | 2012-05-03 16:40:59 | DELETE and UPDATE triggers on parent table of partioned table not firing. |
Previous Message | Jan Lentfer | 2012-05-03 11:15:37 | Re: Very long "<IDLE> in transaction" query |