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.--
Runthe 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;CREATETEMPORARY 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.sqlDROP 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. +