Re: pg_upgrade bug found!

Jeff Davis wrote:
> > I have added a personal regression test to show which
> > pg_class.relfrozenxid values are not preserved, and with this patch the
> > only ones not preserved are toast tables used by system tables, which
> > are not copied from the old cluster (FirstNormalObjectId = 16384). I am
> > attaching that old/new pg_class.relfrozenxid diff as well.
> >
> > Any idea how to correct existing systems? Would VACUUM FREEZE of just
> > the toast tables work?
>
> VACUUM FREEZE will never set the relfrozenxid backward. If it was never
> preserved to begin with, I assume that the existing value could be
> arbitrarily before or after, so it might not be updated.
>
> I think that after you VACUUM FREEZE the toast table, then the real
> oldest frozen xid (as opposed to the bad value in relfrozenxid for the
> toast table) would have to be the same or newer than that of the heap.
> Right? That means you could safely copy the heap's relfrozenxid to the
> relfrozenxid of its toast table.

OK, so the only other idea I have is to write some pretty complicated
query function that does a sequential scan of each toast table and pulls
the earliest xmin/xmax from the tables and use that to set the
relfrozenxid (pretty complicated because it has to deal with the freeze
horizon and wraparound).

> > I perhaps could create a short DO block that
> > would vacuum freeze just toast tables; it would have to be run in every
> > database.
>
> Well, that won't work, because VACUUM can't be executed in a transaction
> block or function.

Good point.

The only bright part of this is that missing clog will throw an error so
we are not returning incorrect data, and hopefully people will report
problems to us when it happens.

Ideally I would like to get this patch and correction code out into the
field in case more people run into this problem. I know some will, I
just don't know how many.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +