Bruce Momjian wrote:
> [email protected] 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 <[email protected]> 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>
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
