From: Bruce Momjian <bruce@momjian.us>
To: Rural Hunter <ruralhunter@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
Date: Tue, 18 Sep 2012 19:22:39 -0400
Message-ID: <20120918232239.GA2014@momjian.us> (raw)
In-Reply-To: <20120917210723.GA30394@momjian.us>
References: <50535302.8080000@gmail.com>
<20120914183915.GA5933@momjian.us>
<5053F896.60005@gmail.com>
<20120915180603.GA20907@momjian.us>
<505557CD.8090304@gmail.com>
<20120916171746.GA24907@momjian.us>
<28666.1347833056@sss.pgh.pa.us>
<20120917043236.GA20123@momjian.us>
<5056AF29.3040903@gmail.com>
<20120917210723.GA30394@momjian.us>
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. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bruce@momjian.us, ruralhunter@gmail.com, pgsql-hackers@postgresql.org, tgl@sss.pgh.pa.us
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
In-Reply-To: <20120918232239.GA2014@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox