Re: pg_upgrade bug found!

On Thu, 2011-04-07 at 12:16 -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. ;-)
>
> Anyway, the attached patch fixes the problem. The fix is for pg_dump's
> binary upgrade mode. This would need to be backpatched back to 8.4
> because pg_migrator needs this too.
>
> 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.

> 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.

Regards,
Jeff Davis