Re: pg_upgrade bug found!

Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > Yes, I like your version better; I did adjust the wording of the last
> > sentence to mention it is really the release, not the new pg_upgrade,
> > which fixes the problem because the fixes are in pg_dump, and hence a
> > new pg_upgrade binary will not work; you need a new install.
>
> Err, right, good point. You might even want to call that out
> specifically, so no one is confused. Also this:
>
> > -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8
> > -- servers that was upgraded by pg_upgrade and pg_migrator.
>
> 'that was' should be 'that were'.
>
> > A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4.
> > These releases will remove the need for the above script by correctly
> > updating all TOAST tables in the migrated databases.
>
> My suggestion:
>
> A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4.
> These releases will include an updated pg_dump which will remove the
> need for the above script by correctly dumping all TOAST tables in the
> migrated databases.

I am worried if I mention pg_dump that people will think pg_dump is
broken, when in fact it is only the --binary-upgrade mode of pg_dump
that is broken.

I adjusted the wording of the last paragraph slighly to be clearer, but
hopefully not confuse.

We don't actually check the pg_dump version and I am hesistant to add
such a check.

I was thinking of sending this out on Monday, but now think people might
like to have the weekend to fix this so I am thinking of sending it to
announce tonight, in 8 hours. OK?

---------------------------------------------------------------------------

A bug has been discovered in all released versions of pg_upgrade and
(formerly) pg_migrator. Anyone who has used pg_upgrade or pg_migrator
should take the following corrective actions as soon as possible.

This bug can cause queries to return the following error:

ERROR: could not access status of transaction ######
DETAIL: could not open file "pg_clog/####": No such file or directory=20

This error prevents access to very wide values stored in the database.
To prevent such failures users need to run the following psql script,
as the superuser, in all upgraded databases as soon as possible:

-- This script fixes data in pre-PG 9.0.4 and pre-8.4.8
-- servers that was upgraded by pg_upgrade and pg_migrator.
-- Run the script using psql for every database in the cluster
-- except 'template0', e.g.:
-- psql -U postgres -a -f pg_upgrade_fix.sql dbname
-- This must be run from a writable directory.
-- It will not lock any tables but will generate I/O.
--
CREATE TEMPORARY TABLE pg_upgrade_fix AS
SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';'
FROM pg_class c, pg_namespace n=20
WHERE c.relnamespace =3D n.oid AND=20
n.nspname =3D 'pg_toast' AND
c.relkind =3D 't'
ORDER by c.oid;
\copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql';
\i pg_upgrade_tmp.sql

A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4.
These releases will remove the need for the above script by correctly
dumping all TOAST tables in the migrated databases.

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

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