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

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