Re: pg_upgrade issues - Mailing list pgsql-bugs

depstein@alliedtesting.com wrote:
> I have encountered another problem with pg_upgrade, while migrating
> from 8.4 to 9.0 (beta2, as well as beta3) on Windows XP Pro.

Wow, your testing of pg_upgrade has been excellent!  I hope you can
continue and test other areas of our system too.  I am actually curious
how you are so good at this.

> I have a table with a regclass column, which references other tables
> in the same database:
>
> CREATE TABLE common_inst.reg_asset
> (
>   asset_id integer NOT NULL,
>   table_name regclass,
>   CONSTRAINT asset_registered_pkey PRIMARY KEY (asset_id)
> )
>
> Sometimes after I migrate the database, the values in the table_name
> column show integer numbers (e.g. '284551' for a table named
> 'common_inst.asset_spot_equity_index') instead of table references.
> These numbers are the OIDs of the tables in the old database, but in
> the new database these OIDs have no referent.

Ah, I never thought of the migrations issues of user tables using the
reg* data types:

 pg_catalog | regclass                    | registered class
 pg_catalog | regconfig                   | registered text search configuration
 pg_catalog | regdictionary               | registered text search dictionary
 pg_catalog | regoper                     | registered operator
 pg_catalog | regoperator                 | registered operator (with args)
 pg_catalog | regproc                     | registered procedure
 pg_catalog | regprocedure                | registered procedure (with args)
 pg_catalog | regtype                     | registered type

In fact, I never even considered that user tables would be using these
data types.  The basic problem is that we don't preserve most of these
oids when recreating them in the new cluster --- we only preserve
pg_type.oid, pg_class.relfilenode, and pg_enum.oid.

> FWIW, when looking at the pg_class entries for the referenced tables,
> I have noticed that in the old database the table OID and the column
> relfilenode have different values. In the migrated database the values
> are the same and coincide with relfilenode in the old database.
>
> For example,
>
> Old database:
>
> Table name: common_inst.asset_spot_equity_index
> pg_class.oid = 284551
> pg_class.relfilenode = 288011
>
> Migrated database:
>
> Table name: common_inst.asset_spot_equity_index
> pg_class.oid = 288011
> pg_class.relfilenode = 288011
>
>
> I am trying to obtain a binary dump of a small test database where this
> issue could be reproduced, but so far, no luck. At present, the least
> such database is 1.5 GB compressed and contains a lot of proprietary
> info. I would welcome any suggestions on how to do this.

Your diagnosis is 100% on target, and very perceptive.  Because we
preserve pg_class.relfilenode, if the table has not been rebuilt, for
example by CLUSTER, the old system the pg_class.oid and
pg_class.relfilenode are the same, and hence pg_class.oid is preserved
through pg_class.relfilenode during the migration.  If they are
different, e.g. they ran CLUSTER, pg_upgrade will be wrong because the
oid has changed, and you will see the errors you are reporting.

I am inclined to prevent pg_upgrade from migrating any database that
uses any of these reg* data types, and document this restriction.  I
probably could allow regtype because that pg_type is preserved.

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

  + None of us is going to be here forever. +