🌐 AI搜索 & 代理 主页
Skip to content

Commit f31b1fd

Browse files
bmomjianbmomjian
committed
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.
1 parent c3f903a commit f31b1fd

File tree

4 files changed

+132
-15
lines changed

4 files changed

+132
-15
lines changed

‎contrib/pg_upgrade/check.c‎

Lines changed: 105 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* server checks and output routines
55
*
66
* Copyright (c) 2010, PostgreSQL Global Development Group
7-
* $PostgreSQL: pgsql/contrib/pg_upgrade/check.c,v 1.12 2010/07/13 15:56:53 momjian Exp $
7+
* $PostgreSQL: pgsql/contrib/pg_upgrade/check.c,v 1.13 2010/07/25 03:28:32 momjian Exp $
88
*/
99

1010
#include "pg_upgrade.h"
@@ -14,6 +14,7 @@ static void set_locale_and_encoding(migratorContext *ctx, Cluster whichCluster);
1414
static void check_new_db_is_empty(migratorContext *ctx);
1515
static void check_locale_and_encoding(migratorContext *ctx, ControlData *oldctrl,
1616
ControlData *newctrl);
17+
static void check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster);
1718

1819

1920
void
@@ -61,11 +62,12 @@ check_old_cluster(migratorContext *ctx, bool live_check,
6162
* Check for various failure cases
6263
*/
6364

64-
old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);
65+
check_for_reg_data_type_usage(ctx, CLUSTER_OLD);
6566

6667
/* old = PG 8.3 checks? */
6768
if (GET_MAJOR_VERSION(ctx->old.major_version) <= 803)
6869
{
70+
old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);
6971
old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
7072
old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
7173
if (ctx->check)
@@ -439,3 +441,104 @@ create_script_for_old_cluster_deletion(migratorContext *ctx,
439441

440442
check_ok(ctx);
441443
}
444+
445+
446+
/*
447+
* check_for_reg_data_type_usage()
448+
* pg_upgrade only preserves these system values:
449+
* pg_class.relfilenode
450+
* pg_type.oid
451+
* pg_enum.oid
452+
*
453+
* Most of the reg* data types reference system catalog info that is
454+
* not preserved, and hence these data types cannot be used in user
455+
* tables upgraded by pg_upgrade.
456+
*/
457+
void
458+
check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster)
459+
{
460+
ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
461+
&ctx->old : &ctx->new;
462+
int dbnum;
463+
FILE *script = NULL;
464+
bool found = false;
465+
char output_path[MAXPGPATH];
466+
467+
prep_status(ctx, "Checking for reg* system oid user data types");
468+
469+
snprintf(output_path, sizeof(output_path), "%s/tables_using_reg.txt",
470+
ctx->cwd);
471+
472+
for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
473+
{
474+
PGresult *res;
475+
bool db_used = false;
476+
int ntups;
477+
int rowno;
478+
int i_nspname,
479+
i_relname,
480+
i_attname;
481+
DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
482+
PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
483+
484+
res = executeQueryOrDie(ctx, conn,
485+
"SELECT n.nspname, c.relname, a.attname "
486+
"FROM pg_catalog.pg_class c, "
487+
" pg_catalog.pg_namespace n, "
488+
" pg_catalog.pg_attribute a "
489+
"WHERE c.oid = a.attrelid AND "
490+
" NOT a.attisdropped AND "
491+
" a.atttypid IN ( "
492+
" 'pg_catalog.regproc'::pg_catalog.regtype, "
493+
" 'pg_catalog.regprocedure'::pg_catalog.regtype, "
494+
" 'pg_catalog.regoper'::pg_catalog.regtype, "
495+
" 'pg_catalog.regoperator'::pg_catalog.regtype, "
496+
" 'pg_catalog.regclass'::pg_catalog.regtype, "
497+
/* regtype.oid is preserved, so 'regtype' is OK */
498+
" 'pg_catalog.regconfig'::pg_catalog.regtype, "
499+
" 'pg_catalog.regdictionary'::pg_catalog.regtype) AND "
500+
" c.relnamespace = n.oid AND "
501+
" n.nspname != 'pg_catalog' AND "
502+
" n.nspname != 'information_schema'");
503+
504+
ntups = PQntuples(res);
505+
i_nspname = PQfnumber(res, "nspname");
506+
i_relname = PQfnumber(res, "relname");
507+
i_attname = PQfnumber(res, "attname");
508+
for (rowno = 0; rowno < ntups; rowno++)
509+
{
510+
found = true;
511+
if (script == NULL && (script = fopen(output_path, "w")) == NULL)
512+
pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
513+
if (!db_used)
514+
{
515+
fprintf(script, "Database: %s\n", active_db->db_name);
516+
db_used = true;
517+
}
518+
fprintf(script, " %s.%s.%s\n",
519+
PQgetvalue(res, rowno, i_nspname),
520+
PQgetvalue(res, rowno, i_relname),
521+
PQgetvalue(res, rowno, i_attname));
522+
}
523+
524+
PQclear(res);
525+
526+
PQfinish(conn);
527+
}
528+
529+
if (found)
530+
{
531+
fclose(script);
532+
pg_log(ctx, PG_REPORT, "fatal\n");
533+
pg_log(ctx, PG_FATAL,
534+
"| Your installation contains one of the reg* data types in\n"
535+
"| user tables. These data types reference system oids that\n"
536+
"| are not preserved by pg_upgrade, so this cluster cannot\n"
537+
"| currently be upgraded. You can remove the problem tables\n"
538+
"| and restart the migration. A list of the problem columns\n"
539+
"| is in the file:\n"
540+
"| \t%s\n\n", output_path);
541+
}
542+
else
543+
check_ok(ctx);
544+
}

‎contrib/pg_upgrade/function.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* server-side function support
55
*
66
* Copyright (c) 2010, PostgreSQL Global Development Group
7-
* $PostgreSQL: pgsql/contrib/pg_upgrade/function.c,v 1.6 2010/07/03 16:33:14 momjian Exp $
7+
* $PostgreSQL: pgsql/contrib/pg_upgrade/function.c,v 1.7 2010/07/25 03:28:32 momjian Exp $
88
*/
99

1010
#include "pg_upgrade.h"
@@ -253,7 +253,7 @@ check_loadable_libraries(migratorContext *ctx)
253253
fclose(script);
254254
pg_log(ctx, PG_REPORT, "fatal\n");
255255
pg_log(ctx, PG_FATAL,
256-
"| Your installation uses loadable libraries that are missing\n"
256+
"| Your installation references loadable libraries that are missing\n"
257257
"| from the new installation. You can add these libraries to\n"
258258
"| the new installation, or remove the functions using them\n"
259259
"| from the old installation. A list of the problem libraries\n"

‎contrib/pg_upgrade/version_old_8_3.c‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* Postgres-version-specific routines
55
*
66
* Copyright (c) 2010, PostgreSQL Global Development Group
7-
* $PostgreSQL: pgsql/contrib/pg_upgrade/version_old_8_3.c,v 1.6 2010/07/03 16:33:14 momjian Exp $
7+
* $PostgreSQL: pgsql/contrib/pg_upgrade/version_old_8_3.c,v 1.7 2010/07/25 03:28:32 momjian Exp $
88
*/
99

1010
#include "pg_upgrade.h"
@@ -94,7 +94,7 @@ old_8_3_check_for_name_data_type_usage(migratorContext *ctx, Cluster whichCluste
9494
fclose(script);
9595
pg_log(ctx, PG_REPORT, "fatal\n");
9696
pg_log(ctx, PG_FATAL,
97-
"| Your installation uses the \"name\" data type in\n"
97+
"| Your installation contains the \"name\" data type in\n"
9898
"| user tables. This data type changed its internal\n"
9999
"| alignment between your old and new clusters so this\n"
100100
"| cluster cannot currently be upgraded. You can\n"
@@ -184,7 +184,7 @@ old_8_3_check_for_tsquery_usage(migratorContext *ctx, Cluster whichCluster)
184184
fclose(script);
185185
pg_log(ctx, PG_REPORT, "fatal\n");
186186
pg_log(ctx, PG_FATAL,
187-
"| Your installation uses the \"tsquery\" data type.\n"
187+
"| Your installation contains the \"tsquery\" data type.\n"
188188
"| This data type added a new internal field between\n"
189189
"| your old and new clusters so this cluster cannot\n"
190190
"| 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
274274
fclose(script);
275275
pg_log(ctx, PG_REPORT, "fatal\n");
276276
pg_log(ctx, PG_FATAL,
277-
"| Your installation uses \"/contrib/isn\" functions\n"
277+
"| Your installation contains \"/contrib/isn\" functions\n"
278278
"| which rely on the bigint data type. Your old and\n"
279279
"| new clusters pass bigint values differently so this\n"
280280
"| cluster cannot currently be upgraded. You can\n"

‎doc/src/sgml/pgupgrade.sgml‎

Lines changed: 21 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/pgupgrade.sgml,v 1.12 2010/05/25 15:55:28 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/pgupgrade.sgml,v 1.13 2010/07/25 03:28:32 momjian Exp $ -->
22

33
<sect1 id="pgupgrade">
44
<title>pg_upgrade</title>
@@ -445,9 +445,9 @@ psql --username postgres --file script.sql postgres
445445
</listitem>
446446
</orderedlist>
447447

448-
</sect2>
448+
</sect2>
449449

450-
<sect2>
450+
<sect2>
451451
<title>Limitations in migrating <emphasis>from</> PostgreSQL 8.3</title>
452452

453453
<para>
@@ -514,16 +514,30 @@ psql --username postgres --file script.sql postgres
514514
version 8.4 or later of the one-click distribution. It is not
515515
possible to upgrade from the MSI installer to the one-click installer.
516516
</para>
517+
518+
</sect2>
519+
520+
<sect2>
521+
<title>Notes</title>
522+
523+
<para>
524+
<application>pg_upgrade</> does not support migration of databases
525+
containing these reg* system oid-referencing data types:
526+
<type>regproc</>, <type>regprocedure</>, <type>regoper</>,
527+
<type>regoperator</>, <type>regclass</>, <type>regconfig</>, and
528+
<type>regdictionary</>. (<type>regtype</> can be migrated.)
529+
</para>
517530

518531
<para>
519-
All failure, rebuild, and reindex cases will be reported by <application>pg_upgrade</>
520-
if they affect your installation; post-migration scripts to rebuild
521-
tables and indexes will be automatically generated.
532+
All failure, rebuild, and reindex cases will be reported by
533+
<application>pg_upgrade</> if they affect your installation;
534+
post-migration scripts to rebuild tables and indexes will be
535+
generated automatically.
522536
</para>
523537

524538
<para>
525539
For deployment testing, create a schema-only copy of the old cluster,
526-
insert dummy data, and migrate that.
540+
insert dummy data, and migrate that.
527541
</para>
528542

529543
<para>

0 commit comments

Comments
 (0)