Skip site navigation (1) Skip section navigation (2)

Re: Very long "<IDLE> in transaction" query

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 (view raw or flat)
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

In response to

Responses

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 firing.
Previous:From: Jan LentferDate: 2012-05-03 11:15:37
Subject: Re: Very long "<IDLE> in transaction" query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group