4 * Postgres-version-specific routines
6 * Copyright (c) 2010, PostgreSQL Global Development Group
7 * contrib/pg_upgrade/version_old_8_3.c
10 #include "pg_upgrade.h"
12 #include "access/transam.h"
16 * old_8_3_check_for_name_data_type_usage()
18 * Alignment for the 'name' data type changed to 'char' in 8.4;
19 * checks tables and indexes.
22 old_8_3_check_for_name_data_type_usage(Cluster whichCluster)
24 ClusterInfo *active_cluster = ACTIVE_CLUSTER(whichCluster);
28 char output_path[MAXPGPATH];
30 prep_status("Checking for invalid 'name' user columns");
32 snprintf(output_path, sizeof(output_path), "%s/tables_using_name.txt",
35 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
44 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
45 PGconn *conn = connectToServer(active_db->db_name, whichCluster);
48 * With a smaller alignment in 8.4, 'name' cannot be used in a
49 * non-pg_catalog table, except as the first column. (We could tighten
50 * that condition with enough analysis, but it seems not worth the
53 res = executeQueryOrDie(conn,
54 "SELECT n.nspname, c.relname, a.attname "
55 "FROM pg_catalog.pg_class c, "
56 " pg_catalog.pg_namespace n, "
57 " pg_catalog.pg_attribute a "
58 "WHERE c.oid = a.attrelid AND "
60 " NOT a.attisdropped AND "
61 " a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
62 " c.relnamespace = n.oid AND "
63 " n.nspname != 'pg_catalog' AND "
64 " n.nspname != 'information_schema'");
66 ntups = PQntuples(res);
67 i_nspname = PQfnumber(res, "nspname");
68 i_relname = PQfnumber(res, "relname");
69 i_attname = PQfnumber(res, "attname");
70 for (rowno = 0; rowno < ntups; rowno++)
73 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
74 pg_log(PG_FATAL, "Could not create necessary file: %s\n", output_path);
77 fprintf(script, "Database: %s\n", active_db->db_name);
80 fprintf(script, " %s.%s.%s\n",
81 PQgetvalue(res, rowno, i_nspname),
82 PQgetvalue(res, rowno, i_relname),
83 PQgetvalue(res, rowno, i_attname));
94 pg_log(PG_REPORT, "fatal\n");
96 "| Your installation contains the \"name\" data type in\n"
97 "| user tables. This data type changed its internal\n"
98 "| alignment between your old and new clusters so this\n"
99 "| cluster cannot currently be upgraded. You can\n"
100 "| remove the problem tables and restart the migration.\n"
101 "| A list of the problem columns is in the file:\n"
102 "| \t%s\n\n", output_path);
110 * old_8_3_check_for_tsquery_usage()
112 * A new 'prefix' field was added to the 'tsquery' data type in 8.4
113 * so migration of such fields is impossible.
116 old_8_3_check_for_tsquery_usage(Cluster whichCluster)
118 ClusterInfo *active_cluster = ACTIVE_CLUSTER(whichCluster);
122 char output_path[MAXPGPATH];
124 prep_status("Checking for tsquery user columns");
126 snprintf(output_path, sizeof(output_path), "%s/tables_using_tsquery.txt",
129 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
132 bool db_used = false;
138 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
139 PGconn *conn = connectToServer(active_db->db_name, whichCluster);
141 /* Find any user-defined tsquery columns */
142 res = executeQueryOrDie(conn,
143 "SELECT n.nspname, c.relname, a.attname "
144 "FROM pg_catalog.pg_class c, "
145 " pg_catalog.pg_namespace n, "
146 " pg_catalog.pg_attribute a "
147 "WHERE c.relkind = 'r' AND "
148 " c.oid = a.attrelid AND "
149 " NOT a.attisdropped AND "
150 " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
151 " c.relnamespace = n.oid AND "
152 " n.nspname != 'pg_catalog' AND "
153 " n.nspname != 'information_schema'");
155 ntups = PQntuples(res);
156 i_nspname = PQfnumber(res, "nspname");
157 i_relname = PQfnumber(res, "relname");
158 i_attname = PQfnumber(res, "attname");
159 for (rowno = 0; rowno < ntups; rowno++)
162 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
163 pg_log(PG_FATAL, "Could not create necessary file: %s\n", output_path);
166 fprintf(script, "Database: %s\n", active_db->db_name);
169 fprintf(script, " %s.%s.%s\n",
170 PQgetvalue(res, rowno, i_nspname),
171 PQgetvalue(res, rowno, i_relname),
172 PQgetvalue(res, rowno, i_attname));
183 pg_log(PG_REPORT, "fatal\n");
185 "| Your installation contains the \"tsquery\" data type.\n"
186 "| This data type added a new internal field between\n"
187 "| your old and new clusters so this cluster cannot\n"
188 "| currently be upgraded. You can remove the problem\n"
189 "| columns and restart the migration. A list of the\n"
190 "| problem columns is in the file:\n"
191 "| \t%s\n\n", output_path);
199 * old_8_3_rebuild_tsvector_tables()
201 * 8.3 sorts lexemes by its length and if lengths are the same then it uses
202 * alphabetic order; 8.4 sorts lexemes in lexicographical order, e.g.
204 * => SELECT 'c bb aaa'::tsvector;
207 * 'aaa' 'bb' 'c' -- 8.4
208 * 'c' 'bb' 'aaa' -- 8.3
211 old_8_3_rebuild_tsvector_tables(bool check_mode,
212 Cluster whichCluster)
214 ClusterInfo *active_cluster = ACTIVE_CLUSTER(whichCluster);
218 char output_path[MAXPGPATH];
220 prep_status("Checking for tsvector user columns");
222 snprintf(output_path, sizeof(output_path), "%s/rebuild_tsvector_tables.sql",
225 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
228 bool db_used = false;
229 char old_nspname[NAMEDATALEN] = "",
230 old_relname[NAMEDATALEN] = "";
236 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
237 PGconn *conn = connectToServer(active_db->db_name, whichCluster);
239 /* Find any user-defined tsvector columns */
240 res = executeQueryOrDie(conn,
241 "SELECT n.nspname, c.relname, a.attname "
242 "FROM pg_catalog.pg_class c, "
243 " pg_catalog.pg_namespace n, "
244 " pg_catalog.pg_attribute a "
245 "WHERE c.relkind = 'r' AND "
246 " c.oid = a.attrelid AND "
247 " NOT a.attisdropped AND "
248 " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
249 " c.relnamespace = n.oid AND "
250 " n.nspname != 'pg_catalog' AND "
251 " n.nspname != 'information_schema'");
254 * This macro is used below to avoid reindexing indexes already rebuilt
255 * because of tsvector columns.
257 #define SKIP_TSVECTOR_TABLES \
258 "i.indrelid NOT IN ( " \
259 "SELECT DISTINCT c.oid " \
260 "FROM pg_catalog.pg_class c, " \
261 " pg_catalog.pg_namespace n, " \
262 " pg_catalog.pg_attribute a " \
263 "WHERE c.relkind = 'r' AND " \
264 " c.oid = a.attrelid AND " \
265 " NOT a.attisdropped AND " \
266 " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
267 " c.relnamespace = n.oid AND " \
268 " n.nspname != 'pg_catalog' AND " \
269 " n.nspname != 'information_schema') "
271 ntups = PQntuples(res);
272 i_nspname = PQfnumber(res, "nspname");
273 i_relname = PQfnumber(res, "relname");
274 i_attname = PQfnumber(res, "attname");
275 for (rowno = 0; rowno < ntups; rowno++)
280 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
281 pg_log(PG_FATAL, "Could not create necessary file: %s\n", output_path);
284 fprintf(script, "\\connect %s\n\n",
285 quote_identifier(active_db->db_name));
289 /* Rebuild all tsvector collumns with one ALTER TABLE command */
290 if (strcmp(PQgetvalue(res, rowno, i_nspname), old_nspname) != 0 ||
291 strcmp(PQgetvalue(res, rowno, i_relname), old_relname) != 0)
293 if (strlen(old_nspname) != 0 || strlen(old_relname) != 0)
294 fprintf(script, ";\n\n");
295 fprintf(script, "ALTER TABLE %s.%s\n",
296 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
297 quote_identifier(PQgetvalue(res, rowno, i_relname)));
300 fprintf(script, ",\n");
301 strlcpy(old_nspname, PQgetvalue(res, rowno, i_nspname), sizeof(old_nspname));
302 strlcpy(old_relname, PQgetvalue(res, rowno, i_relname), sizeof(old_relname));
304 fprintf(script, "ALTER COLUMN %s "
305 /* This could have been a custom conversion function call. */
306 "TYPE pg_catalog.tsvector USING %s::pg_catalog.text::pg_catalog.tsvector",
307 quote_identifier(PQgetvalue(res, rowno, i_attname)),
308 quote_identifier(PQgetvalue(res, rowno, i_attname)));
311 if (strlen(old_nspname) != 0 || strlen(old_relname) != 0)
312 fprintf(script, ";\n\n");
316 /* XXX Mark tables as not accessable somehow */
325 report_status(PG_WARNING, "warning");
327 pg_log(PG_WARNING, "\n"
328 "| Your installation contains tsvector columns.\n"
329 "| The tsvector internal storage format changed\n"
330 "| between your old and new clusters so the tables\n"
331 "| must be rebuilt. After migration, you will be\n"
332 "| given instructions.\n\n");
334 pg_log(PG_WARNING, "\n"
335 "| Your installation contains tsvector columns.\n"
336 "| The tsvector internal storage format changed\n"
337 "| between your old and new clusters so the tables\n"
338 "| must be rebuilt. The file:\n"
340 "| when executed by psql by the database super-user\n"
341 "| will rebuild all tables with tsvector columns.\n\n",
350 * old_8_3_invalidate_hash_gin_indexes()
352 * Hash, Gin, and GiST index binary format has changes from 8.3->8.4
355 old_8_3_invalidate_hash_gin_indexes(bool check_mode,
356 Cluster whichCluster)
358 ClusterInfo *active_cluster = ACTIVE_CLUSTER(whichCluster);
362 char output_path[MAXPGPATH];
364 prep_status("Checking for hash and gin indexes");
366 snprintf(output_path, sizeof(output_path), "%s/reindex_hash_and_gin.sql",
369 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
372 bool db_used = false;
377 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
378 PGconn *conn = connectToServer(active_db->db_name, whichCluster);
380 /* find hash and gin indexes */
381 res = executeQueryOrDie(conn,
382 "SELECT n.nspname, c.relname "
383 "FROM pg_catalog.pg_class c, "
384 " pg_catalog.pg_index i, "
385 " pg_catalog.pg_am a, "
386 " pg_catalog.pg_namespace n "
387 "WHERE i.indexrelid = c.oid AND "
388 " c.relam = a.oid AND "
389 " c.relnamespace = n.oid AND "
390 " a.amname IN ('hash', 'gin') AND "
391 SKIP_TSVECTOR_TABLES);
393 ntups = PQntuples(res);
394 i_nspname = PQfnumber(res, "nspname");
395 i_relname = PQfnumber(res, "relname");
396 for (rowno = 0; rowno < ntups; rowno++)
401 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
402 pg_log(PG_FATAL, "Could not create necessary file: %s\n", output_path);
405 fprintf(script, "\\connect %s\n",
406 quote_identifier(active_db->db_name));
409 fprintf(script, "REINDEX INDEX %s.%s;\n",
410 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
411 quote_identifier(PQgetvalue(res, rowno, i_relname)));
417 if (!check_mode && found)
418 /* mark hash and gin indexes as invalid */
419 PQclear(executeQueryOrDie(conn,
420 "UPDATE pg_catalog.pg_index i "
421 "SET indisvalid = false "
422 "FROM pg_catalog.pg_class c, "
423 " pg_catalog.pg_am a, "
424 " pg_catalog.pg_namespace n "
425 "WHERE i.indexrelid = c.oid AND "
426 " c.relam = a.oid AND "
427 " c.relnamespace = n.oid AND "
428 " a.amname IN ('hash', 'gin')"));
437 report_status(PG_WARNING, "warning");
439 pg_log(PG_WARNING, "\n"
440 "| Your installation contains hash and/or gin\n"
441 "| indexes. These indexes have different\n"
442 "| internal formats between your old and new\n"
443 "| clusters so they must be reindexed with the\n"
444 "| REINDEX command. After migration, you will\n"
445 "| be given REINDEX instructions.\n\n");
447 pg_log(PG_WARNING, "\n"
448 "| Your installation contains hash and/or gin\n"
449 "| indexes. These indexes have different internal\n"
450 "| formats between your old and new clusters so\n"
451 "| they must be reindexed with the REINDEX command.\n"
454 "| when executed by psql by the database super-user\n"
455 "| will recreate all invalid indexes; until then,\n"
456 "| none of these indexes will be used.\n\n",
465 * old_8_3_invalidate_bpchar_pattern_ops_indexes()
467 * 8.4 bpchar_pattern_ops no longer sorts based on trailing spaces
470 old_8_3_invalidate_bpchar_pattern_ops_indexes(bool check_mode,
471 Cluster whichCluster)
473 ClusterInfo *active_cluster = ACTIVE_CLUSTER(whichCluster);
477 char output_path[MAXPGPATH];
479 prep_status("Checking for bpchar_pattern_ops indexes");
481 snprintf(output_path, sizeof(output_path), "%s/reindex_bpchar_ops.sql",
484 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
487 bool db_used = false;
492 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
493 PGconn *conn = connectToServer(active_db->db_name, whichCluster);
495 /* find bpchar_pattern_ops indexes */
498 * Do only non-hash, non-gin indexees; we already invalidated them
499 * above; no need to reindex twice
501 res = executeQueryOrDie(conn,
502 "SELECT n.nspname, c.relname "
503 "FROM pg_catalog.pg_index i, "
504 " pg_catalog.pg_class c, "
505 " pg_catalog.pg_namespace n "
506 "WHERE indexrelid = c.oid AND "
507 " c.relnamespace = n.oid AND "
510 " FROM pg_catalog.pg_opclass o, "
511 " pg_catalog.pg_am a"
512 " WHERE a.amname NOT IN ('hash', 'gin') AND "
513 " a.oid = o.opcmethod AND "
514 " o.opcname = 'bpchar_pattern_ops') "
515 " = ANY (i.indclass) AND "
516 SKIP_TSVECTOR_TABLES);
518 ntups = PQntuples(res);
519 i_nspname = PQfnumber(res, "nspname");
520 i_relname = PQfnumber(res, "relname");
521 for (rowno = 0; rowno < ntups; rowno++)
526 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
527 pg_log(PG_FATAL, "Could not create necessary file: %s\n", output_path);
530 fprintf(script, "\\connect %s\n",
531 quote_identifier(active_db->db_name));
534 fprintf(script, "REINDEX INDEX %s.%s;\n",
535 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
536 quote_identifier(PQgetvalue(res, rowno, i_relname)));
542 if (!check_mode && found)
543 /* mark bpchar_pattern_ops indexes as invalid */
544 PQclear(executeQueryOrDie(conn,
545 "UPDATE pg_catalog.pg_index i "
546 "SET indisvalid = false "
547 "FROM pg_catalog.pg_class c, "
548 " pg_catalog.pg_namespace n "
549 "WHERE indexrelid = c.oid AND "
550 " c.relnamespace = n.oid AND "
553 " FROM pg_catalog.pg_opclass o, "
554 " pg_catalog.pg_am a"
555 " WHERE a.amname NOT IN ('hash', 'gin') AND "
556 " a.oid = o.opcmethod AND "
557 " o.opcname = 'bpchar_pattern_ops') "
558 " = ANY (i.indclass)"));
567 report_status(PG_WARNING, "warning");
569 pg_log(PG_WARNING, "\n"
570 "| Your installation contains indexes using\n"
571 "| \"bpchar_pattern_ops\". These indexes have\n"
572 "| different internal formats between your old and\n"
573 "| new clusters so they must be reindexed with the\n"
574 "| REINDEX command. After migration, you will be\n"
575 "| given REINDEX instructions.\n\n");
577 pg_log(PG_WARNING, "\n"
578 "| Your installation contains indexes using\n"
579 "| \"bpchar_pattern_ops\". These indexes have\n"
580 "| different internal formats between your old and\n"
581 "| new clusters so they must be reindexed with the\n"
582 "| REINDEX command. The file:\n"
584 "| when executed by psql by the database super-user\n"
585 "| will recreate all invalid indexes; until then,\n"
586 "| none of these indexes will be used.\n\n",
595 * old_8_3_create_sequence_script()
597 * 8.4 added the column "start_value" to all sequences. For this reason,
598 * we don't transfer sequence files but instead use the CREATE SEQUENCE
599 * command from the schema dump, and use setval() to restore the sequence
600 * value and 'is_called' from the old database. This is safe to run
601 * by pg_upgrade because sequence files are not transfered from the old
602 * server, even in link mode.
605 old_8_3_create_sequence_script(Cluster whichCluster)
607 ClusterInfo *active_cluster = ACTIVE_CLUSTER(whichCluster);
611 char *output_path = pg_malloc(MAXPGPATH);
613 snprintf(output_path, MAXPGPATH, "%s/adjust_sequences.sql", os_info.cwd);
615 prep_status("Creating script to adjust sequences");
617 for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
620 bool db_used = false;
625 DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
626 PGconn *conn = connectToServer(active_db->db_name, whichCluster);
628 /* Find any sequences */
629 res = executeQueryOrDie(conn,
630 "SELECT n.nspname, c.relname "
631 "FROM pg_catalog.pg_class c, "
632 " pg_catalog.pg_namespace n "
633 "WHERE c.relkind = 'S' AND "
634 " c.relnamespace = n.oid AND "
635 " n.nspname != 'pg_catalog' AND "
636 " n.nspname != 'information_schema'");
638 ntups = PQntuples(res);
639 i_nspname = PQfnumber(res, "nspname");
640 i_relname = PQfnumber(res, "relname");
641 for (rowno = 0; rowno < ntups; rowno++)
646 const char *nspname = PQgetvalue(res, rowno, i_nspname);
647 const char *relname = PQgetvalue(res, rowno, i_relname);
651 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
652 pg_log(PG_FATAL, "Could not create necessary file: %s\n", output_path);
655 fprintf(script, "\\connect %s\n\n",
656 quote_identifier(active_db->db_name));
660 /* Find the desired sequence */
661 seq_res = executeQueryOrDie(conn,
662 "SELECT s.last_value, s.is_called "
664 quote_identifier(nspname),
665 quote_identifier(relname));
667 assert(PQntuples(seq_res) == 1);
668 i_last_value = PQfnumber(seq_res, "last_value");
669 i_is_called = PQfnumber(seq_res, "is_called");
671 fprintf(script, "SELECT setval('%s.%s', %s, '%s');\n",
672 quote_identifier(nspname), quote_identifier(relname),
673 PQgetvalue(seq_res, 0, i_last_value), PQgetvalue(seq_res, 0, i_is_called));
677 fprintf(script, "\n");
692 pg_free(output_path);