From: Thomas Markus <t.markus@proventis.net>
To: pgsql-admin@postgresql.org
Subject: Re: Very long "<IDLE> in transaction" query
Date: Tue, 08 May 2012 08:24:41 +0200
Message-ID: <4FA8BC29.7000305@proventis.net> (raw)
In-Reply-To: <008101cd2916$1c023b20$5406b160$@com>
References: <008101cd2916$1c023b20$5406b160$@com>
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: t.markus@proventis.net
Subject: Re: Very long "<IDLE> in transaction" query
In-Reply-To: <4FA8BC29.7000305@proventis.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox