Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed

On Mon, Sep 17, 2012 at 05:07:23PM -0400, Bruce Momjian wrote:
> > # select * from pg_tables where tablename='sql_features';
> >     schemaname     |  tablename   | tableowner | tablespace |
> > hasindexes | hasrules | hastriggers
> > --------------------+--------------+------------+------------+------------+----------+-------------
> > information_schema | sql_features | postgres   |            | f
> > | f        | f
> > (1 row)
> 
> OK, good to know.  This is the query pg_upgrade 9.2 uses to pull
> information from 9.1 and 9.2:
> 
> 	SELECT c.oid, n.nspname, c.relname,  c.relfilenode, c.reltablespace, t.spclocation 
> 	FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid   
> 		LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid 
> 	WHERE relkind IN ('r','t', 'i', 'S') AND
> 		((n.nspname !~ '^pg_temp_' AND
> 		  n.nspname !~ '^pg_toast_temp_' AND 
> 		  n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade') AND
> 		  c.oid >= 16384
> 		 )   
> 		 OR 
> 		 (n.nspname = 'pg_catalog' AND
> 		  relname IN
> 		  ('pg_largeobject', 'pg_largeobject_loid_pn_index', 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index') 
> 		 )
> 		)
> 	ORDER BY 1;
> 
> Based on the fact that sql_features exists in the information_schema
> schema, I don't think 'sql_features' table is actually being processed
> by pg_upgrade, but I think its TOAST table, because it has a high oid,
> is being processed because it is in the pg_toast schema.  This is
> causing the mismatch between the old and new clusters.
> 
> I am thinking this query needs to be split apart into a UNION where the
> second part handles TOAST tables and looks at the schema of the _owner_
> of the TOAST table.  Needs to be backpatched too.

OK, I am at a conference now so will not be able to write-up a patch
until perhaps next week.  You can drop the information schema in the old
database and pg_upgrade should run fine.  I will test your failure once
I create a patch.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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