From f31b1fd7c1af812561f8729f80ecc8ad55f81a2a Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 25 Jul 2010 03:28:32 +0000 Subject: [PATCH] Prevent pg_upgrade from migrating databases that use reg* data types where the oid is not preserved by pg_upgrade (everything but pg_type). Update documentation. Per bug report from depstein@alliedtesting.com. --- contrib/pg_upgrade/check.c | 107 ++++++++++++++++++++++++++- contrib/pg_upgrade/function.c | 4 +- contrib/pg_upgrade/version_old_8_3.c | 8 +- doc/src/sgml/pgupgrade.sgml | 28 +++++-- 4 files changed, 132 insertions(+), 15 deletions(-) diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c index cdf213dac59..60cf86c4ef5 100644 --- a/contrib/pg_upgrade/check.c +++ b/contrib/pg_upgrade/check.c @@ -4,7 +4,7 @@ * server checks and output routines * * Copyright (c) 2010, PostgreSQL Global Development Group - * $PostgreSQL: pgsql/contrib/pg_upgrade/check.c,v 1.12 2010/07/13 15:56:53 momjian Exp $ + * $PostgreSQL: pgsql/contrib/pg_upgrade/check.c,v 1.13 2010/07/25 03:28:32 momjian Exp $ */ #include "pg_upgrade.h" @@ -14,6 +14,7 @@ static void set_locale_and_encoding(migratorContext *ctx, Cluster whichCluster); 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,11 +62,12 @@ check_old_cluster(migratorContext *ctx, bool live_check, * Check for various failure cases */ - old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD); + 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,3 +441,104 @@ create_script_for_old_cluster_deletion(migratorContext *ctx, 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); +} diff --git a/contrib/pg_upgrade/function.c b/contrib/pg_upgrade/function.c index 3187506df08..261e63b729f 100644 --- a/contrib/pg_upgrade/function.c +++ b/contrib/pg_upgrade/function.c @@ -4,7 +4,7 @@ * server-side function support * * Copyright (c) 2010, PostgreSQL Global Development Group - * $PostgreSQL: pgsql/contrib/pg_upgrade/function.c,v 1.6 2010/07/03 16:33:14 momjian Exp $ + * $PostgreSQL: pgsql/contrib/pg_upgrade/function.c,v 1.7 2010/07/25 03:28:32 momjian Exp $ */ #include "pg_upgrade.h" @@ -253,7 +253,7 @@ check_loadable_libraries(migratorContext *ctx) fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, - "| Your installation uses loadable libraries that are missing\n" + "| 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" diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c index 824c9680547..d9a1ef923b7 100644 --- a/contrib/pg_upgrade/version_old_8_3.c +++ b/contrib/pg_upgrade/version_old_8_3.c @@ -4,7 +4,7 @@ * Postgres-version-specific routines * * Copyright (c) 2010, PostgreSQL Global Development Group - * $PostgreSQL: pgsql/contrib/pg_upgrade/version_old_8_3.c,v 1.6 2010/07/03 16:33:14 momjian Exp $ + * $PostgreSQL: pgsql/contrib/pg_upgrade/version_old_8_3.c,v 1.7 2010/07/25 03:28:32 momjian Exp $ */ #include "pg_upgrade.h" @@ -94,7 +94,7 @@ old_8_3_check_for_name_data_type_usage(migratorContext *ctx, Cluster whichCluste fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, - "| Your installation uses the \"name\" data type in\n" + "| 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,7 +184,7 @@ old_8_3_check_for_tsquery_usage(migratorContext *ctx, Cluster whichCluster) fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, - "| Your installation uses the \"tsquery\" data type.\n" + "| 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,7 +274,7 @@ old_8_3_check_for_isn_and_int8_passing_mismatch(migratorContext *ctx, Cluster wh fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, - "| Your installation uses \"/contrib/isn\" functions\n" + "| 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" diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml index 69ff9dba1b2..e7e858e1a71 100644 --- a/doc/src/sgml/pgupgrade.sgml +++ b/doc/src/sgml/pgupgrade.sgml @@ -1,4 +1,4 @@ - + pg_upgrade @@ -445,9 +445,9 @@ psql --username postgres --file script.sql postgres - + - + Limitations in migrating <emphasis>from</> PostgreSQL 8.3 @@ -514,16 +514,30 @@ psql --username postgres --file script.sql postgres 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. + + + + + Notes + + + pg_upgrade does not support migration of databases + containing these reg* system oid-referencing data types: + regproc, regprocedure, regoper, + regoperator, regclass, regconfig, and + regdictionary. (regtype can be migrated.) + - All failure, rebuild, and reindex cases will be reported by pg_upgrade - if they affect your installation; post-migration scripts to rebuild - tables and indexes will be automatically generated. + All failure, rebuild, and reindex cases will be reported by + pg_upgrade if they affect your installation; + post-migration scripts to rebuild tables and indexes will be + generated automatically. For deployment testing, create a schema-only copy of the old cluster, - insert dummy data, and migrate that. + insert dummy data, and migrate that. -- 2.30.2