4 * Postgres-version-specific routines
7 #include "pg_upgrade.h"
9 #include "access/transam.h"
13 * old_8_3_check_for_name_data_type_usage()
15 * Alignment for the 'name' data type changed to 'char' in 8.4;
16 * checks tables and indexes.
19 old_8_3_check_for_name_data_type_usage(migratorContext *ctx, Cluster whichCluster)
21 ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
22 &ctx->old : &ctx->new;
26 char output_path[MAXPGPATH];
28 prep_status(ctx, "Checking for invalid 'name' user columns");
30 snprintf(output_path, sizeof(output_path), "%s/tables_using_name.txt",
33 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
42 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
43 PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
46 * With a smaller alignment in 8.4, 'name' cannot be used in a
47 * non-pg_catalog table, except as the first column. (We could tighten
48 * that condition with enough analysis, but it seems not worth the
51 res = executeQueryOrDie(ctx, conn,
52 "SELECT n.nspname, c.relname, a.attname "
53 "FROM pg_catalog.pg_class c, "
54 " pg_catalog.pg_namespace n, "
55 " pg_catalog.pg_attribute a "
56 "WHERE c.oid = a.attrelid AND "
58 " NOT a.attisdropped AND "
59 " a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
60 " c.relnamespace = n.oid AND "
61 " n.nspname != 'pg_catalog' AND "
62 " n.nspname != 'information_schema'");
64 ntups = PQntuples(res);
65 i_nspname = PQfnumber(res, "nspname");
66 i_relname = PQfnumber(res, "relname");
67 i_attname = PQfnumber(res, "attname");
68 for (rowno = 0; rowno < ntups; rowno++)
71 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
72 pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
75 fprintf(script, "Database: %s\n", active_db->db_name);
78 fprintf(script, " %s.%s.%s\n",
79 PQgetvalue(res, rowno, i_nspname),
80 PQgetvalue(res, rowno, i_relname),
81 PQgetvalue(res, rowno, i_attname));
92 pg_log(ctx, PG_REPORT, "fatal\n");
94 "| Your installation uses the \"name\" data type in\n"
95 "| user tables. This data type changed its internal\n"
96 "| alignment between your old and new clusters so this\n"
97 "| cluster cannot currently be upgraded. You can\n"
98 "| remove the problem tables and restart the migration.\n"
99 "| A list of the problem columns is in the file:\n"
100 "| \t%s\n\n", output_path);
108 * old_8_3_check_for_tsquery_usage()
110 * A new 'prefix' field was added to the 'tsquery' data type in 8.4
111 * so migration of such fields is impossible.
114 old_8_3_check_for_tsquery_usage(migratorContext *ctx, Cluster whichCluster)
116 ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
117 &ctx->old : &ctx->new;
121 char output_path[MAXPGPATH];
123 prep_status(ctx, "Checking for tsquery user columns");
125 snprintf(output_path, sizeof(output_path), "%s/tables_using_tsquery.txt",
128 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
131 bool db_used = false;
137 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
138 PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
140 /* Find any user-defined tsquery columns */
141 res = executeQueryOrDie(ctx, conn,
142 "SELECT n.nspname, c.relname, a.attname "
143 "FROM pg_catalog.pg_class c, "
144 " pg_catalog.pg_namespace n, "
145 " pg_catalog.pg_attribute a "
146 "WHERE c.relkind = 'r' AND "
147 " c.oid = a.attrelid AND "
148 " NOT a.attisdropped AND "
149 " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
150 " c.relnamespace = n.oid AND "
151 " n.nspname != 'pg_catalog' AND "
152 " n.nspname != 'information_schema'");
154 ntups = PQntuples(res);
155 i_nspname = PQfnumber(res, "nspname");
156 i_relname = PQfnumber(res, "relname");
157 i_attname = PQfnumber(res, "attname");
158 for (rowno = 0; rowno < ntups; rowno++)
161 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
162 pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
165 fprintf(script, "Database: %s\n", active_db->db_name);
168 fprintf(script, " %s.%s.%s\n",
169 PQgetvalue(res, rowno, i_nspname),
170 PQgetvalue(res, rowno, i_relname),
171 PQgetvalue(res, rowno, i_attname));
182 pg_log(ctx, PG_REPORT, "fatal\n");
183 pg_log(ctx, PG_FATAL,
184 "| Your installation uses the \"tsquery\" data type.\n"
185 "| This data type added a new internal field between\n"
186 "| your old and new clusters so this cluster cannot\n"
187 "| currently be upgraded. You can remove the problem\n"
188 "| columns and restart the migration. A list of the\n"
189 "| problem columns is in the file:\n"
190 "| \t%s\n\n", output_path);
198 * old_8_3_check_for_isn_and_int8_passing_mismatch()
200 * /contrib/isn relies on data type int8, and in 8.4 int8 is now passed
201 * by value. The schema dumps the CREATE TYPE PASSEDBYVALUE setting so
202 * it must match for the old and new servers.
205 old_8_3_check_for_isn_and_int8_passing_mismatch(migratorContext *ctx, Cluster whichCluster)
207 ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
208 &ctx->old : &ctx->new;
212 char output_path[MAXPGPATH];
214 prep_status(ctx, "Checking for /contrib/isn with bigint-passing mismatch");
216 if (ctx->old.controldata.float8_pass_by_value ==
217 ctx->new.controldata.float8_pass_by_value)
224 snprintf(output_path, sizeof(output_path), "%s/contrib_isn_and_int8_pass_by_value.txt",
227 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
230 bool db_used = false;
235 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
236 PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
238 /* Find any functions coming from contrib/isn */
239 res = executeQueryOrDie(ctx, conn,
240 "SELECT n.nspname, p.proname "
241 "FROM pg_catalog.pg_proc p, "
242 " pg_catalog.pg_namespace n "
243 "WHERE p.pronamespace = n.oid AND "
244 " p.probin = '$libdir/isn'");
246 ntups = PQntuples(res);
247 i_nspname = PQfnumber(res, "nspname");
248 i_proname = PQfnumber(res, "proname");
249 for (rowno = 0; rowno < ntups; rowno++)
252 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
253 pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
256 fprintf(script, "Database: %s\n", active_db->db_name);
259 fprintf(script, " %s.%s\n",
260 PQgetvalue(res, rowno, i_nspname),
261 PQgetvalue(res, rowno, i_proname));
272 pg_log(ctx, PG_REPORT, "fatal\n");
273 pg_log(ctx, PG_FATAL,
274 "| Your installation uses \"/contrib/isn\" functions\n"
275 "| which rely on the bigint data type. Your old and\n"
276 "| new clusters pass bigint values differently so this\n"
277 "| cluster cannot currently be upgraded. You can\n"
278 "| manually migrate data that use \"/contrib/isn\"\n"
279 "| facilities and remove \"/contrib/isn\" from the\n"
280 "| old cluster and restart the migration. A list\n"
281 "| of the problem functions is in the file:\n"
282 "| \t%s\n\n", output_path);
290 * old_8_3_rebuild_tsvector_tables()
292 * 8.3 sorts lexemes by its length and if lengths are the same then it uses
293 * alphabetic order; 8.4 sorts lexemes in lexicographical order, e.g.
295 * => SELECT 'c bb aaa'::tsvector;
298 * 'aaa' 'bb' 'c' -- 8.4
299 * 'c' 'bb' 'aaa' -- 8.3
302 old_8_3_rebuild_tsvector_tables(migratorContext *ctx, bool check_mode,
303 Cluster whichCluster)
305 ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
306 &ctx->old : &ctx->new;
310 char output_path[MAXPGPATH];
312 prep_status(ctx, "Checking for tsvector user columns");
314 snprintf(output_path, sizeof(output_path), "%s/rebuild_tsvector_tables.sql",
317 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
320 bool db_used = false;
321 char old_nspname[NAMEDATALEN] = "",
322 old_relname[NAMEDATALEN] = "";
328 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
329 PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
331 /* Find any user-defined tsvector columns */
332 res = executeQueryOrDie(ctx, conn,
333 "SELECT n.nspname, c.relname, a.attname "
334 "FROM pg_catalog.pg_class c, "
335 " pg_catalog.pg_namespace n, "
336 " pg_catalog.pg_attribute a "
337 "WHERE c.relkind = 'r' AND "
338 " c.oid = a.attrelid AND "
339 " NOT a.attisdropped AND "
340 " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
341 " c.relnamespace = n.oid AND "
342 " n.nspname != 'pg_catalog' AND "
343 " n.nspname != 'information_schema'");
346 * This macro is used below to avoid reindexing indexes already rebuilt
347 * because of tsvector columns.
349 #define SKIP_TSVECTOR_TABLES \
350 "i.indrelid NOT IN ( " \
351 "SELECT DISTINCT c.oid " \
352 "FROM pg_catalog.pg_class c, " \
353 " pg_catalog.pg_namespace n, " \
354 " pg_catalog.pg_attribute a " \
355 "WHERE c.relkind = 'r' AND " \
356 " c.oid = a.attrelid AND " \
357 " NOT a.attisdropped AND " \
358 " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
359 " c.relnamespace = n.oid AND " \
360 " n.nspname != 'pg_catalog' AND " \
361 " n.nspname != 'information_schema') "
363 ntups = PQntuples(res);
364 i_nspname = PQfnumber(res, "nspname");
365 i_relname = PQfnumber(res, "relname");
366 i_attname = PQfnumber(res, "attname");
367 for (rowno = 0; rowno < ntups; rowno++)
372 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
373 pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
376 fprintf(script, "\\connect %s\n\n",
377 quote_identifier(ctx, active_db->db_name));
381 /* Rebuild all tsvector collumns with one ALTER TABLE command */
382 if (strcmp(PQgetvalue(res, rowno, i_nspname), old_nspname) != 0 ||
383 strcmp(PQgetvalue(res, rowno, i_relname), old_relname) != 0)
385 if (strlen(old_nspname) != 0 || strlen(old_relname) != 0)
386 fprintf(script, ";\n\n");
387 fprintf(script, "ALTER TABLE %s.%s\n",
388 quote_identifier(ctx, PQgetvalue(res, rowno, i_nspname)),
389 quote_identifier(ctx, PQgetvalue(res, rowno, i_relname)));
392 fprintf(script, ",\n");
393 strlcpy(old_nspname, PQgetvalue(res, rowno, i_nspname), sizeof(old_nspname));
394 strlcpy(old_relname, PQgetvalue(res, rowno, i_relname), sizeof(old_relname));
396 fprintf(script, "ALTER COLUMN %s "
397 /* This could have been a custom conversion function call. */
398 "TYPE pg_catalog.tsvector USING %s::pg_catalog.text::pg_catalog.tsvector",
399 quote_identifier(ctx, PQgetvalue(res, rowno, i_attname)),
400 quote_identifier(ctx, PQgetvalue(res, rowno, i_attname)));
403 if (strlen(old_nspname) != 0 || strlen(old_relname) != 0)
404 fprintf(script, ";\n\n");
408 /* XXX Mark tables as not accessable somehow */
417 report_status(ctx, PG_WARNING, "warning");
419 pg_log(ctx, PG_WARNING, "\n"
420 "| Your installation contains tsvector columns.\n"
421 "| The tsvector internal storage format changed\n"
422 "| between your old and new clusters so the tables\n"
423 "| must be rebuilt. After migration, you will be\n"
424 "| given instructions.\n\n");
426 pg_log(ctx, PG_WARNING, "\n"
427 "| Your installation contains tsvector columns.\n"
428 "| The tsvector internal storage format changed\n"
429 "| between your old and new clusters so the tables\n"
430 "| must be rebuilt. The file:\n"
432 "| when executed by psql by the database super-user\n"
433 "| will rebuild all tables with tsvector columns.\n\n",
442 * old_8_3_invalidate_hash_gin_indexes()
444 * Hash, Gin, and GiST index binary format has changes from 8.3->8.4
447 old_8_3_invalidate_hash_gin_indexes(migratorContext *ctx, bool check_mode,
448 Cluster whichCluster)
450 ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
451 &ctx->old : &ctx->new;
455 char output_path[MAXPGPATH];
457 prep_status(ctx, "Checking for hash and gin indexes");
459 snprintf(output_path, sizeof(output_path), "%s/reindex_hash_and_gin.sql",
462 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
465 bool db_used = false;
470 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
471 PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
473 /* find hash and gin indexes */
474 res = executeQueryOrDie(ctx, conn,
475 "SELECT n.nspname, c.relname "
476 "FROM pg_catalog.pg_class c, "
477 " pg_catalog.pg_index i, "
478 " pg_catalog.pg_am a, "
479 " pg_catalog.pg_namespace n "
480 "WHERE i.indexrelid = c.oid AND "
481 " c.relam = a.oid AND "
482 " c.relnamespace = n.oid AND "
483 " a.amname IN ('hash', 'gin') AND "
484 SKIP_TSVECTOR_TABLES);
486 ntups = PQntuples(res);
487 i_nspname = PQfnumber(res, "nspname");
488 i_relname = PQfnumber(res, "relname");
489 for (rowno = 0; rowno < ntups; rowno++)
494 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
495 pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
498 fprintf(script, "\\connect %s\n",
499 quote_identifier(ctx, active_db->db_name));
502 fprintf(script, "REINDEX INDEX %s.%s;\n",
503 quote_identifier(ctx, PQgetvalue(res, rowno, i_nspname)),
504 quote_identifier(ctx, PQgetvalue(res, rowno, i_relname)));
510 if (!check_mode && found)
511 /* mark hash and gin indexes as invalid */
512 PQclear(executeQueryOrDie(ctx, conn,
513 "UPDATE pg_catalog.pg_index i "
514 "SET indisvalid = false "
515 "FROM pg_catalog.pg_class c, "
516 " pg_catalog.pg_am a, "
517 " pg_catalog.pg_namespace n "
518 "WHERE i.indexrelid = c.oid AND "
519 " c.relam = a.oid AND "
520 " c.relnamespace = n.oid AND "
521 " a.amname IN ('hash', 'gin')"));
530 report_status(ctx, PG_WARNING, "warning");
532 pg_log(ctx, PG_WARNING, "\n"
533 "| Your installation contains hash and/or gin\n"
534 "| indexes. These indexes have different\n"
535 "| internal formats between your old and new\n"
536 "| clusters so they must be reindexed with the\n"
537 "| REINDEX command. After migration, you will\n"
538 "| be given REINDEX instructions.\n\n");
540 pg_log(ctx, PG_WARNING, "\n"
541 "| Your installation contains hash and/or gin\n"
542 "| indexes. These indexes have different internal\n"
543 "| formats between your old and new clusters so\n"
544 "| they must be reindexed with the REINDEX command.\n"
547 "| when executed by psql by the database super-user\n"
548 "| will recreate all invalid indexes; until then,\n"
549 "| none of these indexes will be used.\n\n",
558 * old_8_3_invalidate_bpchar_pattern_ops_indexes()
560 * 8.4 bpchar_pattern_ops no longer sorts based on trailing spaces
563 old_8_3_invalidate_bpchar_pattern_ops_indexes(migratorContext *ctx, bool check_mode,
564 Cluster whichCluster)
566 ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
567 &ctx->old : &ctx->new;
571 char output_path[MAXPGPATH];
573 prep_status(ctx, "Checking for bpchar_pattern_ops indexes");
575 snprintf(output_path, sizeof(output_path), "%s/reindex_bpchar_ops.sql",
578 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
581 bool db_used = false;
586 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
587 PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
589 /* find bpchar_pattern_ops indexes */
592 * Do only non-hash, non-gin indexees; we already invalidated them
593 * above; no need to reindex twice
595 res = executeQueryOrDie(ctx, conn,
596 "SELECT n.nspname, c.relname "
597 "FROM pg_catalog.pg_index i, "
598 " pg_catalog.pg_class c, "
599 " pg_catalog.pg_namespace n "
600 "WHERE indexrelid = c.oid AND "
601 " c.relnamespace = n.oid AND "
604 " FROM pg_catalog.pg_opclass o, "
605 " pg_catalog.pg_am a"
606 " WHERE a.amname NOT IN ('hash', 'gin') AND "
607 " a.oid = o.opcmethod AND "
608 " o.opcname = 'bpchar_pattern_ops') "
609 " = ANY (i.indclass) AND "
610 SKIP_TSVECTOR_TABLES);
612 ntups = PQntuples(res);
613 i_nspname = PQfnumber(res, "nspname");
614 i_relname = PQfnumber(res, "relname");
615 for (rowno = 0; rowno < ntups; rowno++)
620 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
621 pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
624 fprintf(script, "\\connect %s\n",
625 quote_identifier(ctx, active_db->db_name));
628 fprintf(script, "REINDEX INDEX %s.%s;\n",
629 quote_identifier(ctx, PQgetvalue(res, rowno, i_nspname)),
630 quote_identifier(ctx, PQgetvalue(res, rowno, i_relname)));
636 if (!check_mode && found)
637 /* mark bpchar_pattern_ops indexes as invalid */
638 PQclear(executeQueryOrDie(ctx, conn,
639 "UPDATE pg_catalog.pg_index i "
640 "SET indisvalid = false "
641 "FROM pg_catalog.pg_class c, "
642 " pg_catalog.pg_namespace n "
643 "WHERE indexrelid = c.oid AND "
644 " c.relnamespace = n.oid AND "
647 " FROM pg_catalog.pg_opclass o, "
648 " pg_catalog.pg_am a"
649 " WHERE a.amname NOT IN ('hash', 'gin') AND "
650 " a.oid = o.opcmethod AND "
651 " o.opcname = 'bpchar_pattern_ops') "
652 " = ANY (i.indclass)"));
661 report_status(ctx, PG_WARNING, "warning");
663 pg_log(ctx, PG_WARNING, "\n"
664 "| Your installation contains indexes using\n"
665 "| \"bpchar_pattern_ops\". These indexes have\n"
666 "| different internal formats between your old and\n"
667 "| new clusters so they must be reindexed with the\n"
668 "| REINDEX command. After migration, you will be\n"
669 "| given REINDEX instructions.\n\n");
671 pg_log(ctx, PG_WARNING, "\n"
672 "| Your installation contains indexes using\n"
673 "| \"bpchar_pattern_ops\". These indexes have\n"
674 "| different internal formats between your old and\n"
675 "| new clusters so they must be reindexed with the\n"
676 "| REINDEX command. The file:\n"
678 "| when executed by psql by the database super-user\n"
679 "| will recreate all invalid indexes; until then,\n"
680 "| none of these indexes will be used.\n\n",
689 * old_8_3_create_sequence_script()
691 * 8.4 added the column "start_value" to all sequences. For this reason,
692 * we don't transfer sequence files but instead use the CREATE SEQUENCE
693 * command from the schema dump, and use setval() to restore the sequence
694 * value and 'is_called' from the old database. This is safe to run
695 * by pg_upgrade because sequence files are not transfered from the old
696 * server, even in link mode.
699 old_8_3_create_sequence_script(migratorContext *ctx, Cluster whichCluster)
701 ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
702 &ctx->old : &ctx->new;
706 char *output_path = pg_malloc(ctx, MAXPGPATH);
708 snprintf(output_path, MAXPGPATH, "%s/adjust_sequences.sql", ctx->output_dir);
710 prep_status(ctx, "Creating script to adjust sequences");
712 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
715 bool db_used = false;
720 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
721 PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
723 /* Find any sequences */
724 res = executeQueryOrDie(ctx, conn,
725 "SELECT n.nspname, c.relname "
726 "FROM pg_catalog.pg_class c, "
727 " pg_catalog.pg_namespace n "
728 "WHERE c.relkind = 'S' AND "
729 " c.relnamespace = n.oid AND "
730 " n.nspname != 'pg_catalog' AND "
731 " n.nspname != 'information_schema'");
733 ntups = PQntuples(res);
734 i_nspname = PQfnumber(res, "nspname");
735 i_relname = PQfnumber(res, "relname");
736 for (rowno = 0; rowno < ntups; rowno++)
741 const char *nspname = PQgetvalue(res, rowno, i_nspname);
742 const char *relname = PQgetvalue(res, rowno, i_relname);
746 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
747 pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
750 fprintf(script, "\\connect %s\n\n",
751 quote_identifier(ctx, active_db->db_name));
755 /* Find the desired sequence */
756 seq_res = executeQueryOrDie(ctx, conn,
757 "SELECT s.last_value, s.is_called "
759 quote_identifier(ctx, nspname),
760 quote_identifier(ctx, relname));
762 assert(PQntuples(seq_res) == 1);
763 i_last_value = PQfnumber(seq_res, "last_value");
764 i_is_called = PQfnumber(seq_res, "is_called");
766 fprintf(script, "SELECT setval('%s.%s', %s, '%s');\n",
767 quote_identifier(ctx, nspname), quote_identifier(ctx, relname),
768 PQgetvalue(seq_res, 0, i_last_value), PQgetvalue(seq_res, 0, i_is_called));
772 fprintf(script, "\n");
787 pg_free(output_path);