From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Jeff Davis <pgsql(at)j-davis(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, RhodiumToad on IRC <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: pg_upgrade bug found! |
Date: | 2011-04-08 01:33:19 |
Message-ID: | 201104080133.p381XJQ10907@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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. +
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-04-08 01:58:50 | Re: WIP: Allow SQL-language functions to reference parameters by parameter name |
Previous Message | Jeff Davis | 2011-04-08 01:10:41 | Re: pg_upgrade bug found! |