Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 21C541337B5D for ; Mon, 28 Feb 2011 21:43:48 -0400 (AST) 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 68785-05; Tue, 1 Mar 2011 01:43:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.endcrypt.com (mail.endcrypt.com [74.205.105.202]) by mail.postgresql.org (Postfix) with ESMTP id 43C9D1337B39; Mon, 28 Feb 2011 21:43:40 -0400 (AST) Received: from [192.168.1.50] (unknown [69.149.154.167]) by mail.endcrypt.com (Postfix) with ESMTP id B6B2E31190; Tue, 1 Mar 2011 01:43:39 +0000 (UTC) Subject: Re: [HACKERS] Re: PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum Mime-Version: 1.0 (Apple Message framework v1082) Content-Type: text/plain; charset=us-ascii From: David Christensen In-Reply-To: <20110228212832.GH26397@sonic.net> Date: Mon, 28 Feb 2011 19:43:39 -0600 Cc: Maxim Boguk , Fujii Masao , pgsql-admin@postgresql.org, PostgreSQL-development Content-Transfer-Encoding: quoted-printable Message-Id: <1B3CCBDD-4AC6-404A-9E26-DB4CA2554228@endpoint.com> References: <4D2D6A56.9060808@enterprisedb.com> <20110228212832.GH26397@sonic.net> To: daveg , Heikki Linnakangas X-Mailer: Apple Mail (2.1082) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201102/231 X-Sequence-Number: 36870 On Feb 28, 2011, at 3:28 PM, daveg wrote: > On Wed, Jan 12, 2011 at 10:46:14AM +0200, Heikki Linnakangas wrote: >> On 12.01.2011 06:21, Fujii Masao wrote: >>> On Sat, Dec 25, 2010 at 2:09 PM, Maxim Boguk = wrote: >>>> While I trying create reproducible test case for BUG #5798 I >>>> encountered very strange effect on two of my servers (both servers >>>> have same hardware platform/OS (freebsd 7.2) and PostgreSQL 8.4.4). >>>>=20 >>>> Very simple test table created as: >>>> CREATE TABLE test (id integer); >>>> INSERT INTO test select generate_series(0,10000); >>>>=20 >>>> And I trying repeateble vacuum of that table with script: >>>> perl -e "foreach (1..100000) {system \"psql -d test -h -c 'vacuum=20= >>>> test'\";}" >>>>=20 >>>> And once per like an minute (really random intervals can be 5 = minutes >>>> without problems can be 3 vacuum in row show same error) I getting >>>> next errors: >>>> WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation = "test" page=20 >>>> 1 >>>> ... >>>> WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation = "test" >>>> page 30 for all pages of the relation. >>=20 >> Oh, interesting. This is the first time anyone can reliably = reproducible=20 >> that. I can't reproduce that on my laptop with that script, though, = so=20 >> I'm going to need your help to debug this. >>=20 >> Can you compile PostgreSQL with the attached patch, and rerun the = test?=20 >> It will dump the pages with incorrectly set flags to files in /tmp/, = and=20 >> adds a bit more detail in the WARNING. Please run the test until you=20= >> get those warnings, and tar up the the created "/tmp/pageimage*" = files,=20 >> and post them along with the warning generated. >>=20 >> We'll likely need to go back and forth a few times with various=20 >> debugging patches until we get to the heart of this.. >=20 > Anything new on this? I'm seeing at on one of my clients production = boxes. > Also, what is the significance, ie what is the risk or damage = potential if > this flag is set incorrectly? Was this cluster upgraded to 8.4.4 from 8.4.0? It sounds to me like a = known bug in 8.4.0 which was fixed by this commit: commit 7fc7a7c4d082bfbd579f49e92b046dd51f1faf5f Author: Tom Lane Date: Mon Aug 24 02:18:32 2009 +0000 Fix a violation of WAL coding rules in the recent patch to include = an "all tuples visible" flag in heap page headers. The flag update = *must* be applied before calling XLogInsert, but heap_update and the tuple moving routines in VACUUM FULL were ignoring this rule. A crash and replay could therefore leave the flag incorrectly set, causing rows to appear visible in seqscans when they should not be. This might = explain recent reports of data corruption from Jeff Ross and others. =20 In passing, do a bit of editorialization on comments in = visibilitymap.c. oy:postgresql machack$ git describe --tag = 7fc7a7c4d082bfbd579f49e92b046dd51f1faf5f REL8_4_0-190-g7fc7a7c If the flag got twiddled while running as 8.4.0, the incorrect = PD_ALL_VISIBLE flag would (obviously) not be fixed by the upgrade to = 8.4.4. (Is this a separate issue?) Regards, David -- David Christensen End Point Corporation david@endpoint.com