Re: When does postgres set the connection state back to idle?

Stephen Sugden <stephen@aers.ca> wrote:

> We're trying to diagnose a system issue where we ended up with
> 100's of non-idle queries in pg_stat_activity, but the actual
> system load didn't correspond to what we should have seen with
> that query load. Is it possible that a client could cause this by
> not acknowledging that it has received results, so that postgres
> will think the query is active even when it's done the hard work
> of getting the queried data off disk? These were all SELECT
> queries if that makes any difference.

You need to look at pg_stat_activity and pg_locks.  Look for
processes that have waiting true, and locks that have granted false.
There are queries on the Wiki to help sort it out.

http://wiki.postgresql.org/wiki/Lock_Monitoring

http://wiki.postgresql.org/wiki/Lock_dependency_information

By the way, it sounds like you should seriously consider using a
connection pooler.

-Kevin