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