Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EF65F633554 for ; Thu, 8 Apr 2010 09:57:13 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 05039-05 for ; Thu, 8 Apr 2010 12:56:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f225.google.com (mail-fx0-f225.google.com [209.85.220.225]) by mail.postgresql.org (Postfix) with ESMTP id A6F9D632224 for ; Thu, 8 Apr 2010 09:57:02 -0300 (ADT) Received: by fxm25 with SMTP id 25so156862fxm.3 for ; Thu, 08 Apr 2010 05:57:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=m/bBdXT0WZT0duxOIEgP4LGsjVaXuA0OtEHcWkrrsOQ=; b=gGPiPm5yFjgRQb3Xuo1WV1e+szjbiARHYAgFfIvHj4gxxnJByr/7+7KCHRIrI5odvv ShZN/IUaRMrTPKA9GXVUydJ8rT6esEI1tX2jGgFp1B+h+XMEWGnz60tRPehE6SVsvwOj FoeE59e2mUYnhMqdxyA5V7bvsPPGfs8BjlEsY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=lvvuwZUzQK+RiYo3zu9RMJNRiw2FZovN1BSan/z7wQ0kAsGOl+OtoOByIqKudVVRAG t0mf/VaaOGyE3mjObPVajnOXJLmYfHaUWNRx72Ps2nQBKJMa8va4iLi4M9lh3ST1FAi/ WgLcdTZbiOttupGnLQsimeJgIkP8Gd736uXqQ= MIME-Version: 1.0 Received: by 10.103.228.19 with HTTP; Thu, 8 Apr 2010 05:57:00 -0700 (PDT) In-Reply-To: References: Date: Thu, 8 Apr 2010 08:57:00 -0400 Received: by 10.103.84.21 with SMTP id m21mr57044mul.106.1270731420570; Thu, 08 Apr 2010 05:57:00 -0700 (PDT) Message-ID: Subject: Re: PD_ALL_VISIBLE flag warning From: Jonathan Foy To: Greg Stark Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=0016e65b5b0aed95180483b93654 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.014 tagged_above=-10 required=5 tests=AWL=0.584, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/82 X-Sequence-Number: 34176 --0016e65b5b0aed95180483b93654 Content-Type: text/plain; charset=ISO-8859-1 Thanks for the reply... We have an unhealthy habit of not updating very often around here, one that I'm trying to fix. We run both 8.1 and 8.4, and we're testing the update to 8.1.20 this weekend....from 8.1.3. Across the board 8.4 update will take much more testing due to the typing issue. Other 8.4 updates will take place soon. Anyway, my guilt about outdated versions aside... We never actually ran 8.4.0. We went straight from 8.3.3 to 8.4.1. I did a full dump/restore from the 8.3.3 database into the 8.4.1 database (using the 8.4 pg_dump), partitioning as I went. The table in question was not partitioned in 8.3, the 8.4 dump/restore seemed as good a place to do the partitioning as any. I don't specifically remember doing it, but I almost always do a manual vacuum/analyze after a restore. Autovacuum is/was also turned on. According to the stats, everything seems to have been at least vacuumed on 2/15 (the date of the update I believe), so it is doubtful to me that any of the tables, especially the currently active partition, would have gone un-vacuumed as of 4/1. The logs for that day have already rotated into less-easily available storage, but I'm considering retrieving them to look for any aborts/rollbacks. Also, for what it's worth, we never delete rows from this table, we only insert and update. I have not seen the warning since. Sound like trouble? On Thu, Apr 8, 2010 at 12:59 AM, Greg Stark wrote: > On Thu, Apr 1, 2010 at 2:10 PM, Jonathan Foy wrote: > > Hello > > > > I came in this morning and noticed this warning sitting in my inbox quite > a > > few times... > > > > WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation > "table_2010q1" > > page 471118 > > WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation > "table_2010q1" > > page 471119 > > WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation > "table_2010q1" > > page 471120 > > .... > > > > and I'm wondering how worked up I should be getting. > > This is a warning about a fairly serious data corruption problem. In > the worst case prior queries returned incorrect results -- which if > those results were used to calculate subsequent updates could have > caused your data to be inconsistent. > > Specifically, having that flag set would cause rows to be returned > which should not have been returned, either because they had been > deleted, the transaction inserting them aborted, or they had been > updated (causing duplicate copies of the same row to be returned). > > There was a bug which could cause this in 8.4.0 but it was fixed in > 8.4.1. You say you're running 8.4.1 (you should be running 8.4.3 > though none of the subsequent bug-fixes are as serious if the SSL > security vulnerability isn't relevant) so this is quite worrisome. > Nobody else has reported anything similar though. > > Is it possible these pages have never been vacuumed since you updated > from 8.4.0 to 8.4.1? I'm not sure how likely that is. In 8.4 Postgres > tries to avoid vacuuming pages that don't need it so it's possible > this situation could live on for longer than in the past. > > I think a regular "VACUUM" of every table should guarantee that any > remnants of this bug from 8.4.0 are cleaned up. If you do this and > possibly get some more warnings from it but never see it again > afterwards then I would assume they're leftovers from that old bug. > > -- > greg > --0016e65b5b0aed95180483b93654 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thanks for the reply...

We have an unhealthy habit of not updating v= ery often around here, one that I'm trying to fix. We run both 8.1 and = 8.4, and we're testing the update to 8.1.20 this weekend....from 8.1.3.= =A0 Across the board 8.4 update will take much more testing due to the typi= ng issue. Other 8.4 updates will take place soon.

Anyway, my guilt about outdated versions aside...

We never actua= lly ran 8.4.0.=A0 We went straight from 8.3.3 to 8.4.1.=A0 I did a full dum= p/restore from the 8.3.3 database into the 8.4.1 database (using the 8.4 pg= _dump), partitioning as I went. The table in question was not partitioned i= n 8.3, the 8.4 dump/restore seemed as good a place to do the partitioning a= s any.=A0 I don't specifically remember doing it, but I almost always d= o a manual vacuum/analyze after a restore.=A0 Autovacuum is/was also turned= on.=A0 According to the stats, everything seems to have been at least vacu= umed on 2/15 (the date of the update I believe), so it is doubtful to me th= at any of the tables, especially the currently active partition, would have= gone un-vacuumed as of 4/1.

The logs for that day have already rotated into less-easily available s= torage, but I'm considering retrieving them to look for any aborts/roll= backs.

Also, for what it's worth, we never delete rows from this= table, we only insert and update.

I have not seen the warning since.

Sound like trouble?

On Thu, Apr 8, 2010 at 12:59 AM, Greg Stark <gsstark@mit.edu&= gt; wrote:
On Thu, Apr 1, 2010 at 2:10 PM, Jonathan Foy <thefoy@gmail.com> wrote:
> Hello
>
> I came in this morning and noticed this warning sitting in my inbox qu= ite a
> few times...
>
> WARNING:=A0 PD_ALL_VISIBLE flag was incorrectly set in relation "= table_2010q1"
> page 471118
> WARNING:=A0 PD_ALL_VISIBLE flag was incorrectly set in relation "= table_2010q1"
> page 471119
> WARNING:=A0 PD_ALL_VISIBLE flag was incorrectly set in relation "= table_2010q1"
> page 471120
> ....
>
> and I'm wondering how worked up I should be getting.

This is a warning about a fairly serious data corruption problem. In<= br> the worst case prior queries returned incorrect results -- which if
those results were used to calculate subsequent updates could have
caused your data to be inconsistent.

Specifically, having that flag set would cause rows to be returned
which should not have been returned, either because they had been
deleted, the transaction inserting them aborted, or they had been
updated (causing duplicate copies of the same row to be returned).

There was a bug which could cause this in 8.4.0 but it was fixed in
8.4.1. =A0You say you're running 8.4.1 (you should be running 8.4.3
though none of the subsequent bug-fixes are as serious if the SSL
security vulnerability isn't relevant) so this is quite worrisome.
Nobody else has reported anything similar though.

Is it possible these pages have never been vacuumed since you updated
from 8.4.0 to 8.4.1? I'm not sure how likely that is. In 8.4 Postgres tries to avoid vacuuming pages that don't need it so it's possible<= br> this situation could live on for longer than in the past.

I think a regular "VACUUM" of every table should guarantee that a= ny
remnants of this bug from 8.4.0 are cleaned up. If you do this and
possibly get some more warnings from it but never see it again
afterwards then I would assume they're leftovers from that old bug.

--
greg

--0016e65b5b0aed95180483b93654--