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

[Thomas Markus <t.markus@xxxxxxxxxxxxx>]

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>



--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin