Re: [GENERAL] pg_upgrade problem - Mailing list pgsql-hackers

Bruce Momjian wrote:
> Tom Lane wrote:
> > hubert depesz lubaczewski <depesz@depesz.com> writes:
> > > Worked a bit to get the ltree problem down to smallest possible, repeatable, situation.
> >
> > I looked at this again and verified that indeed, commit
> > 8eee65c996048848c20f6637c1d12b319a4ce244 introduced an incompatible
> > change into the on-disk format of ltree columns: it widened
> > ltree_level.len, which is one component of an ltree on disk.
> > So the crash is hardly surprising.  I think that the only thing
> > pg_upgrade could do about it is refuse to upgrade when ltree columns
> > are present in an 8.3 database.  I'm not sure though how you'd identify
> > contrib/ltree versus some random user-defined type named ltree.
>
> It is actually easy to do using the attached patch.  I check for the
> functions that support the data type and check of they are from an
> 'ltree' shared object.  I don't check actual user table type names in
> this case.

Attached patch applied to 9.0, 9.1, and HEAD.  Doc changes included.

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

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 37c38c1..720f130
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_old_cluster(migratorContext *ctx,
*** 72,77 ****
--- 72,78 ----
      {
          old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
          old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
+         old_8_3_check_ltree_usage(ctx, CLUSTER_OLD);
          if (ctx->check)
          {
              old_8_3_rebuild_tsvector_tables(ctx, true, CLUSTER_OLD);
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 41c4b11..7a02fa1
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** void old_8_3_check_for_name_data_type_us
*** 394,399 ****
--- 394,401 ----
                                         Cluster whichCluster);
  void old_8_3_check_for_tsquery_usage(migratorContext *ctx,
                                  Cluster whichCluster);
+ void old_8_3_check_ltree_usage(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,
diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c
new file mode 100644
index 6fcd61b..7e3a7aa
*** a/contrib/pg_upgrade/version_old_8_3.c
--- b/contrib/pg_upgrade/version_old_8_3.c
*************** old_8_3_check_for_tsquery_usage(migrator
*** 204,209 ****
--- 204,289 ----


  /*
+  *    old_8_3_check_ltree_usage()
+  *    8.3 -> 8.4
+  *    The internal ltree structure was changed in 8.4 so upgrading is impossible.
+  */
+ void
+ old_8_3_check_ltree_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 /contrib/ltree");
+
+     snprintf(output_path, sizeof(output_path), "%s/contrib_ltree.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/ltree */
+         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/ltree'");
+
+         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 the \"ltree\" data type.  This data type\n"
+                "| changed its internal storage format between your old and new clusters so this\n"
+                "| cluster cannot currently be upgraded.  You can manually upgrade databases\n"
+                "| that use \"contrib/ltree\" facilities and remove \"contrib/ltree\" from the old\n"
+                "| cluster and restart the upgrade.  A list of the problem functions is in the\n"
+                "| 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
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 31f1c3d..74fba2e
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*************** psql --username postgres --file script.s
*** 465,470 ****
--- 465,475 ----
    </para>

    <para>
+    pg_upgrade will not work if the <filename>ltree</>
+    contrib module is installed in a database.
+   </para>
+
+   <para>
     You must drop any such columns and migrate them manually.
    </para>

diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 93b9e69..ea74648
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_old_cluster(bool live_check, char
*** 81,86 ****
--- 81,87 ----
      {
          old_8_3_check_for_name_data_type_usage(&old_cluster);
          old_8_3_check_for_tsquery_usage(&old_cluster);
+         old_8_3_check_ltree_usage(&old_cluster);
          if (user_opts.check)
          {
              old_8_3_rebuild_tsvector_tables(&old_cluster, true);
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index feea324..2abd917
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** void new_9_0_populate_pg_largeobject_met
*** 411,416 ****
--- 411,417 ----

  void        old_8_3_check_for_name_data_type_usage(ClusterInfo *cluster);
  void        old_8_3_check_for_tsquery_usage(ClusterInfo *cluster);
+ void        old_8_3_check_ltree_usage(ClusterInfo *cluster);
  void        old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode);
  void        old_8_3_invalidate_hash_gin_indexes(ClusterInfo *cluster, bool check_mode);
  void old_8_3_invalidate_bpchar_pattern_ops_indexes(ClusterInfo *cluster,
diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c
new file mode 100644
index 55d919c..625f4ad
*** a/contrib/pg_upgrade/version_old_8_3.c
--- b/contrib/pg_upgrade/version_old_8_3.c
*************** old_8_3_check_for_tsquery_usage(ClusterI
*** 202,207 ****
--- 202,288 ----


  /*
+  *    old_8_3_check_ltree_usage()
+  *    8.3 -> 8.4
+  *    The internal ltree structure was changed in 8.4 so upgrading is impossible.
+  */
+ void
+ old_8_3_check_ltree_usage(ClusterInfo *cluster)
+ {
+     int            dbnum;
+     FILE       *script = NULL;
+     bool        found = false;
+     char        output_path[MAXPGPATH];
+
+     prep_status("Checking for contrib/ltree");
+
+     snprintf(output_path, sizeof(output_path), "%s/contrib_ltree.txt",
+              os_info.cwd);
+
+     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
+     {
+         PGresult   *res;
+         bool        db_used = false;
+         int            ntups;
+         int            rowno;
+         int            i_nspname,
+                     i_proname;
+         DbInfo       *active_db = &cluster->dbarr.dbs[dbnum];
+         PGconn       *conn = connectToServer(cluster, active_db->db_name);
+
+         /* Find any functions coming from contrib/ltree */
+         res = executeQueryOrDie(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/ltree'");
+
+         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(PG_FATAL, "Could not open file \"%s\": %s\n",
+                        output_path, getErrorText(errno));
+             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 (script)
+         fclose(script);
+
+     if (found)
+     {
+         pg_log(PG_REPORT, "fatal\n");
+         pg_log(PG_FATAL,
+                "Your installation contains the \"ltree\" data type.  This data type\n"
+                "changed its internal storage format between your old and new clusters so this\n"
+                "cluster cannot currently be upgraded.  You can manually upgrade databases\n"
+                "that use \"contrib/ltree\" facilities and remove \"contrib/ltree\" from the old\n"
+                "cluster and restart the upgrade.  A list of the problem functions is in the\n"
+                "file:\n"
+                "    %s\n\n", output_path);
+     }
+     else
+         check_ok();
+ }
+
+
+ /*
   * 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
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 2d8bbce..9b0718d
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*************** psql --username postgres --file script.s
*** 488,493 ****
--- 488,498 ----
    </para>

    <para>
+    pg_upgrade will not work if the <filename>ltree</>
+    contrib module is installed in a database.
+   </para>
+
+   <para>
     pg_upgrade will require a table rebuild if:
     <itemizedlist>
      <listitem>