Re: pg_upgrade issues - Mailing list pgsql-bugs

Bruce Momjian wrote:
> 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.

Attached is a secondary patch for /contrib/isn, in case you want that
too.

--
  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.13
diff -c -c -r1.13 check.c
*** contrib/pg_upgrade/check.c    25 Jul 2010 03:28:32 -0000    1.13
--- contrib/pg_upgrade/check.c    25 Jul 2010 03:43:07 -0000
***************
*** 14,19 ****
--- 14,21 ----
  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_isn_and_int8_passing_mismatch(migratorContext *ctx,
+                                                 Cluster whichCluster);
  static void check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster);


***************
*** 63,73 ****
       */

      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)
--- 65,75 ----
       */

      check_for_reg_data_type_usage(ctx, CLUSTER_OLD);
+     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)
***************
*** 444,449 ****
--- 446,543 ----


  /*
+  *     check_for_isn_and_int8_passing_mismatch()
+  *
+  *    /contrib/isn relies on data type int8, and in 8.4 int8 can now be passed
+  *    by value.  The schema dumps the CREATE TYPE PASSEDBYVALUE setting so
+  *    it must match for the old and new servers.
+  */
+ void
+ check_for_isn_and_int8_passing_mismatch(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 /contrib/isn with bigint-passing mismatch");
+
+     if (ctx->old.controldata.float8_pass_by_value ==
+         ctx->new.controldata.float8_pass_by_value)
+     {
+         /* no mismatch */
+         check_ok(ctx);
+         return;
+     }
+
+     snprintf(output_path, sizeof(output_path), "%s/contrib_isn_and_int8_pass_by_value.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_proname;
+         DbInfo       *active_db = &active_cluster->dbarr.dbs[dbnum];
+         PGconn       *conn = connectToServer(ctx, active_db->db_name, whichCluster);
+
+         /* Find any functions coming from contrib/isn */
+         res = executeQueryOrDie(ctx, conn,
+                                 "SELECT n.nspname, p.proname "
+                                 "FROM    pg_catalog.pg_proc p, "
+                                 "        pg_catalog.pg_namespace n "
+                                 "WHERE    p.pronamespace = n.oid AND "
+                                 "        p.probin = '$libdir/isn'");
+
+         ntups = PQntuples(res);
+         i_nspname = PQfnumber(res, "nspname");
+         i_proname = PQfnumber(res, "proname");
+         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\n",
+                     PQgetvalue(res, rowno, i_nspname),
+                     PQgetvalue(res, rowno, i_proname));
+         }
+
+         PQclear(res);
+
+         PQfinish(conn);
+     }
+
+     if (found)
+     {
+         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"
+                "| manually migrate data that use \"/contrib/isn\"\n"
+                "| facilities and remove \"/contrib/isn\" from the\n"
+                "| old cluster and restart the migration.  A list\n"
+                "| of the problem functions is in the file:\n"
+                "| \t%s\n\n", output_path);
+     }
+     else
+         check_ok(ctx);
+ }
+
+
+ /*
   * check_for_reg_data_type_usage()
   *    pg_upgrade only preserves these system values:
   *        pg_class.relfilenode
Index: contrib/pg_upgrade/pg_upgrade.h
===================================================================
RCS file: /cvsroot/pgsql/contrib/pg_upgrade/pg_upgrade.h,v
retrieving revision 1.15
diff -c -c -r1.15 pg_upgrade.h
*** contrib/pg_upgrade/pg_upgrade.h    6 Jul 2010 19:18:55 -0000    1.15
--- contrib/pg_upgrade/pg_upgrade.h    25 Jul 2010 03:43:07 -0000
***************
*** 388,395 ****
                                         Cluster whichCluster);
  void old_8_3_check_for_tsquery_usage(migratorContext *ctx,
                                  Cluster whichCluster);
- void old_8_3_check_for_isn_and_int8_passing_mismatch(migratorContext *ctx,
-                                                 Cluster whichCluster);
  void old_8_3_rebuild_tsvector_tables(migratorContext *ctx,
                                  bool check_mode, Cluster whichCluster);
  void old_8_3_invalidate_hash_gin_indexes(migratorContext *ctx,
--- 388,393 ----
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.7
diff -c -c -r1.7 version_old_8_3.c
*** contrib/pg_upgrade/version_old_8_3.c    25 Jul 2010 03:28:32 -0000    1.7
--- contrib/pg_upgrade/version_old_8_3.c    25 Jul 2010 03:43:07 -0000
***************
*** 198,295 ****


  /*
-  * old_8_3_check_for_isn_and_int8_passing_mismatch()
-  *    8.3 -> 8.4
-  *    /contrib/isn relies on data type int8, and in 8.4 int8 is now passed
-  *    by value.  The schema dumps the CREATE TYPE PASSEDBYVALUE setting so
-  *    it must match for the old and new servers.
-  */
- void
- old_8_3_check_for_isn_and_int8_passing_mismatch(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 /contrib/isn with bigint-passing mismatch");
-
-     if (ctx->old.controldata.float8_pass_by_value ==
-         ctx->new.controldata.float8_pass_by_value)
-     {
-         /* no mismatch */
-         check_ok(ctx);
-         return;
-     }
-
-     snprintf(output_path, sizeof(output_path), "%s/contrib_isn_and_int8_pass_by_value.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_proname;
-         DbInfo       *active_db = &active_cluster->dbarr.dbs[dbnum];
-         PGconn       *conn = connectToServer(ctx, active_db->db_name, whichCluster);
-
-         /* Find any functions coming from contrib/isn */
-         res = executeQueryOrDie(ctx, conn,
-                                 "SELECT n.nspname, p.proname "
-                                 "FROM    pg_catalog.pg_proc p, "
-                                 "        pg_catalog.pg_namespace n "
-                                 "WHERE    p.pronamespace = n.oid AND "
-                                 "        p.probin = '$libdir/isn'");
-
-         ntups = PQntuples(res);
-         i_nspname = PQfnumber(res, "nspname");
-         i_proname = PQfnumber(res, "proname");
-         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\n",
-                     PQgetvalue(res, rowno, i_nspname),
-                     PQgetvalue(res, rowno, i_proname));
-         }
-
-         PQclear(res);
-
-         PQfinish(conn);
-     }
-
-     if (found)
-     {
-         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"
-                "| manually migrate data that use \"/contrib/isn\"\n"
-                "| facilities and remove \"/contrib/isn\" from the\n"
-                "| old cluster and restart the migration.  A list\n"
-                "| of the problem functions is in the file:\n"
-                "| \t%s\n\n", output_path);
-     }
-     else
-         check_ok(ctx);
- }
-
-
- /*
   * old_8_3_rebuild_tsvector_tables()
   *    8.3 -> 8.4
   * 8.3 sorts lexemes by its length and if lengths are the same then it uses
--- 198,203 ----