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. +