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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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