Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id EE11998FC83
	for <pgsql-admin@postgresql.org>; Tue,  8 May 2012 03:24:58 -0300 (ADT)
Received: from intern.proventis.net ([85.182.161.50])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <t.markus@proventis.net>) id 1SRdqy-0008Mk-LU
	for pgsql-admin@postgresql.org; Tue, 08 May 2012 06:24:58 +0000
Received: from mail.proventis.net (unknown [192.168.22.239])
	by intern.proventis.net (Postfix) with ESMTP id 6441114EB9
	for <pgsql-admin@postgresql.org>; Tue,  8 May 2012 08:24:42 +0200 (CEST)
Received: from localhost (localhost [127.0.0.1])
	by mail.proventis.net (Postfix) with ESMTP id 5CA6FCDFAE
	for <pgsql-admin@postgresql.org>; Tue,  8 May 2012 08:24:42 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at mail.proventis.net
Received: from mail.proventis.net ([127.0.0.1])
	by localhost (mail.proventis.net [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id BhleSUd-ZZ1q for <pgsql-admin@postgresql.org>;
	Tue,  8 May 2012 08:24:41 +0200 (CEST)
Received: from [192.168.22.130] (tmarkus.proventis.net [192.168.22.130])
	by mail.proventis.net (Postfix) with ESMTP id DD14A1C2267
	for <pgsql-admin@postgresql.org>; Tue,  8 May 2012 08:24:41 +0200 (CEST)
Message-ID: <4FA8BC29.7000305@proventis.net>
Date: Tue, 08 May 2012 08:24:41 +0200
From: Thomas Markus <t.markus@proventis.net>
Organization: proventis GmbH
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:12.0) Gecko/20120427 Thunderbird/12.0.1
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Very long "<IDLE> in transaction" query
References: <008101cd2916$1c023b20$5406b160$@com>
In-Reply-To: <008101cd2916$1c023b20$5406b160$@com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201205/33
X-Sequence-Number: 39522

Hi Gnanam,

I assume your not ending your transactions with commit or rollback. When 
using a connection pooler your connection is reused for next request. So 
if one request does something that initiates an implizit BEGIN (like an 
UPDATE/INSERT) without a COMMIT or ROLLBACK this connection remains in 
IDLE IN TRANSACTION mode. Please ensure that you invoke a COMMIT after 
each successful request or ROLLBACK after failed or control these inside 
your application. I'm not familiar with pgpool but check if you can 
configure your pooler for this behaviour

Thomas



Am 03.05.2012 12:18, schrieb Gnanakumar:
> Hi,
>
> 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.
<snip>