Re: pg_upgrade and regclass - Mailing list pgsql-hackers

Bruce Momjian wrote:
> In Postgres 8.4, pg_upgrade preserved pg_class relfilenodes by creating
> files in the file system.  In Postgres 9.0, we changed this by creating
> pg_upgrade_support functions which allow us to directly preserve
> pg_class.oids.
>
> Unfortunately, check.c was not updated to reflect this and clusters
> using regclass were prevented from being upgraded by pg_upgrade.
>
> I have developed the attached patch to allow clusters using regclass to
> be upgraded.  I plan to apply it to PG 9.0, 9.1, and HEAD.

I have applied the attached patch to all relevant releases.  I did a
more modest single-line code change for back branches.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 460d06b..ac3f99b
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*************** psql --username postgres --file script.s
*** 557,563 ****
     <application>pg_upgrade</> does not support upgrading of databases
     containing these <type>reg*</> OID-referencing system data types:
     <type>regproc</>, <type>regprocedure</>, <type>regoper</>,
!    <type>regoperator</>, <type>regclass</>, <type>regconfig</>, and
     <type>regdictionary</>.  (<type>regtype</> can be upgraded.)
    </para>

--- 557,563 ----
     <application>pg_upgrade</> does not support upgrading of databases
     containing these <type>reg*</> OID-referencing system data types:
     <type>regproc</>, <type>regprocedure</>, <type>regoper</>,
!    <type>regoperator</>, <type>regconfig</>, and
     <type>regdictionary</>.  (<type>regtype</> can be upgraded.)
    </para>

diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index d32a84c..7185f13
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_for_isn_and_int8_passing_mismatch(
*** 611,621 ****
  /*
   * check_for_reg_data_type_usage()
   *    pg_upgrade only preserves these system values:
!  *        pg_class.relfilenode
   *        pg_type.oid
   *        pg_enum.oid
   *
!  *    Most of the reg* data types reference system catalog info that is
   *    not preserved, and hence these data types cannot be used in user
   *    tables upgraded by pg_upgrade.
   */
--- 611,621 ----
  /*
   * check_for_reg_data_type_usage()
   *    pg_upgrade only preserves these system values:
!  *        pg_class.oid
   *        pg_type.oid
   *        pg_enum.oid
   *
!  *    Many of the reg* data types reference system catalog info that is
   *    not preserved, and hence these data types cannot be used in user
   *    tables upgraded by pg_upgrade.
   */
*************** check_for_reg_data_type_usage(ClusterInf
*** 653,668 ****
                                  "        NOT a.attisdropped AND "
                                  "        a.atttypid IN ( "
            "            'pg_catalog.regproc'::pg_catalog.regtype, "
!                                 "            'pg_catalog.regprocedure'::pg_catalog.regtype, "
            "            'pg_catalog.regoper'::pg_catalog.regtype, "
!                                 "            'pg_catalog.regoperator'::pg_catalog.regtype, "
!          "            'pg_catalog.regclass'::pg_catalog.regtype, "
          /* regtype.oid is preserved, so 'regtype' is OK */
!         "            'pg_catalog.regconfig'::pg_catalog.regtype, "
!                                 "            'pg_catalog.regdictionary'::pg_catalog.regtype) AND "
!                                 "        c.relnamespace = n.oid AND "
!                               "        n.nspname != 'pg_catalog' AND "
!                          "        n.nspname != 'information_schema'");

          ntups = PQntuples(res);
          i_nspname = PQfnumber(res, "nspname");
--- 653,668 ----
                                  "        NOT a.attisdropped AND "
                                  "        a.atttypid IN ( "
            "            'pg_catalog.regproc'::pg_catalog.regtype, "
!           "            'pg_catalog.regprocedure'::pg_catalog.regtype, "
            "            'pg_catalog.regoper'::pg_catalog.regtype, "
!           "            'pg_catalog.regoperator'::pg_catalog.regtype, "
!         /* regclass.oid is preserved, so 'regclass' is OK */
          /* regtype.oid is preserved, so 'regtype' is OK */
!           "            'pg_catalog.regconfig'::pg_catalog.regtype, "
!           "            'pg_catalog.regdictionary'::pg_catalog.regtype) AND "
!           "        c.relnamespace = n.oid AND "
!           "        n.nspname != 'pg_catalog' AND "
!           "        n.nspname != 'information_schema'");

          ntups = PQntuples(res);
          i_nspname = PQfnumber(res, "nspname");