Re: Idle transactions in PostgreSQL 9.2.4

Thanks Victor. The states were idle indeed but my application was not
getting the results from that connections back. I have just found that
there were 2 of the threads issuing queries to the same connection in
parallel.
That was the origin of the problem indeed.

Best Regards,
Svetlin Manavski

On 17 October 2013 15:26, Victor Yegorov <vyegorov(at)gmail(dot)com> wrote:

> 2013/10/15 Svetlin Manavski <svetlin(dot)manavski(at)itrinegy(dot)com>
>
>> I have some idle transactions in PostgreSQL 9.2.4 server which never end.
>> My application was working fine on version 9.1 (BSD) but the problem
>> appeared immediately as we ported it to 9.2.4 on Linux. The idle operations
>> would frequently appear as COMMITs but sometimes I observed INSERTS as well.
>> Please note that each commit below refers to *very light* inserts of
>> single rows which are supposed to last milliseconds. However some of the
>> just never end even if they are not waiting on other operations. See below:
>>
>> SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM
>> pg_stat_activity;
>>
>
> In 9.2 there's an extra column in the view that you're missing in your
> query: state.
>
> If state='active', then `query` shows _currently running_ query.
> Otherwise it shows _last query_ executed by the session.
>
> Check here:
> http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
>
>
> --
> Victor Y. Yegorov
>