Re: pg_upgrade bug found!

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.

OK, I have applied the attached three patches to 8.4, 9.0, and 9.1.
They are all slightly different because of code drift, and I created a
unified diff which I find is clearer for single-line changes.

I was very careful about the patching of queries because many of these
queries are only activated when dumping an older database, and are
therefore hard to test for SQL query errors. I included all the version
patches in case someone sees something I missed.

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

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