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