Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 65E8F13C6999
	for <pgsql-admin@postgresql.org>; Fri,  4 May 2012 10:38:56 -0300 (ADT)
Received: from mh6.mail.rice.edu ([128.42.201.4])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ktm@aart.rice.edu>) id 1SQIil-0001pU-NA
	for pgsql-admin@postgresql.org; Fri, 04 May 2012 13:38:56 +0000
Received: from mh6.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh6.mail.rice.edu (Postfix) with ESMTP id 5411129117C;
	Fri,  4 May 2012 08:38:43 -0500 (CDT)
Received: from mh6.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh6.mail.rice.edu (Postfix) with ESMTP id 43684290CBA;
	Fri,  4 May 2012 08:38:43 -0500 (CDT)
X-Virus-Scanned: by amavis-2.6.4 at mh6.mail.rice.edu, auth channel
Received: from mh6.mail.rice.edu ([127.0.0.1])
	by mh6.mail.rice.edu (mh6.mail.rice.edu [127.0.0.1]) (amavis,
	port 10026)
	with ESMTP id 1aDN2TbWD5jz; Fri,  4 May 2012 08:38:43 -0500 (CDT)
X-SMTP-Auth: no
X-SMTP-Auth: no
X-SMTP-Auth: no
Received: from aart.rice.edu (aart.rice.edu [168.7.56.48])
	by mh6.mail.rice.edu (Postfix) with ESMTP id 0D5F129117D;
	Fri,  4 May 2012 08:38:43 -0500 (CDT)
Received: by aart.rice.edu (Postfix, from userid 18612)
	id 059B1100589; Fri,  4 May 2012 08:38:43 -0500 (CDT)
Date: Fri, 4 May 2012 08:38:42 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: Gnanakumar <gnanam@zoniac.com>
Cc: 'Albe Laurenz' <laurenz.albe@wien.gv.at>, pgsql-admin@postgresql.org
Subject: Re: Very long "<IDLE> in transaction" query
Message-ID: <20120504133842.GH32303@aart.rice.edu>
References: <008101cd2916$1c023b20$5406b160$@com>
	<D960CB61B694CF459DCFB4B0128514C207D4FFA7@exadv11.host.magwien.gv.at>
	<008001cd29db$ab24ba60$016e2f20$@com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <008001cd29db$ab24ba60$016e2f20$@com>
User-Agent: Mutt/1.5.20 (2009-12-10)
X-Pg-Spam-Score: -4.2 (----)
X-Archive-Number: 201205/26
X-Sequence-Number: 39515

On Fri, May 04, 2012 at 03:22:47PM +0530, Gnanakumar wrote:
> > 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).
> 
> Ours is a web-based application and all calls are made "only" from where the
> web server is running.  No external calls are allowed in my case.   Hence,
> the "only" client machine in my case is the web server.
> 

You may also want to consider setting a statement_timeout to prevent this
until you can find the problem with the application.

Regards,
Ken