Re: pg_upgrade bug found!

Jeff Davis wrote:
> On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote:
> > So I think we have four possible approaches to correct databases:
> >
> > 1) SELECT * to set the hint bits
> > 2) VACUUM to set the hint bits
> > 3) VACUUM FREEZE to remove the old xids
> > 4) some complicated function
> >
> > I don't like #4, and I think I can script #2 and #3 in psql by using COPY
> > to create a VACUUM script and then run it with \i. #1 is easy in a DO
> > block with PL/pgSQL.
>
> The only one that sounds very reasonable to me is #3. If there are any
> xids older than the relfrozenxid, we need to get rid of them. If there
> is some reason that doesn't work, I suppose we can consider the
> alternatives. But I don't like the hint-bit-setting approach much.
>
> What if the xmax is really a transaction that got an exclusive lock on
> the tuple, rather than actually deleting it? Are you sure that a SELECT
> (or even a normal VACUUM) would get rid of that xid, or might something
> still try to look it up in the clog later?
>
> Not only that, but hint-bit-setting is not WAL-logged, so you'd really
> have to do a checkpoint afterward.

Glad you said that! Here is a script which does what we want:

-- This script fixes data in pre-PG 9.0.4 and pre-8.4.8
-- servers that were upgraded by pg_upgrade and pg_migrator.
-- Run the script using psql for every database in the cluster,
-- except 'template0', e.g.
-- psql -f pg_upgrade_fix dbname
-- It will not lock any tables but will generate I/O.
--
SET vacuum_freeze_min_age = 0;
SET vacuum_freeze_table_age = 0;
CREATE TEMPORARY TABLE pg_upgrade_fix AS
SELECT 'VACUUM FREEZE pg_toast.' ||
quote_ident(relname) || ';'
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid AND
n.nspname = 'pg_toast' AND
c.relkind = 't';
\copy pg_upgrade_fix TO 'pg_upgrade_fix.sql';
\i pg_upgrade_fix.sql
DROP TABLE pg_upgrade_fix;

Looks pretty simple to copy/paste and use.

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

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