Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 371DDCE6EC for ; Fri, 15 Jun 2012 16:32:35 -0300 (ADT) Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SfcFz-0003Tu-MN for pgsql-performance@postgresql.org; Fri, 15 Jun 2012 19:32:34 +0000 Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 15 Jun 2012 14:32:17 -0500 Message-Id: <4FDB476A02000025000484F4@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.2 Date: Fri, 15 Jun 2012 14:32:10 -0500 From: "Kevin Grittner" To: "Benedict Holland" Cc: Subject: Re: Update blocking a select count(*)? References: <4FDB3C0202000025000484E4@gw.wicourts.gov> <4FDB40B702000025000484EE@gw.wicourts.gov> In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201206/88 X-Sequence-Number: 47114 Benedict Holland wrote: > Yes. I needed to do a full vacuum. Again, the database is very > large. I batch inserted quite a lot of data and then modified that > data. The vacuum isn't blocking anything. It was blocking other > tables (as expected) but continues to run and clean. My tables in > general are around 10GB, each update seems to nearly double the > size of it so I required a full vacuum. I was trying to suggest techniques which would prevent that bloat and make the VACUUM FULL unnecessary. But, now that I've had a chance to format the attachment into a readable format, I agree that it isn't part of the problem. The iceberg in this case is the ALTER TABLE, which is competing with two other queries. > The blocked statements are the select count(*) and the alter > table. OK. > Both are blocked on the update table command. Not directly. The lock held by the UPDATE would *not* block the SELECT; but it *does* block the ALTER TABLE command, which can't share the table while it changes the structure of the table. The SELECT is blocked behind the ALTER TABLE. > The alter table command SHOULD be blocked and that is fine. I'm glad we're on the same page there. > The select count(*) should never be blocked as that is the whole > point of running an MVCC operation at least to my understanding. I > can even accept the use case that the select should block with an > Alter Table operation if data is retrieved from the table, but a > select count(*) only returns the number of rows and should be > table space independent. In PostgreSQL SELECT count(*) must scan the table to see which rows are visible to the executing database transaction. Without that, it can't give a completely accurate count from a transactional perspective. If you can settle for a non-transactional approximation, select the reltuples value from the pg_class row for the table. > I also don't understand why a select count(*) requires an > AccessShareLock. I don't understand why a select should lock > anything at all. So that the table isn't dropped or truncated while the count is scanning the table. -Kevin