Обсуждение: When does postgres set the connection state back to idle?

I've been having a difficult time finding an answer for this via google, so please excuse me if this is the wrong list, or a stupid question...

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.

-Stephen

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