Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CFFBA9FCEE5 for ; Mon, 29 Oct 2007 09:53:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 14141-02-5 for ; Mon, 29 Oct 2007 09:53:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mx01.bfk.de (mx01.bfk.de [193.227.124.2]) by postgresql.org (Postfix) with ESMTP id B2B629FBCDA for ; Mon, 29 Oct 2007 09:48:11 -0300 (ADT) Received: from mx00.int.bfk.de ([10.119.110.2]) by mx01.bfk.de with esmtps (TLS-1.0:RSA_AES_256_CBC_SHA1:32) id 1ImU2L-0005Qt-4C for pgsql-hackers@postgresql.org; Mon, 29 Oct 2007 13:48:09 +0100 Received: from fweimer by bfk.de with local id 1ImU2K-0003QV-UM for pgsql-hackers@postgresql.org; Mon, 29 Oct 2007 13:48:08 +0100 To: pgsql-hackers@postgresql.org Subject: Re: Hmmm ... isn't count_nondeletable_pages all wet? References: <28916.1189900143@sss.pgh.pa.us> <29644.1189904237@sss.pgh.pa.us> From: Florian Weimer Date: Mon, 29 Oct 2007 13:48:08 +0100 In-Reply-To: <29644.1189904237@sss.pgh.pa.us> (Tom Lane's message of "Sat, 15 Sep 2007 20:57:17 -0400") Message-ID: <82wst6ulx3.fsf@mid.bfk.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/1443 X-Sequence-Number: 109811 * Tom Lane: > I am fairly sure that this bug explains problems previously reported > by Merlin Moncure: > http://archives.postgresql.org/pgsql-general/2006-10/msg01312.php > and Florian Weimer: > http://archives.postgresql.org/pgsql-general/2006-11/msg00305.php > In both those cases, off-list investigation showed that the symptoms > were caused by multiple index entries pointing to the same heap tuples, > where one index entry matched the actual contents of the row and > the other did not. In both cases this occurred for a fairly small > number of rows that were clumped together into small ranges of blocks. > It looks to me like this is perfectly explained by the theory that > that range of blocks had been truncated away by a VACUUM at some point > in the table's history, and that the non-matching index entries stemmed > from an insert or update that occurred and then aborted after VACUUM had > examined the blocks the first time but before it could return to check > whether the blocks were still empty. We did have auto-vacuum running, and while the table in question was supposedly INSERT-only, some rollback might have occurred before the corruption hit us, resulting in the dead tuples. So your explanation makes sense to me (but I'm not really familiar with PostgreSQL internals). Regarding Scott's commment of other reports, I don't think we've experienced the issue again; we've switched servers since then, and the usage patterns have changed over time. --=20 Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstra=DFe 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99