MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Thu, 03 May 2012 13:15:37 +0200
From: Jan Lentfer <Jan.Lentfer@web.de>
To: <pgsql-admin@postgresql.org>
Subject: Re: Very long "<IDLE> in transaction" query
In-Reply-To: <008101cd2916$1c023b20$5406b160$@com>
References: <008101cd2916$1c023b20$5406b160$@com>
Message-ID: <a96af08dc91e765a4cd37191a092ca64@neslonek.homeunix.org>
User-Agent: RoundCube Webmail/0.5.3

On Thu, 3 May 2012 15:48:35 +0530, Gnanakumar wrote:
> 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:

[...]

Usually these are application problems, not database ones. E.g. a piece 
of code not cathing an error correctly or breaking out of a loop or 
something like that and leaving the transaction open (not issuing COMMIT 
or ROLLBACK). Seens this quite often in production and it was always 
tracebla back to an application error.

To find out what the session has last done before it started idling you 
would need to turn on statement logging. Then at least you might be able 
to trace down in which part of you application this was caused.


Jan

-- 
professional: http://www.oscar-consult.de
private: http://neslonek.homeunix.org/drupal/