Re: pg_upgrade issues - Mailing list pgsql-bugs

Bruce Momjian wrote:
> depstein@alliedtesting.com wrote:
> > 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.

I have applied the attached patch to CVS HEAD and 9.0 that prevent
migration when any reg* data type is used in a user table (except
regtype because pg_type.oid is preserved).

I documented this restriction.  Thanks again for the report.

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

  + None of us is going to be here forever. +
Index: contrib/pg_upgrade/check.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/pg_upgrade/check.c,v
retrieving revision 1.12
diff -c -c -r1.12 check.c
*** contrib/pg_upgrade/check.c    13 Jul 2010 15:56:53 -0000    1.12
--- contrib/pg_upgrade/check.c    25 Jul 2010 03:19:48 -0000
***************
*** 14,19 ****
--- 14,20 ----
  static void check_new_db_is_empty(migratorContext *ctx);
  static void check_locale_and_encoding(migratorContext *ctx, ControlData *oldctrl,
                            ControlData *newctrl);
+ static void check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster);


  void
***************
*** 61,71 ****
       * Check for various failure cases
       */

!     old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);

      /* old = PG 8.3 checks? */
      if (GET_MAJOR_VERSION(ctx->old.major_version) <= 803)
      {
          old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
          old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
          if (ctx->check)
--- 62,73 ----
       * Check for various failure cases
       */

!     check_for_reg_data_type_usage(ctx, CLUSTER_OLD);

      /* old = PG 8.3 checks? */
      if (GET_MAJOR_VERSION(ctx->old.major_version) <= 803)
      {
+         old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);
          old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
          old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
          if (ctx->check)
***************
*** 439,441 ****
--- 441,544 ----

      check_ok(ctx);
  }
+
+
+ /*
+  * 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.
+  */
+ void
+ check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster)
+ {
+     ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
+     &ctx->old : &ctx->new;
+     int            dbnum;
+     FILE       *script = NULL;
+     bool        found = false;
+     char        output_path[MAXPGPATH];
+
+     prep_status(ctx, "Checking for reg* system oid user data types");
+
+     snprintf(output_path, sizeof(output_path), "%s/tables_using_reg.txt",
+              ctx->cwd);
+
+     for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
+     {
+         PGresult   *res;
+         bool        db_used = false;
+         int            ntups;
+         int            rowno;
+         int            i_nspname,
+                     i_relname,
+                     i_attname;
+         DbInfo       *active_db = &active_cluster->dbarr.dbs[dbnum];
+         PGconn       *conn = connectToServer(ctx, active_db->db_name, whichCluster);
+
+         res = executeQueryOrDie(ctx, conn,
+                                 "SELECT n.nspname, c.relname, a.attname "
+                                 "FROM    pg_catalog.pg_class c, "
+                                 "        pg_catalog.pg_namespace n, "
+                                 "        pg_catalog.pg_attribute a "
+                                 "WHERE    c.oid = a.attrelid AND "
+                                 "        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");
+         i_relname = PQfnumber(res, "relname");
+         i_attname = PQfnumber(res, "attname");
+         for (rowno = 0; rowno < ntups; rowno++)
+         {
+             found = true;
+             if (script == NULL && (script = fopen(output_path, "w")) == NULL)
+                 pg_log(ctx, PG_FATAL, "Could not create necessary file:  %s\n", output_path);
+             if (!db_used)
+             {
+                 fprintf(script, "Database:  %s\n", active_db->db_name);
+                 db_used = true;
+             }
+             fprintf(script, "  %s.%s.%s\n",
+                     PQgetvalue(res, rowno, i_nspname),
+                     PQgetvalue(res, rowno, i_relname),
+                     PQgetvalue(res, rowno, i_attname));
+         }
+
+         PQclear(res);
+
+         PQfinish(conn);
+     }
+
+     if (found)
+     {
+         fclose(script);
+         pg_log(ctx, PG_REPORT, "fatal\n");
+         pg_log(ctx, PG_FATAL,
+                "| Your installation contains one of the reg* data types in\n"
+                "| user tables.  These data types reference system oids that\n"
+                "| are not preserved by pg_upgrade, so this cluster cannot\n"
+                "| currently be upgraded.  You can remove the problem tables\n"
+                "| and restart the migration.  A list of the problem columns\n"
+                "| is in the file:\n"
+                "| \t%s\n\n", output_path);
+     }
+     else
+         check_ok(ctx);
+ }
Index: contrib/pg_upgrade/function.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/pg_upgrade/function.c,v
retrieving revision 1.6
diff -c -c -r1.6 function.c
*** contrib/pg_upgrade/function.c    3 Jul 2010 16:33:14 -0000    1.6
--- contrib/pg_upgrade/function.c    25 Jul 2010 03:19:48 -0000
***************
*** 253,259 ****
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!              "| Your installation uses loadable libraries that are missing\n"
               "| from the new installation.  You can add these libraries to\n"
                 "| the new installation, or remove the functions using them\n"
              "| from the old installation.  A list of the problem libraries\n"
--- 253,259 ----
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!              "| Your installation references loadable libraries that are missing\n"
               "| from the new installation.  You can add these libraries to\n"
                 "| the new installation, or remove the functions using them\n"
              "| from the old installation.  A list of the problem libraries\n"
Index: contrib/pg_upgrade/version_old_8_3.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/pg_upgrade/version_old_8_3.c,v
retrieving revision 1.6
diff -c -c -r1.6 version_old_8_3.c
*** contrib/pg_upgrade/version_old_8_3.c    3 Jul 2010 16:33:14 -0000    1.6
--- contrib/pg_upgrade/version_old_8_3.c    25 Jul 2010 03:19:48 -0000
***************
*** 94,100 ****
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!                "| Your installation uses the \"name\" data type in\n"
                 "| user tables.  This data type changed its internal\n"
                 "| alignment between your old and new clusters so this\n"
                 "| cluster cannot currently be upgraded.  You can\n"
--- 94,100 ----
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!                "| Your installation contains the \"name\" data type in\n"
                 "| user tables.  This data type changed its internal\n"
                 "| alignment between your old and new clusters so this\n"
                 "| cluster cannot currently be upgraded.  You can\n"
***************
*** 184,190 ****
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!                "| Your installation uses the \"tsquery\" data type.\n"
                 "| This data type added a new internal field between\n"
                 "| your old and new clusters so this cluster cannot\n"
                 "| currently be upgraded.  You can remove the problem\n"
--- 184,190 ----
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!                "| Your installation contains the \"tsquery\" data type.\n"
                 "| This data type added a new internal field between\n"
                 "| your old and new clusters so this cluster cannot\n"
                 "| currently be upgraded.  You can remove the problem\n"
***************
*** 274,280 ****
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!                "| Your installation uses \"/contrib/isn\" functions\n"
                 "| which rely on the bigint data type.  Your old and\n"
                 "| new clusters pass bigint values differently so this\n"
                 "| cluster cannot currently be upgraded.  You can\n"
--- 274,280 ----
          fclose(script);
          pg_log(ctx, PG_REPORT, "fatal\n");
          pg_log(ctx, PG_FATAL,
!                "| Your installation contains \"/contrib/isn\" functions\n"
                 "| which rely on the bigint data type.  Your old and\n"
                 "| new clusters pass bigint values differently so this\n"
                 "| cluster cannot currently be upgraded.  You can\n"
Index: doc/src/sgml/pgupgrade.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/pgupgrade.sgml,v
retrieving revision 1.12
diff -c -c -r1.12 pgupgrade.sgml
*** doc/src/sgml/pgupgrade.sgml    25 May 2010 15:55:28 -0000    1.12
--- doc/src/sgml/pgupgrade.sgml    25 Jul 2010 03:19:49 -0000
***************
*** 445,453 ****
     </listitem>
    </orderedlist>

!   </sect2>

!   <sect2>
    <title>Limitations in migrating <emphasis>from</> PostgreSQL 8.3</title>

    <para>
--- 445,453 ----
     </listitem>
    </orderedlist>

!  </sect2>

!  <sect2>
    <title>Limitations in migrating <emphasis>from</> PostgreSQL 8.3</title>

    <para>
***************
*** 514,529 ****
     version 8.4 or later of the one-click distribution. It is not
     possible to upgrade from the MSI installer to the one-click installer.
    </para>

    <para>
!    All failure, rebuild, and reindex cases will be reported by <application>pg_upgrade</>
!    if they affect your installation; post-migration scripts to rebuild
!    tables and indexes will be automatically generated.
    </para>

    <para>
     For deployment testing, create a schema-only copy of the old cluster,
!    insert dummy data, and migrate that.
    </para>

    <para>
--- 514,543 ----
     version 8.4 or later of the one-click distribution. It is not
     possible to upgrade from the MSI installer to the one-click installer.
    </para>
+
+  </sect2>
+
+  <sect2>
+   <title>Notes</title>
+
+   <para>
+    <application>pg_upgrade</> does not support migration of databases
+    containing these reg* system oid-referencing data types:
+    <type>regproc</>, <type>regprocedure</>, <type>regoper</>,
+    <type>regoperator</>, <type>regclass</>, <type>regconfig</>, and
+    <type>regdictionary</>.  (<type>regtype</> can be migrated.)
+   </para>

    <para>
!    All failure, rebuild, and reindex cases will be reported by
!    <application>pg_upgrade</> if they affect your installation;
!    post-migration scripts to rebuild tables and indexes will be
!    generated automatically.
    </para>

    <para>
     For deployment testing, create a schema-only copy of the old cluster,
!    insert dummy data, and migrate that.
    </para>

    <para>