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