Re: pg_upgrade bug found!

Bruce Momjian wrote:
> OK, now that I have committed the fixes to git, I think it is time to
> consider how we are going to handle this fix for people who have already
> used pg_upgrade, or are using it in currently released versions.
>
> I am thinking an announce list email with this query would be enough,
> and state that we are planning a minor release with this fix in the
> next few weeks. I can provide details on the cause and behavior of the
> bug.

OK, here is a draft email announcement:

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

A bug has been discovered in all released Postgres versions that
performed major version upgrades using pg_upgrade and (formerly)
pg_migrator. The 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

This error prevents access to very wide values stored in the database.
To prevent such failures, users are encourage to run the following
psql script in all upgraded databases as soon as possible; a fix will be
included in upcoming Postgres releases 8.4.8 and 9.0.4:

-- 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
WHERE c.relnamespace = n.oid AND
n.nspname = 'pg_toast' AND
c.relkind = 't'
ORDER by c.oid;
\copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql';
\i pg_upgrade_tmp.sql

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

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