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-2011, 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 (script)
  91                 fclose(script);
  92
  93         if (found)
  94         {
  95                 pg_log(PG_REPORT, "fatal\n");
  96                 pg_log(PG_FATAL,
  97                            "| Your installation contains the \"name\" data type in\n"
  98                            "| user tables.  This data type changed its internal\n"
  99                            "| alignment between your old and new clusters so this\n"
 100                            "| cluster cannot currently be upgraded.  You can\n"
 101                            "| remove the problem tables and restart the upgrade.\n"
 102                            "| A list of the problem columns is in the file:\n"
 103                            "| \t%s\n\n", output_path);
 104         }
 105         else
 106                 check_ok();
 107 }
 108
 109
 110 /*
 111  * old_8_3_check_for_tsquery_usage()
 112  *      8.3 -> 8.4
 113  *      A new 'prefix' field was added to the 'tsquery' data type in 8.4
 114  *      so upgrading of such fields is impossible.
 115  */
 116 void
 117 old_8_3_check_for_tsquery_usage(ClusterInfo *cluster)
 118 {
 119         int                     dbnum;
 120         FILE       *script = NULL;
 121         bool            found = false;
 122         char            output_path[MAXPGPATH];
 123
 124         prep_status("Checking for tsquery user columns");
 125
 126         snprintf(output_path, sizeof(output_path), "%s/tables_using_tsquery.txt",
 127                          os_info.cwd);
 128
 129         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 130         {
 131                 PGresult   *res;
 132                 bool            db_used = false;
 133                 int                     ntups;
 134                 int                     rowno;
 135                 int                     i_nspname,
 136                                         i_relname,
 137                                         i_attname;
 138                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 139                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 140
 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'");
 154
 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++)
 160                 {
 161                         found = true;
 162                         if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 163                                 pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 164                         if (!db_used)
 165                         {
 166                                 fprintf(script, "Database:  %s\n", active_db->db_name);
 167                                 db_used = true;
 168                         }
 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));
 173                 }
 174
 175                 PQclear(res);
 176
 177                 PQfinish(conn);
 178         }
 179
 180         if (script)
 181                 fclose(script);
 182
 183         if (found)
 184         {
 185                 pg_log(PG_REPORT, "fatal\n");
 186                 pg_log(PG_FATAL,
 187                            "| Your installation contains the \"tsquery\" data type.\n"
 188                            "| This data type added a new internal field between\n"
 189                            "| your old and new clusters so this cluster cannot\n"
 190                            "| currently be upgraded.  You can remove the problem\n"
 191                            "| columns and restart the upgrade.  A list of the\n"
 192                            "| problem columns is in the file:\n"
 193                            "| \t%s\n\n", output_path);
 194         }
 195         else
 196                 check_ok();
 197 }
 198
 199
 200 /*
 201  * old_8_3_rebuild_tsvector_tables()
 202  *      8.3 -> 8.4
 203  * 8.3 sorts lexemes by its length and if lengths are the same then it uses
 204  * alphabetic order;  8.4 sorts lexemes in lexicographical order, e.g.
 205  *
 206  * => SELECT 'c bb aaa'::tsvector;
 207  *         tsvector
 208  * ----------------
 209  *      'aaa' 'bb' 'c'             -- 8.4
 210  *      'c' 'bb' 'aaa'             -- 8.3
 211  */
 212 void
 213 old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode)
 214 {
 215         int                     dbnum;
 216         FILE       *script = NULL;
 217         bool            found = false;
 218         char            output_path[MAXPGPATH];
 219
 220         prep_status("Checking for tsvector user columns");
 221
 222         snprintf(output_path, sizeof(output_path), "%s/rebuild_tsvector_tables.sql",
 223                          os_info.cwd);
 224
 225         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 226         {
 227                 PGresult   *res;
 228                 bool            db_used = false;
 229                 char            nspname[NAMEDATALEN] = "",
 230                                         relname[NAMEDATALEN] = "";
 231                 int                     ntups;
 232                 int                     rowno;
 233                 int                     i_nspname,
 234                                         i_relname,
 235                                         i_attname;
 236                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 237                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 238
 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'");
 252
 253 /*
 254  *      This macro is used below to avoid reindexing indexes already rebuilt
 255  *      because of tsvector columns.
 256  */
 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') "
 270
 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++)
 276                 {
 277                         found = true;
 278                         if (!check_mode)
 279                         {
 280                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 281                                         pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 282                                 if (!db_used)
 283                                 {
 284                                         fprintf(script, "\\connect %s\n\n",
 285                                                         quote_identifier(active_db->db_name));
 286                                         db_used = true;
 287                                 }
 288
 289                                 /* Rebuild all tsvector collumns with one ALTER TABLE command */
 290                                 if (strcmp(PQgetvalue(res, rowno, i_nspname), nspname) != 0 ||
 291                                         strcmp(PQgetvalue(res, rowno, i_relname), relname) != 0)
 292                                 {
 293                                         if (strlen(nspname) != 0 || strlen(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)));
 298                                 }
 299                                 else
 300                                         fprintf(script, ",\n");
 301                                 strlcpy(nspname, PQgetvalue(res, rowno, i_nspname), sizeof(nspname));
 302                                 strlcpy(relname, PQgetvalue(res, rowno, i_relname), sizeof(relname));
 303
 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)));
 309                         }
 310                 }
 311                 if (strlen(nspname) != 0 || strlen(relname) != 0)
 312                         fprintf(script, ";\n\n");
 313
 314                 PQclear(res);
 315
 316                 /* XXX Mark tables as not accessable somehow */
 317
 318                 PQfinish(conn);
 319         }
 320
 321         if (script)
 322                 fclose(script);
 323
 324         if (found)
 325         {
 326                 report_status(PG_WARNING, "warning");
 327                 if (check_mode)
 328                         pg_log(PG_WARNING, "\n"
 329                                    "| Your installation contains tsvector columns.\n"
 330                                    "| The tsvector internal storage format changed\n"
 331                                    "| between your old and new clusters so the tables\n"
 332                                    "| must be rebuilt.  After upgrading, you will be\n"
 333                                    "| given instructions.\n\n");
 334                 else
 335                         pg_log(PG_WARNING, "\n"
 336                                    "| Your installation contains tsvector columns.\n"
 337                                    "| The tsvector internal storage format changed\n"
 338                                    "| between your old and new clusters so the tables\n"
 339                                    "| must be rebuilt.  The file:\n"
 340                                    "| \t%s\n"
 341                                    "| when executed by psql by the database super-user\n"
 342                                    "| will rebuild all tables with tsvector columns.\n\n",
 343                                    output_path);
 344         }
 345         else
 346                 check_ok();
 347 }
 348
 349
 350 /*
 351  * old_8_3_invalidate_hash_gin_indexes()
 352  *      8.3 -> 8.4
 353  *      Hash, Gin, and GiST index binary format has changes from 8.3->8.4
 354  */
 355 void
 356 old_8_3_invalidate_hash_gin_indexes(ClusterInfo *cluster, bool check_mode)
 357 {
 358         int                     dbnum;
 359         FILE       *script = NULL;
 360         bool            found = false;
 361         char            output_path[MAXPGPATH];
 362
 363         prep_status("Checking for hash and gin indexes");
 364
 365         snprintf(output_path, sizeof(output_path), "%s/reindex_hash_and_gin.sql",
 366                          os_info.cwd);
 367
 368         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 369         {
 370                 PGresult   *res;
 371                 bool            db_used = false;
 372                 int                     ntups;
 373                 int                     rowno;
 374                 int                     i_nspname,
 375                                         i_relname;
 376                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 377                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 378
 379                 /* find hash and gin indexes */
 380                 res = executeQueryOrDie(conn,
 381                                                                 "SELECT n.nspname, c.relname "
 382                                                                 "FROM   pg_catalog.pg_class c, "
 383                                                                 "               pg_catalog.pg_index i, "
 384                                                                 "               pg_catalog.pg_am a, "
 385                                                                 "               pg_catalog.pg_namespace n "
 386                                                                 "WHERE  i.indexrelid = c.oid AND "
 387                                                                 "               c.relam = a.oid AND "
 388                                                                 "               c.relnamespace = n.oid AND "
 389                                                         "               a.amname IN ('hash', 'gin') AND "
 390                                                                 SKIP_TSVECTOR_TABLES);
 391
 392                 ntups = PQntuples(res);
 393                 i_nspname = PQfnumber(res, "nspname");
 394                 i_relname = PQfnumber(res, "relname");
 395                 for (rowno = 0; rowno < ntups; rowno++)
 396                 {
 397                         found = true;
 398                         if (!check_mode)
 399                         {
 400                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 401                                         pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 402                                 if (!db_used)
 403                                 {
 404                                         fprintf(script, "\\connect %s\n",
 405                                                         quote_identifier(active_db->db_name));
 406                                         db_used = true;
 407                                 }
 408                                 fprintf(script, "REINDEX INDEX %s.%s;\n",
 409                                                 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
 410                                                 quote_identifier(PQgetvalue(res, rowno, i_relname)));
 411                         }
 412                 }
 413
 414                 PQclear(res);
 415
 416                 if (!check_mode && found)
 417                         /* mark hash and gin indexes as invalid */
 418                         PQclear(executeQueryOrDie(conn,
 419                                                                           "UPDATE pg_catalog.pg_index i "
 420                                                                           "SET  indisvalid = false "
 421                                                                           "FROM         pg_catalog.pg_class c, "
 422                                                                           "             pg_catalog.pg_am a, "
 423                                                                           "             pg_catalog.pg_namespace n "
 424                                                                           "WHERE        i.indexrelid = c.oid AND "
 425                                                                           "             c.relam = a.oid AND "
 426                                                                           "             c.relnamespace = n.oid AND "
 427                                                                         "               a.amname IN ('hash', 'gin')"));
 428
 429                 PQfinish(conn);
 430         }
 431
 432         if (script)
 433                 fclose(script);
 434
 435         if (found)
 436         {
 437                 report_status(PG_WARNING, "warning");
 438                 if (check_mode)
 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 upgrading, you will\n"
 445                                    "| be given REINDEX instructions.\n\n");
 446                 else
 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"
 452                                    "| The file:\n"
 453                                    "| \t%s\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",
 457                                    output_path);
 458         }
 459         else
 460                 check_ok();
 461 }
 462
 463
 464 /*
 465  * old_8_3_invalidate_bpchar_pattern_ops_indexes()
 466  *      8.3 -> 8.4
 467  *      8.4 bpchar_pattern_ops no longer sorts based on trailing spaces
 468  */
 469 void
 470 old_8_3_invalidate_bpchar_pattern_ops_indexes(ClusterInfo *cluster,
 471                                                                                           bool check_mode)
 472 {
 473         int                     dbnum;
 474         FILE       *script = NULL;
 475         bool            found = false;
 476         char            output_path[MAXPGPATH];
 477
 478         prep_status("Checking for bpchar_pattern_ops indexes");
 479
 480         snprintf(output_path, sizeof(output_path), "%s/reindex_bpchar_ops.sql",
 481                          os_info.cwd);
 482
 483         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 484         {
 485                 PGresult   *res;
 486                 bool            db_used = false;
 487                 int                     ntups;
 488                 int                     rowno;
 489                 int                     i_nspname,
 490                                         i_relname;
 491                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 492                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 493
 494                 /* find bpchar_pattern_ops indexes */
 495
 496                 /*
 497                  * Do only non-hash, non-gin indexees;  we already invalidated them
 498                  * above; no need to reindex twice
 499                  */
 500                 res = executeQueryOrDie(conn,
 501                                                                 "SELECT n.nspname, c.relname "
 502                                                                 "FROM   pg_catalog.pg_index i, "
 503                                                                 "               pg_catalog.pg_class c, "
 504                                                                 "               pg_catalog.pg_namespace n "
 505                                                                 "WHERE  indexrelid = c.oid AND "
 506                                                                 "               c.relnamespace = n.oid AND "
 507                                                                 "               ( "
 508                                                                 "                       SELECT  o.oid "
 509                                    "                    FROM    pg_catalog.pg_opclass o, "
 510                                   "                                     pg_catalog.pg_am a"
 511                 "                       WHERE   a.amname NOT IN ('hash', 'gin') AND "
 512                         "                                       a.oid = o.opcmethod AND "
 513                                                                 "                                       o.opcname = 'bpchar_pattern_ops') "
 514                                                                 "               = ANY (i.indclass) AND "
 515                                                                 SKIP_TSVECTOR_TABLES);
 516
 517                 ntups = PQntuples(res);
 518                 i_nspname = PQfnumber(res, "nspname");
 519                 i_relname = PQfnumber(res, "relname");
 520                 for (rowno = 0; rowno < ntups; rowno++)
 521                 {
 522                         found = true;
 523                         if (!check_mode)
 524                         {
 525                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 526                                         pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 527                                 if (!db_used)
 528                                 {
 529                                         fprintf(script, "\\connect %s\n",
 530                                                         quote_identifier(active_db->db_name));
 531                                         db_used = true;
 532                                 }
 533                                 fprintf(script, "REINDEX INDEX %s.%s;\n",
 534                                                 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
 535                                                 quote_identifier(PQgetvalue(res, rowno, i_relname)));
 536                         }
 537                 }
 538
 539                 PQclear(res);
 540
 541                 if (!check_mode && found)
 542                         /* mark bpchar_pattern_ops indexes as invalid */
 543                         PQclear(executeQueryOrDie(conn,
 544                                                                           "UPDATE pg_catalog.pg_index i "
 545                                                                           "SET  indisvalid = false "
 546                                                                           "FROM pg_catalog.pg_class c, "
 547                                                                           "             pg_catalog.pg_namespace n "
 548                                                                           "WHERE        indexrelid = c.oid AND "
 549                                                                           "             c.relnamespace = n.oid AND "
 550                                                                           "             ( "
 551                                                                           "                     SELECT  o.oid "
 552                                                  "                      FROM    pg_catalog.pg_opclass o, "
 553                                                 "                                       pg_catalog.pg_am a"
 554                           "                     WHERE   a.amname NOT IN ('hash', 'gin') AND "
 555                                   "                                     a.oid = o.opcmethod AND "
 556                                                                           "                                     o.opcname = 'bpchar_pattern_ops') "
 557                                                                           "             = ANY (i.indclass)"));
 558
 559                 PQfinish(conn);
 560         }
 561
 562         if (script)
 563                 fclose(script);
 564
 565         if (found)
 566         {
 567                 report_status(PG_WARNING, "warning");
 568                 if (check_mode)
 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 upgrading, you will be\n"
 575                                    "| given REINDEX instructions.\n\n");
 576                 else
 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"
 583                                    "| \t%s\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",
 587                                    output_path);
 588         }
 589         else
 590                 check_ok();
 591 }
 592
 593
 594 /*
 595  * old_8_3_create_sequence_script()
 596  *      8.3 -> 8.4
 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.
 603  */
 604 char *
 605 old_8_3_create_sequence_script(ClusterInfo *cluster)
 606 {
 607         int                     dbnum;
 608         FILE       *script = NULL;
 609         bool            found = false;
 610         char       *output_path = pg_malloc(MAXPGPATH);
 611
 612         snprintf(output_path, MAXPGPATH, "%s/adjust_sequences.sql", os_info.cwd);
 613
 614         prep_status("Creating script to adjust sequences");
 615
 616         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 617         {
 618                 PGresult   *res;
 619                 bool            db_used = false;
 620                 int                     ntups;
 621                 int                     rowno;
 622                 int                     i_nspname,
 623                                         i_relname;
 624                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 625                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 626
 627                 /* Find any sequences */
 628                 res = executeQueryOrDie(conn,
 629                                                                 "SELECT n.nspname, c.relname "
 630                                                                 "FROM   pg_catalog.pg_class c, "
 631                                                                 "               pg_catalog.pg_namespace n "
 632                                                                 "WHERE  c.relkind = 'S' AND "
 633                                                                 "               c.relnamespace = n.oid AND "
 634                                                           "             n.nspname != 'pg_catalog' AND "
 635                                                  "              n.nspname != 'information_schema'");
 636
 637                 ntups = PQntuples(res);
 638                 i_nspname = PQfnumber(res, "nspname");
 639                 i_relname = PQfnumber(res, "relname");
 640                 for (rowno = 0; rowno < ntups; rowno++)
 641                 {
 642                         PGresult   *seq_res;
 643                         int                     i_last_value,
 644                                                 i_is_called;
 645                         const char *nspname = PQgetvalue(res, rowno, i_nspname);
 646                         const char *relname = PQgetvalue(res, rowno, i_relname);
 647
 648                         found = true;
 649
 650                         if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 651                                 pg_log(PG_FATAL, "Could not create necessary file:  %s\n", output_path);
 652                         if (!db_used)
 653                         {
 654                                 fprintf(script, "\\connect %s\n\n",
 655                                                 quote_identifier(active_db->db_name));
 656                                 db_used = true;
 657                         }
 658
 659                         /* Find the desired sequence */
 660                         seq_res = executeQueryOrDie(conn,
 661                                                                                 "SELECT s.last_value, s.is_called "
 662                                                                                 "FROM   %s.%s s",
 663                                                                                 quote_identifier(nspname),
 664                                                                                 quote_identifier(relname));
 665
 666                         assert(PQntuples(seq_res) == 1);
 667                         i_last_value = PQfnumber(seq_res, "last_value");
 668                         i_is_called = PQfnumber(seq_res, "is_called");
 669
 670                         fprintf(script, "SELECT setval('%s.%s', %s, '%s');\n",
 671                                         quote_identifier(nspname), quote_identifier(relname),
 672                                         PQgetvalue(seq_res, 0, i_last_value), PQgetvalue(seq_res, 0, i_is_called));
 673                         PQclear(seq_res);
 674                 }
 675                 if (db_used)
 676                         fprintf(script, "\n");
 677
 678                 PQclear(res);
 679
 680                 PQfinish(conn);
 681         }
 682
 683         if (script)
 684                 fclose(script);
 685
 686         check_ok();
 687
 688         if (found)
 689                 return output_path;
 690         else
 691         {
 692                 pg_free(output_path);
 693                 return NULL;
 694         }
 695 }