pg_upgrade bug found!

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: RhodiumToad on IRC <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_upgrade bug found!
Date: 2011-04-06 03:04:33
Message-ID: 201104060304.p3634Xd03852@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Yes, this is not good! :-(

I will still add a special flag to postgres to turn off autovacuum, but
as we suspected, this is only a marginal improvement and not the cause
of the 9.0.X failures. The good news is that only two people have seen
this problem and it only happens when the hint bits have not been set on
the toast rows and the oldest heap rows have been updated.

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

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2011-04-06 03:08:56 Re: Transaction log
Previous Message Josh Kupershmidt 2011-04-06 01:55:31 Re: [HACKERS] Uppercase SGML entity declarations