Re: Idle transactions in PostgreSQL 9.2.4

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