Re: pg_upgrade bug found!

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? I perhaps could create a short DO block that
would vacuum freeze just toast tables; it would have to be run in every
database.

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

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