Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C6A4E1337BFB for ; Wed, 11 May 2011 19:33:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 93262-02 for ; Wed, 11 May 2011 22:33:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 109D11337BED for ; Wed, 11 May 2011 19:33:40 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Wed, 11 May 2011 17:33:40 -0500 Message-Id: <4DCAC86D020000250003D5AE@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Wed, 11 May 2011 17:33:33 -0500 From: "Kevin Grittner" To: "Stephen Sugden" , Subject: Re: When does postgres set the connection state back to idle? References: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201105/86 X-Sequence-Number: 37523 Stephen Sugden 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