Re: Re: [ADMIN] PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum

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<maxim.boguk@gmail.com>  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).
>>>>
>>>> Very simple test table created as:
>>>> CREATE TABLE test (id integer);
>>>> INSERT INTO test select generate_series(0,10000);
>>>>
>>>> And I trying repeateble vacuum of that table with script:
>>>> perl -e "foreach (1..100000) {system \"psql -d test -h -c 'vacuum
>>>> test'\";}"
>>>>
>>>> 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
>>>> 1
>>>> ...
>>>> WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation "test"
>>>> page 30 for all pages of the relation.
>>
>> Oh, interesting. This is the first time anyone can reliably reproducible
>> that. I can't reproduce that on my laptop with that script, though, so
>> I'm going to need your help to debug this.
>>
>> Can you compile PostgreSQL with the attached patch, and rerun the test?
>> It will dump the pages with incorrectly set flags to files in /tmp/, and
>> adds a bit more detail in the WARNING.  Please run the test until you
>> get those warnings, and tar up the the created "/tmp/pageimage*" files,
>> and post them along with the warning generated.
>>
>> We'll likely need to go back and forth a few times with various
>> debugging patches until we get to the heart of this..
>
> 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 <tgl@sss.pgh.pa.us>
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.

    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
theupgrade to 8.4.4.  (Is this a separate issue?) 

Regards,

David
--
David Christensen
End Point Corporation
david@endpoint.com