Re: pg_upgrade bug found! - Mailing list pgsql-hackers

Noah Misch wrote:
> On Thu, Apr 07, 2011 at 12:16:55PM -0400, Bruce Momjian wrote:
> > Bruce Momjian wrote:
> > > OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
> > > the two reported pg_upgrade problems he saw via IRC.  It seems toast
> > > tables have xids and pg_dump is not preserving the toast relfrozenxids
> > > as it should.  Heap tables have preserved relfrozenxids, but if you
> > > update a heap row but don't change the toast value, and the old heap row
> > > is later removed, the toast table can have an older relfrozenxids than
> > > the heap table.
> > > 
> > > The fix for this is to have pg_dump preserve toast relfrozenxids, which
> > > can be easily added and backpatched.  We might want to push a 9.0.4 for
> > > this.  Second, we need to find a way for people to detect and fix
> > > existing systems that have this problem, perhaps looming when the
> > > pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we
> > > need to figure out how to get this information to users.  Perhaps the
> > > communication comes through the 9.0.4 release announcement.
> > 
> > I am not sure how to interpret the lack of replies to this email. 
> > Either it is confidence, shock, or we told you so.  ;-)
> 
> Your explanation and patch make sense.  Seems all too clear in retrospect.

Yeah, like "duh" for me.

> > Any idea how to correct existing systems?  Would VACUUM FREEZE of just
> > the toast tables work?  I perhaps could create a short DO block that
> > would vacuum freeze just toast tables;  it would have to be run in every
> > database.
> 
> I see three cases:
> 
> 1) The pg_class.relfrozenxid that the TOAST table should have received
> ("true relfrozenxid") is still covered by available clog files.  Fixable
> with some combination of pg_class.relfrozenxid twiddling and "SET
> vacuum_freeze_table_age = 0; VACUUM toasttbl".

Right, VACUUM FREEZE.  I now see I don't need to set
vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has:
   if (n->options & VACOPT_FREEZE)n->freeze_min_age = n->freeze_table_age = 0;

> 2) The true relfrozenxid is no longer covered by available clog files.
> The fix for case 1 will get "file "foo" doesn't exist, reading as
> zeroes" log messages, and we will treat all transactions as uncommitted.

Uh, are you sure?  I think it would return an error message about a
missing clog file for the query;  here is a report of a case not related
to pg_upgrade:
http://archives.postgresql.org/pgsql-admin/2010-09/msg00109.php

> Not generally fixable after that has happened.  We could probably
> provide a recipe for checking whether it could have happened given
> access to a backup from just before the upgrade.

The IRC folks pulled the clog files off of backups.

> 3) Enough transaction xids have elapsed such that the true relfrozenxid
> is again covered by clog files, but those records are unrelated to the
> original transactions.  Actually, I don't think this can happen, even
> with the maximum autovacuum_freeze_max_age.

Yes, I don't think that can happen either.

One concern I have is that existing heap tables are protecting clog
files, but once those are frozen, the system might remove clog files not
realizing it has to freeze the heap tables too.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +