granicus.if.org Git - postgresql/blob - contrib/pg_upgrade/version_old_8_3.c

   1 /*
   2  *      version.c
   3  *
   4  *      Postgres-version-specific routines
   5  *
   6  *      Copyright (c) 2010, PostgreSQL Global Development Group
   7  *      contrib/pg_upgrade/version_old_8_3.c
   8  */
   9
  10 #include "pg_upgrade.h"
  11
  12 #include "access/transam.h"
  13
  14
  15 /*
  16  * old_8_3_check_for_name_data_type_usage()
  17  *      8.3 -> 8.4
  18  *      Alignment for the 'name' data type changed to 'char' in 8.4;
  19  *      checks tables and indexes.
  20  */
  21 void
  22 old_8_3_check_for_name_data_type_usage(ClusterInfo *cluster)
  23 {
  24         int                     dbnum;
  25         FILE       *script = NULL;
  26         bool            found = false;
  27         char            output_path[MAXPGPATH];
  28
  29         prep_status("Checking for invalid 'name' user columns");
  30
  31         snprintf(output_path, sizeof(output_path), "%s/tables_using_name.txt",
  32                          os_info.cwd);
  33
  34         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
  35         {
  36                 PGresult   *res;
  37                 bool            db_used = false;
  38                 int                     ntups;
  39                 int                     rowno;
  40                 int                     i_nspname,
  41                                         i_relname,
  42                                         i_attname;
  43                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
  44                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
  45
  46                 /*
  47                  * With a smaller alignment in 8.4, 'name' cannot be used in a
  48                  * non-pg_catalog table, except as the first column. (We could tighten
  49                  * that condition with enough analysis, but it seems not worth the
  50                  * trouble.)
  51                  */
  52                 res = executeQueryOrDie(conn,
  53                                                                 "SELECT n.nspname, c.relname, a.attname "
  54                                                                 "FROM   pg_catalog.pg_class c, "
  55                                                                 "               pg_catalog.pg_namespace n, "
  56                                                                 "               pg_catalog.pg_attribute a "
  57                                                                 "WHERE  c.oid = a.attrelid AND "
  58                                                                 "               a.attnum > 1 AND "
  59                                                                 "               NOT a.attisdropped AND "
  60                                                                 "               a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
  61                                                                 "               c.relnamespace = n.oid AND "
  62                                                           "             n.nspname != 'pg_catalog' AND "
  63                                                  "              n.nspname != 'information_schema'");
  64
  65                 ntups = PQntuples(res);
  66                 i_nspname = PQfnumber(res, "nspname");
  67                 i_relname = PQfnumber(res, "relname");
  68                 i_attname = PQfnumber(res, "attname");
  69                 for (rowno = 0; rowno < ntups; rowno++)
  70                 {
  71                         found = true;
  72                         if (script == NULL && (script = fopen(output_path, "w")) == NULL)
  73                                 pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
  74                         if (!db_used)
  75                         {
  76                                 fprintf(script, "Database:  %s\n", active_db->db_name);
  77                                 db_used = true;
  78                         }
  79                         fprintf(script, "  %s.%s.%s\n",
  80                                         PQgetvalue(res, rowno, i_nspname),
  81                                         PQgetvalue(res, rowno, i_relname),
  82                                         PQgetvalue(res, rowno, i_attname));
  83                 }
  84
  85                 PQclear(res);
  86
  87                 PQfinish(conn);
  88         }
  89
  90         if (found)
  91         {
  92                 fclose(script);
  93                 pg_log(PG_REPORT, "fatal\n");
  94                 pg_log(PG_FATAL,
  95                            "| Your installation contains the \"name\" data type in\n"
  96                            "| user tables.  This data type changed its internal\n"
  97                            "| alignment between your old and new clusters so this\n"
  98                            "| cluster cannot currently be upgraded.  You can\n"
  99                            "| remove the problem tables and restart the upgrade.\n"
 100                            "| A list of the problem columns is in the file:\n"
 101                            "| \t%s\n\n", output_path);
 102         }
 103         else
 104                 check_ok();
 105 }
 106
 107
 108 /*
 109  * old_8_3_check_for_tsquery_usage()
 110  *      8.3 -> 8.4
 111  *      A new 'prefix' field was added to the 'tsquery' data type in 8.4
 112  *      so upgrading of such fields is impossible.
 113  */
 114 void
 115 old_8_3_check_for_tsquery_usage(ClusterInfo *cluster)
 116 {
 117         int                     dbnum;
 118         FILE       *script = NULL;
 119         bool            found = false;
 120         char            output_path[MAXPGPATH];
 121
 122         prep_status("Checking for tsquery user columns");
 123
 124         snprintf(output_path, sizeof(output_path), "%s/tables_using_tsquery.txt",
 125                          os_info.cwd);
 126
 127         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 128         {
 129                 PGresult   *res;
 130                 bool            db_used = false;
 131                 int                     ntups;
 132                 int                     rowno;
 133                 int                     i_nspname,
 134                                         i_relname,
 135                                         i_attname;
 136                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 137                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 138
 139                 /* Find any user-defined tsquery columns */
 140                 res = executeQueryOrDie(conn,
 141                                                                 "SELECT n.nspname, c.relname, a.attname "
 142                                                                 "FROM   pg_catalog.pg_class c, "
 143                                                                 "               pg_catalog.pg_namespace n, "
 144                                                                 "               pg_catalog.pg_attribute a "
 145                                                                 "WHERE  c.relkind = 'r' AND "
 146                                                                 "               c.oid = a.attrelid AND "
 147                                                                 "               NOT a.attisdropped AND "
 148                                                                 "               a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
 149                                                                 "               c.relnamespace = n.oid AND "
 150                                                           "             n.nspname != 'pg_catalog' AND "
 151                                                  "              n.nspname != 'information_schema'");
 152
 153                 ntups = PQntuples(res);
 154                 i_nspname = PQfnumber(res, "nspname");
 155                 i_relname = PQfnumber(res, "relname");
 156                 i_attname = PQfnumber(res, "attname");
 157                 for (rowno = 0; rowno < ntups; rowno++)
 158                 {
 159                         found = true;
 160                         if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 161                                 pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 162                         if (!db_used)
 163                         {
 164                                 fprintf(script, "Database:  %s\n", active_db->db_name);
 165                                 db_used = true;
 166                         }
 167                         fprintf(script, "  %s.%s.%s\n",
 168                                         PQgetvalue(res, rowno, i_nspname),
 169                                         PQgetvalue(res, rowno, i_relname),
 170                                         PQgetvalue(res, rowno, i_attname));
 171                 }
 172
 173                 PQclear(res);
 174
 175                 PQfinish(conn);
 176         }
 177
 178         if (found)
 179         {
 180                 fclose(script);
 181                 pg_log(PG_REPORT, "fatal\n");
 182                 pg_log(PG_FATAL,
 183                            "| Your installation contains the \"tsquery\" data type.\n"
 184                            "| This data type added a new internal field between\n"
 185                            "| your old and new clusters so this cluster cannot\n"
 186                            "| currently be upgraded.  You can remove the problem\n"
 187                            "| columns and restart the upgrade.  A list of the\n"
 188                            "| problem columns is in the file:\n"
 189                            "| \t%s\n\n", output_path);
 190         }
 191         else
 192                 check_ok();
 193 }
 194
 195
 196 /*
 197  * old_8_3_rebuild_tsvector_tables()
 198  *      8.3 -> 8.4
 199  * 8.3 sorts lexemes by its length and if lengths are the same then it uses
 200  * alphabetic order;  8.4 sorts lexemes in lexicographical order, e.g.
 201  *
 202  * => SELECT 'c bb aaa'::tsvector;
 203  *         tsvector
 204  * ----------------
 205  *      'aaa' 'bb' 'c'             -- 8.4
 206  *      'c' 'bb' 'aaa'             -- 8.3
 207  */
 208 void
 209 old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode)
 210 {
 211         int                     dbnum;
 212         FILE       *script = NULL;
 213         bool            found = false;
 214         char            output_path[MAXPGPATH];
 215
 216         prep_status("Checking for tsvector user columns");
 217
 218         snprintf(output_path, sizeof(output_path), "%s/rebuild_tsvector_tables.sql",
 219                          os_info.cwd);
 220
 221         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 222         {
 223                 PGresult   *res;
 224                 bool            db_used = false;
 225                 char            old_nspname[NAMEDATALEN] = "",
 226                                         old_relname[NAMEDATALEN] = "";
 227                 int                     ntups;
 228                 int                     rowno;
 229                 int                     i_nspname,
 230                                         i_relname,
 231                                         i_attname;
 232                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 233                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 234
 235                 /* Find any user-defined tsvector columns */
 236                 res = executeQueryOrDie(conn,
 237                                                                 "SELECT n.nspname, c.relname, a.attname "
 238                                                                 "FROM   pg_catalog.pg_class c, "
 239                                                                 "               pg_catalog.pg_namespace n, "
 240                                                                 "               pg_catalog.pg_attribute a "
 241                                                                 "WHERE  c.relkind = 'r' AND "
 242                                                                 "               c.oid = a.attrelid AND "
 243                                                                 "               NOT a.attisdropped AND "
 244                                                                 "               a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
 245                                                                 "               c.relnamespace = n.oid AND "
 246                                                           "             n.nspname != 'pg_catalog' AND "
 247                                                  "              n.nspname != 'information_schema'");
 248
 249 /*
 250  *      This macro is used below to avoid reindexing indexes already rebuilt
 251  *      because of tsvector columns.
 252  */
 253 #define SKIP_TSVECTOR_TABLES \
 254                                                                 "i.indrelid NOT IN ( "                                  \
 255                                                                 "SELECT DISTINCT c.oid "                                \
 256                                                                 "FROM   pg_catalog.pg_class c, "                \
 257                                                                 "               pg_catalog.pg_namespace n, "    \
 258                                                                 "               pg_catalog.pg_attribute a "             \
 259                                                                 "WHERE  c.relkind = 'r' AND "                   \
 260                                                                 "               c.oid = a.attrelid AND "                \
 261                                                                 "               NOT a.attisdropped AND "                \
 262                                                                 "               a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
 263                                                                 "               c.relnamespace = n.oid AND "    \
 264                                                                 "               n.nspname != 'pg_catalog' AND " \
 265                                                                 "               n.nspname != 'information_schema') "
 266
 267                 ntups = PQntuples(res);
 268                 i_nspname = PQfnumber(res, "nspname");
 269                 i_relname = PQfnumber(res, "relname");
 270                 i_attname = PQfnumber(res, "attname");
 271                 for (rowno = 0; rowno < ntups; rowno++)
 272                 {
 273                         found = true;
 274                         if (!check_mode)
 275                         {
 276                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 277                                         pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 278                                 if (!db_used)
 279                                 {
 280                                         fprintf(script, "\\connect %s\n\n",
 281                                                         quote_identifier(active_db->db_name));
 282                                         db_used = true;
 283                                 }
 284
 285                                 /* Rebuild all tsvector collumns with one ALTER TABLE command */
 286                                 if (strcmp(PQgetvalue(res, rowno, i_nspname), old_nspname) != 0 ||
 287                                  strcmp(PQgetvalue(res, rowno, i_relname), old_relname) != 0)
 288                                 {
 289                                         if (strlen(old_nspname) != 0 || strlen(old_relname) != 0)
 290                                                 fprintf(script, ";\n\n");
 291                                         fprintf(script, "ALTER TABLE %s.%s\n",
 292                                                  quote_identifier(PQgetvalue(res, rowno, i_nspname)),
 293                                                 quote_identifier(PQgetvalue(res, rowno, i_relname)));
 294                                 }
 295                                 else
 296                                         fprintf(script, ",\n");
 297                                 strlcpy(old_nspname, PQgetvalue(res, rowno, i_nspname), sizeof(old_nspname));
 298                                 strlcpy(old_relname, PQgetvalue(res, rowno, i_relname), sizeof(old_relname));
 299
 300                                 fprintf(script, "ALTER COLUMN %s "
 301                                 /* This could have been a custom conversion function call. */
 302                                                 "TYPE pg_catalog.tsvector USING %s::pg_catalog.text::pg_catalog.tsvector",
 303                                                 quote_identifier(PQgetvalue(res, rowno, i_attname)),
 304                                                 quote_identifier(PQgetvalue(res, rowno, i_attname)));
 305                         }
 306                 }
 307                 if (strlen(old_nspname) != 0 || strlen(old_relname) != 0)
 308                         fprintf(script, ";\n\n");
 309
 310                 PQclear(res);
 311
 312                 /* XXX Mark tables as not accessable somehow */
 313
 314                 PQfinish(conn);
 315         }
 316
 317         if (found)
 318         {
 319                 if (!check_mode)
 320                         fclose(script);
 321                 report_status(PG_WARNING, "warning");
 322                 if (check_mode)
 323                         pg_log(PG_WARNING, "\n"
 324                                    "| Your installation contains tsvector columns.\n"
 325                                    "| The tsvector internal storage format changed\n"
 326                                    "| between your old and new clusters so the tables\n"
 327                                    "| must be rebuilt.  After upgrading, you will be\n"
 328                                    "| given instructions.\n\n");
 329                 else
 330                         pg_log(PG_WARNING, "\n"
 331                                    "| Your installation contains tsvector columns.\n"
 332                                    "| The tsvector internal storage format changed\n"
 333                                    "| between your old and new clusters so the tables\n"
 334                                    "| must be rebuilt.  The file:\n"
 335                                    "| \t%s\n"
 336                                    "| when executed by psql by the database super-user\n"
 337                                    "| will rebuild all tables with tsvector columns.\n\n",
 338                                    output_path);
 339         }
 340         else
 341                 check_ok();
 342 }
 343
 344
 345 /*
 346  * old_8_3_invalidate_hash_gin_indexes()
 347  *      8.3 -> 8.4
 348  *      Hash, Gin, and GiST index binary format has changes from 8.3->8.4
 349  */
 350 void
 351 old_8_3_invalidate_hash_gin_indexes(ClusterInfo *cluster, bool check_mode)
 352 {
 353         int                     dbnum;
 354         FILE       *script = NULL;
 355         bool            found = false;
 356         char            output_path[MAXPGPATH];
 357
 358         prep_status("Checking for hash and gin indexes");
 359
 360         snprintf(output_path, sizeof(output_path), "%s/reindex_hash_and_gin.sql",
 361                          os_info.cwd);
 362
 363         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 364         {
 365                 PGresult   *res;
 366                 bool            db_used = false;
 367                 int                     ntups;
 368                 int                     rowno;
 369                 int                     i_nspname,
 370                                         i_relname;
 371                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 372                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 373
 374                 /* find hash and gin indexes */
 375                 res = executeQueryOrDie(conn,
 376                                                                 "SELECT n.nspname, c.relname "
 377                                                                 "FROM   pg_catalog.pg_class c, "
 378                                                                 "               pg_catalog.pg_index i, "
 379                                                                 "               pg_catalog.pg_am a, "
 380                                                                 "               pg_catalog.pg_namespace n "
 381                                                                 "WHERE  i.indexrelid = c.oid AND "
 382                                                                 "               c.relam = a.oid AND "
 383                                                                 "               c.relnamespace = n.oid AND "
 384                                                         "               a.amname IN ('hash', 'gin') AND "
 385                                                                 SKIP_TSVECTOR_TABLES);
 386
 387                 ntups = PQntuples(res);
 388                 i_nspname = PQfnumber(res, "nspname");
 389                 i_relname = PQfnumber(res, "relname");
 390                 for (rowno = 0; rowno < ntups; rowno++)
 391                 {
 392                         found = true;
 393                         if (!check_mode)
 394                         {
 395                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 396                                         pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 397                                 if (!db_used)
 398                                 {
 399                                         fprintf(script, "\\connect %s\n",
 400                                                         quote_identifier(active_db->db_name));
 401                                         db_used = true;
 402                                 }
 403                                 fprintf(script, "REINDEX INDEX %s.%s;\n",
 404                                                 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
 405                                                 quote_identifier(PQgetvalue(res, rowno, i_relname)));
 406                         }
 407                 }
 408
 409                 PQclear(res);
 410
 411                 if (!check_mode && found)
 412                         /* mark hash and gin indexes as invalid */
 413                         PQclear(executeQueryOrDie(conn,
 414                                                                           "UPDATE pg_catalog.pg_index i "
 415                                                                           "SET  indisvalid = false "
 416                                                                           "FROM         pg_catalog.pg_class c, "
 417                                                                           "             pg_catalog.pg_am a, "
 418                                                                           "             pg_catalog.pg_namespace n "
 419                                                                           "WHERE        i.indexrelid = c.oid AND "
 420                                                                           "             c.relam = a.oid AND "
 421                                                                           "             c.relnamespace = n.oid AND "
 422                                                                         "               a.amname IN ('hash', 'gin')"));
 423
 424                 PQfinish(conn);
 425         }
 426
 427         if (found)
 428         {
 429                 if (!check_mode)
 430                         fclose(script);
 431                 report_status(PG_WARNING, "warning");
 432                 if (check_mode)
 433                         pg_log(PG_WARNING, "\n"
 434                                    "| Your installation contains hash and/or gin\n"
 435                                    "| indexes.  These indexes have different\n"
 436                                    "| internal formats between your old and new\n"
 437                                    "| clusters so they must be reindexed with the\n"
 438                                    "| REINDEX command. After upgrading, you will\n"
 439                                    "| be given REINDEX instructions.\n\n");
 440                 else
 441                         pg_log(PG_WARNING, "\n"
 442                                    "| Your installation contains hash and/or gin\n"
 443                                    "| indexes.  These indexes have different internal\n"
 444                                    "| formats between your old and new clusters so\n"
 445                                    "| they must be reindexed with the REINDEX command.\n"
 446                                    "| The file:\n"
 447                                    "| \t%s\n"
 448                                    "| when executed by psql by the database super-user\n"
 449                                    "| will recreate all invalid indexes; until then,\n"
 450                                    "| none of these indexes will be used.\n\n",
 451                                    output_path);
 452         }
 453         else
 454                 check_ok();
 455 }
 456
 457
 458 /*
 459  * old_8_3_invalidate_bpchar_pattern_ops_indexes()
 460  *      8.3 -> 8.4
 461  *      8.4 bpchar_pattern_ops no longer sorts based on trailing spaces
 462  */
 463 void
 464 old_8_3_invalidate_bpchar_pattern_ops_indexes(ClusterInfo *cluster,
 465                                                                                           bool check_mode)
 466 {
 467         int                     dbnum;
 468         FILE       *script = NULL;
 469         bool            found = false;
 470         char            output_path[MAXPGPATH];
 471
 472         prep_status("Checking for bpchar_pattern_ops indexes");
 473
 474         snprintf(output_path, sizeof(output_path), "%s/reindex_bpchar_ops.sql",
 475                          os_info.cwd);
 476
 477         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 478         {
 479                 PGresult   *res;
 480                 bool            db_used = false;
 481                 int                     ntups;
 482                 int                     rowno;
 483                 int                     i_nspname,
 484                                         i_relname;
 485                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 486                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 487
 488                 /* find bpchar_pattern_ops indexes */
 489
 490                 /*
 491                  * Do only non-hash, non-gin indexees;  we already invalidated them
 492                  * above; no need to reindex twice
 493                  */
 494                 res = executeQueryOrDie(conn,
 495                                                                 "SELECT n.nspname, c.relname "
 496                                                                 "FROM   pg_catalog.pg_index i, "
 497                                                                 "               pg_catalog.pg_class c, "
 498                                                                 "               pg_catalog.pg_namespace n "
 499                                                                 "WHERE  indexrelid = c.oid AND "
 500                                                                 "               c.relnamespace = n.oid AND "
 501                                                                 "               ( "
 502                                                                 "                       SELECT  o.oid "
 503                                    "                    FROM    pg_catalog.pg_opclass o, "
 504                                   "                                     pg_catalog.pg_am a"
 505                 "                       WHERE   a.amname NOT IN ('hash', 'gin') AND "
 506                         "                                       a.oid = o.opcmethod AND "
 507                                                                 "                                       o.opcname = 'bpchar_pattern_ops') "
 508                                                                 "               = ANY (i.indclass) AND "
 509                                                                 SKIP_TSVECTOR_TABLES);
 510
 511                 ntups = PQntuples(res);
 512                 i_nspname = PQfnumber(res, "nspname");
 513                 i_relname = PQfnumber(res, "relname");
 514                 for (rowno = 0; rowno < ntups; rowno++)
 515                 {
 516                         found = true;
 517                         if (!check_mode)
 518                         {
 519                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 520                                         pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 521                                 if (!db_used)
 522                                 {
 523                                         fprintf(script, "\\connect %s\n",
 524                                                         quote_identifier(active_db->db_name));
 525                                         db_used = true;
 526                                 }
 527                                 fprintf(script, "REINDEX INDEX %s.%s;\n",
 528                                                 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
 529                                                 quote_identifier(PQgetvalue(res, rowno, i_relname)));
 530                         }
 531                 }
 532
 533                 PQclear(res);
 534
 535                 if (!check_mode && found)
 536                         /* mark bpchar_pattern_ops indexes as invalid */
 537                         PQclear(executeQueryOrDie(conn,
 538                                                                           "UPDATE pg_catalog.pg_index i "
 539                                                                           "SET  indisvalid = false "
 540                                                                           "FROM pg_catalog.pg_class c, "
 541                                                                           "             pg_catalog.pg_namespace n "
 542                                                                           "WHERE        indexrelid = c.oid AND "
 543                                                                           "             c.relnamespace = n.oid AND "
 544                                                                           "             ( "
 545                                                                           "                     SELECT  o.oid "
 546                                                  "                      FROM    pg_catalog.pg_opclass o, "
 547                                                 "                                       pg_catalog.pg_am a"
 548                           "                     WHERE   a.amname NOT IN ('hash', 'gin') AND "
 549                                   "                                     a.oid = o.opcmethod AND "
 550                                                                           "                                     o.opcname = 'bpchar_pattern_ops') "
 551                                                                           "             = ANY (i.indclass)"));
 552
 553                 PQfinish(conn);
 554         }
 555
 556         if (found)
 557         {
 558                 if (!check_mode)
 559                         fclose(script);
 560                 report_status(PG_WARNING, "warning");
 561                 if (check_mode)
 562                         pg_log(PG_WARNING, "\n"
 563                                    "| Your installation contains indexes using\n"
 564                                    "| \"bpchar_pattern_ops\".  These indexes have\n"
 565                                    "| different internal formats between your old and\n"
 566                                    "| new clusters so they must be reindexed with the\n"
 567                                    "| REINDEX command.  After upgrading, you will be\n"
 568                                    "| given REINDEX instructions.\n\n");
 569                 else
 570                         pg_log(PG_WARNING, "\n"
 571                                    "| Your installation contains indexes using\n"
 572                                    "| \"bpchar_pattern_ops\".  These indexes have\n"
 573                                    "| different internal formats between your old and\n"
 574                                    "| new clusters so they must be reindexed with the\n"
 575                                    "| REINDEX command.  The file:\n"
 576                                    "| \t%s\n"
 577                                    "| when executed by psql by the database super-user\n"
 578                                    "| will recreate all invalid indexes; until then,\n"
 579                                    "| none of these indexes will be used.\n\n",
 580                                    output_path);
 581         }
 582         else
 583                 check_ok();
 584 }
 585
 586
 587 /*
 588  * old_8_3_create_sequence_script()
 589  *      8.3 -> 8.4
 590  *      8.4 added the column "start_value" to all sequences.  For this reason,
 591  *      we don't transfer sequence files but instead use the CREATE SEQUENCE
 592  *      command from the schema dump, and use setval() to restore the sequence
 593  *      value and 'is_called' from the old database.  This is safe to run
 594  *      by pg_upgrade because sequence files are not transfered from the old
 595  *      server, even in link mode.
 596  */
 597 char *
 598 old_8_3_create_sequence_script(ClusterInfo *cluster)
 599 {
 600         int                     dbnum;
 601         FILE       *script = NULL;
 602         bool            found = false;
 603         char       *output_path = pg_malloc(MAXPGPATH);
 604
 605         snprintf(output_path, MAXPGPATH, "%s/adjust_sequences.sql", os_info.cwd);
 606
 607         prep_status("Creating script to adjust sequences");
 608
 609         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 610         {
 611                 PGresult   *res;
 612                 bool            db_used = false;
 613                 int                     ntups;
 614                 int                     rowno;
 615                 int                     i_nspname,
 616                                         i_relname;
 617                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 618                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 619
 620                 /* Find any sequences */
 621                 res = executeQueryOrDie(conn,
 622                                                                 "SELECT n.nspname, c.relname "
 623                                                                 "FROM   pg_catalog.pg_class c, "
 624                                                                 "               pg_catalog.pg_namespace n "
 625                                                                 "WHERE  c.relkind = 'S' AND "
 626                                                                 "               c.relnamespace = n.oid AND "
 627                                                           "             n.nspname != 'pg_catalog' AND "
 628                                                  "              n.nspname != 'information_schema'");
 629
 630                 ntups = PQntuples(res);
 631                 i_nspname = PQfnumber(res, "nspname");
 632                 i_relname = PQfnumber(res, "relname");
 633                 for (rowno = 0; rowno < ntups; rowno++)
 634                 {
 635                         PGresult   *seq_res;
 636                         int                     i_last_value,
 637                                                 i_is_called;
 638                         const char *nspname = PQgetvalue(res, rowno, i_nspname);
 639                         const char *relname = PQgetvalue(res, rowno, i_relname);
 640
 641                         found = true;
 642
 643                         if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 644                                 pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 645                         if (!db_used)
 646                         {
 647                                 fprintf(script, "\\connect %s\n\n",
 648                                                 quote_identifier(active_db->db_name));
 649                                 db_used = true;
 650                         }
 651
 652                         /* Find the desired sequence */
 653                         seq_res = executeQueryOrDie(conn,
 654                                                                                 "SELECT s.last_value, s.is_called "
 655                                                                                 "FROM   %s.%s s",
 656                                                                                 quote_identifier(nspname),
 657                                                                                 quote_identifier(relname));
 658
 659                         assert(PQntuples(seq_res) == 1);
 660                         i_last_value = PQfnumber(seq_res, "last_value");
 661                         i_is_called = PQfnumber(seq_res, "is_called");
 662
 663                         fprintf(script, "SELECT setval('%s.%s', %s, '%s');\n",
 664                                         quote_identifier(nspname), quote_identifier(relname),
 665                                         PQgetvalue(seq_res, 0, i_last_value), PQgetvalue(seq_res, 0, i_is_called));
 666                         PQclear(seq_res);
 667                 }
 668                 if (db_used)
 669                         fprintf(script, "\n");
 670
 671                 PQclear(res);
 672
 673                 PQfinish(conn);
 674         }
 675         if (found)
 676                 fclose(script);
 677
 678         check_ok();
 679
 680         if (found)
 681                 return output_path;
 682         else
 683         {
 684                 pg_free(output_path);
 685                 return NULL;
 686         }
 687 }