On Mon, Sep 17, 2012 at 01:03:37PM +0800, Rural Hunter wrote:
> >As you can see, we look at the existing TOAST usage and force the new
> >cluster to match. As I remember we replay the DROP COLUMN in binary
> >upgrade mode so the new cluster always matches the old cluster's TOAST
> >usage. I certainly have never seen this bug reported before.
> >
> >I think the big question is why did this case fail? I can say that the
> >query that pulls details from each cluster skips information_schema or
> >oid < FirstNormalObjectId. I wonder if there is a mismatch between what
> >pg_dump filters out and pg_upgrade. Can you tell us the schema of the
> >'sql_features' table?
> # 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.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
