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 open file \"%s\": %s\n", output_path, getErrorText(errno));
  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 user tables.  This\n"
  98                            "data type changed its internal alignment between your old and new\n"
  99                            "clusters so this cluster cannot currently be upgraded.  You can remove\n"
 100                            "the problem tables and restart the upgrade.  A list of the problem\n"
 101                            "columns is in the file:\n"
 102                            "    %s\n\n", output_path);
 103         }
 104         else
 105                 check_ok();
 106 }
 107
 108
 109 /*
 110  * old_8_3_check_for_tsquery_usage()
 111  *      8.3 -> 8.4
 112  *      A new 'prefix' field was added to the 'tsquery' data type in 8.4
 113  *      so upgrading of such fields is impossible.
 114  */
 115 void
 116 old_8_3_check_for_tsquery_usage(ClusterInfo *cluster)
 117 {
 118         int                     dbnum;
 119         FILE       *script = NULL;
 120         bool            found = false;
 121         char            output_path[MAXPGPATH];
 122
 123         prep_status("Checking for tsquery user columns");
 124
 125         snprintf(output_path, sizeof(output_path), "%s/tables_using_tsquery.txt",
 126                          os_info.cwd);
 127
 128         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 129         {
 130                 PGresult   *res;
 131                 bool            db_used = false;
 132                 int                     ntups;
 133                 int                     rowno;
 134                 int                     i_nspname,
 135                                         i_relname,
 136                                         i_attname;
 137                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 138                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 139
 140                 /* Find any user-defined tsquery columns */
 141                 res = executeQueryOrDie(conn,
 142                                                                 "SELECT n.nspname, c.relname, a.attname "
 143                                                                 "FROM   pg_catalog.pg_class c, "
 144                                                                 "               pg_catalog.pg_namespace n, "
 145                                                                 "               pg_catalog.pg_attribute a "
 146                                                                 "WHERE  c.relkind = 'r' AND "
 147                                                                 "               c.oid = a.attrelid AND "
 148                                                                 "               NOT a.attisdropped AND "
 149                                                                 "               a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
 150                                                                 "               c.relnamespace = n.oid AND "
 151                                                           "             n.nspname != 'pg_catalog' AND "
 152                                                  "              n.nspname != 'information_schema'");
 153
 154                 ntups = PQntuples(res);
 155                 i_nspname = PQfnumber(res, "nspname");
 156                 i_relname = PQfnumber(res, "relname");
 157                 i_attname = PQfnumber(res, "attname");
 158                 for (rowno = 0; rowno < ntups; rowno++)
 159                 {
 160                         found = true;
 161                         if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 162                                 pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
 163                         if (!db_used)
 164                         {
 165                                 fprintf(script, "Database: %s\n", active_db->db_name);
 166                                 db_used = true;
 167                         }
 168                         fprintf(script, "  %s.%s.%s\n",
 169                                         PQgetvalue(res, rowno, i_nspname),
 170                                         PQgetvalue(res, rowno, i_relname),
 171                                         PQgetvalue(res, rowno, i_attname));
 172                 }
 173
 174                 PQclear(res);
 175
 176                 PQfinish(conn);
 177         }
 178
 179         if (script)
 180                 fclose(script);
 181
 182         if (found)
 183         {
 184                 pg_log(PG_REPORT, "fatal\n");
 185                 pg_log(PG_FATAL,
 186                            "Your installation contains the \"tsquery\" data type.    This data type\n"
 187                            "added a new internal field between your old and new clusters so this\n"
 188                            "cluster cannot currently be upgraded.  You can remove the problem\n"
 189                            "columns and restart the upgrade.  A list of the problem columns is in the\n"
 190                            "file:\n"
 191                            "    %s\n\n", output_path);
 192         }
 193         else
 194                 check_ok();
 195 }
 196
 197
 198 /*
 199  * old_8_3_rebuild_tsvector_tables()
 200  *      8.3 -> 8.4
 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.
 203  *
 204  * => SELECT 'c bb aaa'::tsvector;
 205  *         tsvector
 206  * ----------------
 207  *      'aaa' 'bb' 'c'             -- 8.4
 208  *      'c' 'bb' 'aaa'             -- 8.3
 209  */
 210 void
 211 old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode)
 212 {
 213         int                     dbnum;
 214         FILE       *script = NULL;
 215         bool            found = false;
 216         char            output_path[MAXPGPATH];
 217
 218         prep_status("Checking for tsvector user columns");
 219
 220         snprintf(output_path, sizeof(output_path), "%s/rebuild_tsvector_tables.sql",
 221                          os_info.cwd);
 222
 223         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 224         {
 225                 PGresult   *res;
 226                 bool            db_used = false;
 227                 char            nspname[NAMEDATALEN] = "",
 228                                         relname[NAMEDATALEN] = "";
 229                 int                     ntups;
 230                 int                     rowno;
 231                 int                     i_nspname,
 232                                         i_relname,
 233                                         i_attname;
 234                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 235                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 236
 237                 /* Find any user-defined tsvector columns */
 238                 res = executeQueryOrDie(conn,
 239                                                                 "SELECT n.nspname, c.relname, a.attname "
 240                                                                 "FROM   pg_catalog.pg_class c, "
 241                                                                 "               pg_catalog.pg_namespace n, "
 242                                                                 "               pg_catalog.pg_attribute a "
 243                                                                 "WHERE  c.relkind = 'r' AND "
 244                                                                 "               c.oid = a.attrelid AND "
 245                                                                 "               NOT a.attisdropped AND "
 246                                                                 "               a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
 247                                                                 "               c.relnamespace = n.oid AND "
 248                                                           "             n.nspname != 'pg_catalog' AND "
 249                                                  "              n.nspname != 'information_schema'");
 250
 251 /*
 252  *      This macro is used below to avoid reindexing indexes already rebuilt
 253  *      because of tsvector columns.
 254  */
 255 #define SKIP_TSVECTOR_TABLES \
 256                                                                 "i.indrelid NOT IN ( "                                  \
 257                                                                 "SELECT DISTINCT c.oid "                                \
 258                                                                 "FROM   pg_catalog.pg_class c, "                \
 259                                                                 "               pg_catalog.pg_namespace n, "    \
 260                                                                 "               pg_catalog.pg_attribute a "             \
 261                                                                 "WHERE  c.relkind = 'r' AND "                   \
 262                                                                 "               c.oid = a.attrelid AND "                \
 263                                                                 "               NOT a.attisdropped AND "                \
 264                                                                 "               a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
 265                                                                 "               c.relnamespace = n.oid AND "    \
 266                                                                 "               n.nspname != 'pg_catalog' AND " \
 267                                                                 "               n.nspname != 'information_schema') "
 268
 269                 ntups = PQntuples(res);
 270                 i_nspname = PQfnumber(res, "nspname");
 271                 i_relname = PQfnumber(res, "relname");
 272                 i_attname = PQfnumber(res, "attname");
 273                 for (rowno = 0; rowno < ntups; rowno++)
 274                 {
 275                         found = true;
 276                         if (!check_mode)
 277                         {
 278                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 279                                         pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
 280                                 if (!db_used)
 281                                 {
 282                                         fprintf(script, "\\connect %s\n\n",
 283                                                         quote_identifier(active_db->db_name));
 284                                         db_used = true;
 285                                 }
 286
 287                                 /* Rebuild all tsvector collumns with one ALTER TABLE command */
 288                                 if (strcmp(PQgetvalue(res, rowno, i_nspname), nspname) != 0 ||
 289                                         strcmp(PQgetvalue(res, rowno, i_relname), relname) != 0)
 290                                 {
 291                                         if (strlen(nspname) != 0 || strlen(relname) != 0)
 292                                                 fprintf(script, ";\n\n");
 293                                         fprintf(script, "ALTER TABLE %s.%s\n",
 294                                                  quote_identifier(PQgetvalue(res, rowno, i_nspname)),
 295                                                 quote_identifier(PQgetvalue(res, rowno, i_relname)));
 296                                 }
 297                                 else
 298                                         fprintf(script, ",\n");
 299                                 strlcpy(nspname, PQgetvalue(res, rowno, i_nspname), sizeof(nspname));
 300                                 strlcpy(relname, PQgetvalue(res, rowno, i_relname), sizeof(relname));
 301
 302                                 fprintf(script, "ALTER COLUMN %s "
 303                                 /* This could have been a custom conversion function call. */
 304                                                 "TYPE pg_catalog.tsvector USING %s::pg_catalog.text::pg_catalog.tsvector",
 305                                                 quote_identifier(PQgetvalue(res, rowno, i_attname)),
 306                                                 quote_identifier(PQgetvalue(res, rowno, i_attname)));
 307                         }
 308                 }
 309                 if (strlen(nspname) != 0 || strlen(relname) != 0)
 310                         fprintf(script, ";\n\n");
 311
 312                 PQclear(res);
 313
 314                 /* XXX Mark tables as not accessable somehow */
 315
 316                 PQfinish(conn);
 317         }
 318
 319         if (script)
 320                 fclose(script);
 321
 322         if (found)
 323         {
 324                 report_status(PG_WARNING, "warning");
 325                 if (check_mode)
 326                         pg_log(PG_WARNING, "\n"
 327                                    "Your installation contains tsvector columns.  The tsvector internal\n"
 328                                    "storage format changed between your old and new clusters so the tables\n"
 329                                    "must be rebuilt.  After upgrading, you will be given instructions.\n\n");
 330                 else
 331                         pg_log(PG_WARNING, "\n"
 332                                    "Your installation contains tsvector columns.  The tsvector internal\n"
 333                                    "storage format changed between your old and new clusters so the tables\n"
 334                                    "must be rebuilt.  The file:\n"
 335                                    "    %s\n"
 336                                    "when executed by psql by the database superuser will rebuild all tables\n"
 337                                    "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 open file \"%s\": %s\n", output_path, getErrorText(errno));
 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 (script)
 428                 fclose(script);
 429
 430         if (found)
 431         {
 432                 report_status(PG_WARNING, "warning");
 433                 if (check_mode)
 434                         pg_log(PG_WARNING, "\n"
 435                                    "Your installation contains hash and/or GIN indexes.  These indexes have\n"
 436                                    "different internal formats between your old and new clusters, so they\n"
 437                                    "must be reindexed with the REINDEX command.  After upgrading, you will\n"
 438                                    "be given REINDEX instructions.\n\n");
 439                 else
 440                         pg_log(PG_WARNING, "\n"
 441                                    "Your installation contains hash and/or GIN indexes.  These indexes have\n"
 442                                    "different internal formats between your old and new clusters, so they\n"
 443                                    "must be reindexed with the REINDEX command.  The file:\n"
 444                                    "    %s\n"
 445                                    "when executed by psql by the database superuser will recreate all invalid\n"
 446                                    "indexes; until then, none of these indexes will be used.\n\n",
 447                                    output_path);
 448         }
 449         else
 450                 check_ok();
 451 }
 452
 453
 454 /*
 455  * old_8_3_invalidate_bpchar_pattern_ops_indexes()
 456  *      8.3 -> 8.4
 457  *      8.4 bpchar_pattern_ops no longer sorts based on trailing spaces
 458  */
 459 void
 460 old_8_3_invalidate_bpchar_pattern_ops_indexes(ClusterInfo *cluster,
 461                                                                                           bool check_mode)
 462 {
 463         int                     dbnum;
 464         FILE       *script = NULL;
 465         bool            found = false;
 466         char            output_path[MAXPGPATH];
 467
 468         prep_status("Checking for bpchar_pattern_ops indexes");
 469
 470         snprintf(output_path, sizeof(output_path), "%s/reindex_bpchar_ops.sql",
 471                          os_info.cwd);
 472
 473         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 474         {
 475                 PGresult   *res;
 476                 bool            db_used = false;
 477                 int                     ntups;
 478                 int                     rowno;
 479                 int                     i_nspname,
 480                                         i_relname;
 481                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 482                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 483
 484                 /* find bpchar_pattern_ops indexes */
 485
 486                 /*
 487                  * Do only non-hash, non-gin indexees;  we already invalidated them
 488                  * above; no need to reindex twice
 489                  */
 490                 res = executeQueryOrDie(conn,
 491                                                                 "SELECT n.nspname, c.relname "
 492                                                                 "FROM   pg_catalog.pg_index i, "
 493                                                                 "               pg_catalog.pg_class c, "
 494                                                                 "               pg_catalog.pg_namespace n "
 495                                                                 "WHERE  indexrelid = c.oid AND "
 496                                                                 "               c.relnamespace = n.oid AND "
 497                                                                 "               ( "
 498                                                                 "                       SELECT  o.oid "
 499                                    "                    FROM    pg_catalog.pg_opclass o, "
 500                                   "                                     pg_catalog.pg_am a"
 501                 "                       WHERE   a.amname NOT IN ('hash', 'gin') AND "
 502                         "                                       a.oid = o.opcmethod AND "
 503                                                                 "                                       o.opcname = 'bpchar_pattern_ops') "
 504                                                                 "               = ANY (i.indclass) AND "
 505                                                                 SKIP_TSVECTOR_TABLES);
 506
 507                 ntups = PQntuples(res);
 508                 i_nspname = PQfnumber(res, "nspname");
 509                 i_relname = PQfnumber(res, "relname");
 510                 for (rowno = 0; rowno < ntups; rowno++)
 511                 {
 512                         found = true;
 513                         if (!check_mode)
 514                         {
 515                                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 516                                         pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
 517                                 if (!db_used)
 518                                 {
 519                                         fprintf(script, "\\connect %s\n",
 520                                                         quote_identifier(active_db->db_name));
 521                                         db_used = true;
 522                                 }
 523                                 fprintf(script, "REINDEX INDEX %s.%s;\n",
 524                                                 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
 525                                                 quote_identifier(PQgetvalue(res, rowno, i_relname)));
 526                         }
 527                 }
 528
 529                 PQclear(res);
 530
 531                 if (!check_mode && found)
 532                         /* mark bpchar_pattern_ops indexes as invalid */
 533                         PQclear(executeQueryOrDie(conn,
 534                                                                           "UPDATE pg_catalog.pg_index i "
 535                                                                           "SET  indisvalid = false "
 536                                                                           "FROM pg_catalog.pg_class c, "
 537                                                                           "             pg_catalog.pg_namespace n "
 538                                                                           "WHERE        indexrelid = c.oid AND "
 539                                                                           "             c.relnamespace = n.oid AND "
 540                                                                           "             ( "
 541                                                                           "                     SELECT  o.oid "
 542                                                  "                      FROM    pg_catalog.pg_opclass o, "
 543                                                 "                                       pg_catalog.pg_am a"
 544                           "                     WHERE   a.amname NOT IN ('hash', 'gin') AND "
 545                                   "                                     a.oid = o.opcmethod AND "
 546                                                                           "                                     o.opcname = 'bpchar_pattern_ops') "
 547                                                                           "             = ANY (i.indclass)"));
 548
 549                 PQfinish(conn);
 550         }
 551
 552         if (script)
 553                 fclose(script);
 554
 555         if (found)
 556         {
 557                 report_status(PG_WARNING, "warning");
 558                 if (check_mode)
 559                         pg_log(PG_WARNING, "\n"
 560                                    "Your installation contains indexes using \"bpchar_pattern_ops\".  These\n"
 561                                    "indexes have different internal formats between your old and new clusters\n"
 562                                    "so they must be reindexed with the REINDEX command.  After upgrading, you\n"
 563                                    "will be given REINDEX instructions.\n\n");
 564                 else
 565                         pg_log(PG_WARNING, "\n"
 566                                    "Your installation contains indexes using \"bpchar_pattern_ops\".  These\n"
 567                                    "indexes have different internal formats between your old and new clusters\n"
 568                                    "so they must be reindexed with the REINDEX command.  The file:\n"
 569                                    "    %s\n"
 570                                    "when executed by psql by the database superuser will recreate all invalid\n"
 571                                    "indexes; until then, none of these indexes will be used.\n\n",
 572                                    output_path);
 573         }
 574         else
 575                 check_ok();
 576 }
 577
 578
 579 /*
 580  * old_8_3_create_sequence_script()
 581  *      8.3 -> 8.4
 582  *      8.4 added the column "start_value" to all sequences.  For this reason,
 583  *      we don't transfer sequence files but instead use the CREATE SEQUENCE
 584  *      command from the schema dump, and use setval() to restore the sequence
 585  *      value and 'is_called' from the old database.  This is safe to run
 586  *      by pg_upgrade because sequence files are not transfered from the old
 587  *      server, even in link mode.
 588  */
 589 char *
 590 old_8_3_create_sequence_script(ClusterInfo *cluster)
 591 {
 592         int                     dbnum;
 593         FILE       *script = NULL;
 594         bool            found = false;
 595         char       *output_path = pg_malloc(MAXPGPATH);
 596
 597         snprintf(output_path, MAXPGPATH, "%s/adjust_sequences.sql", os_info.cwd);
 598
 599         prep_status("Creating script to adjust sequences");
 600
 601         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 602         {
 603                 PGresult   *res;
 604                 bool            db_used = false;
 605                 int                     ntups;
 606                 int                     rowno;
 607                 int                     i_nspname,
 608                                         i_relname;
 609                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 610                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 611
 612                 /* Find any sequences */
 613                 res = executeQueryOrDie(conn,
 614                                                                 "SELECT n.nspname, c.relname "
 615                                                                 "FROM   pg_catalog.pg_class c, "
 616                                                                 "               pg_catalog.pg_namespace n "
 617                                                                 "WHERE  c.relkind = 'S' AND "
 618                                                                 "               c.relnamespace = n.oid AND "
 619                                                           "             n.nspname != 'pg_catalog' AND "
 620                                                  "              n.nspname != 'information_schema'");
 621
 622                 ntups = PQntuples(res);
 623                 i_nspname = PQfnumber(res, "nspname");
 624                 i_relname = PQfnumber(res, "relname");
 625                 for (rowno = 0; rowno < ntups; rowno++)
 626                 {
 627                         PGresult   *seq_res;
 628                         int                     i_last_value,
 629                                                 i_is_called;
 630                         const char *nspname = PQgetvalue(res, rowno, i_nspname);
 631                         const char *relname = PQgetvalue(res, rowno, i_relname);
 632
 633                         found = true;
 634
 635                         if (script == NULL && (script = fopen(output_path, "w")) == NULL)
 636                                 pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
 637                         if (!db_used)
 638                         {
 639                                 fprintf(script, "\\connect %s\n\n",
 640                                                 quote_identifier(active_db->db_name));
 641                                 db_used = true;
 642                         }
 643
 644                         /* Find the desired sequence */
 645                         seq_res = executeQueryOrDie(conn,
 646                                                                                 "SELECT s.last_value, s.is_called "
 647                                                                                 "FROM   %s.%s s",
 648                                                                                 quote_identifier(nspname),
 649                                                                                 quote_identifier(relname));
 650
 651                         assert(PQntuples(seq_res) == 1);
 652                         i_last_value = PQfnumber(seq_res, "last_value");
 653                         i_is_called = PQfnumber(seq_res, "is_called");
 654
 655                         fprintf(script, "SELECT setval('%s.%s', %s, '%s');\n",
 656                                         quote_identifier(nspname), quote_identifier(relname),
 657                                         PQgetvalue(seq_res, 0, i_last_value), PQgetvalue(seq_res, 0, i_is_called));
 658                         PQclear(seq_res);
 659                 }
 660                 if (db_used)
 661                         fprintf(script, "\n");
 662
 663                 PQclear(res);
 664
 665                 PQfinish(conn);
 666         }
 667
 668         if (script)
 669                 fclose(script);
 670
 671         check_ok();
 672
 673         if (found)
 674                 return output_path;
 675         else
 676         {
 677                 pg_free(output_path);
 678                 return NULL;
 679         }
 680 }