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_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_fatal("Your installation contains the \"name\" data type in user tables.  This\n"
 100                 "data type changed its internal alignment between your old and new\n"
 101                            "clusters so this cluster cannot currently be upgraded.  You can remove\n"
 102                 "the problem tables and restart the upgrade.  A list of the problem\n"
 103                            "columns is in the file:\n"
 104                            "    %s\n\n", output_path);
 105         }
 106         else
 107                 check_ok();
 108 }
 109
 110
 111 /*
 112  * old_8_3_check_for_tsquery_usage()
 113  *      8.3 -> 8.4
 114  *      A new 'prefix' field was added to the 'tsquery' data type in 8.4
 115  *      so upgrading of such fields is impossible.
 116  */
 117 void
 118 old_8_3_check_for_tsquery_usage(ClusterInfo *cluster)
 119 {
 120         int                     dbnum;
 121         FILE       *script = NULL;
 122         bool            found = false;
 123         char            output_path[MAXPGPATH];
 124
 125         prep_status("Checking for tsquery user columns");
 126
 127         snprintf(output_path, sizeof(output_path), "tables_using_tsquery.txt");
 128
 129         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 130         {
 131                 PGresult   *res;
 132                 bool            db_used = false;
 133                 int                     ntups;
 134                 int                     rowno;
 135                 int                     i_nspname,
 136                                         i_relname,
 137                                         i_attname;
 138                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 139                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 140
 141                 /* Find any user-defined tsquery columns */
 142                 res = executeQueryOrDie(conn,
 143                                                                 "SELECT n.nspname, c.relname, a.attname "
 144                                                                 "FROM   pg_catalog.pg_class c, "
 145                                                                 "               pg_catalog.pg_namespace n, "
 146                                                                 "               pg_catalog.pg_attribute a "
 147                 /* materialized views didn't exist in 8.3, so no need to check 'm' */
 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_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_fatal("Your installation contains the \"tsquery\" data type.    This data type\n"
 190                            "added a new internal field between your old and new clusters so this\n"
 191                 "cluster cannot currently be upgraded.  You can remove the problem\n"
 192                            "columns and restart the upgrade.  A list of the problem columns is in the\n"
 193                            "file:\n"
 194                            "    %s\n\n", output_path);
 195         }
 196         else
 197                 check_ok();
 198 }
 199
 200
 201 /*
 202  *      old_8_3_check_ltree_usage()
 203  *      8.3 -> 8.4
 204  *      The internal ltree structure was changed in 8.4 so upgrading is impossible.
 205  */
 206 void
 207 old_8_3_check_ltree_usage(ClusterInfo *cluster)
 208 {
 209         int                     dbnum;
 210         FILE       *script = NULL;
 211         bool            found = false;
 212         char            output_path[MAXPGPATH];
 213
 214         prep_status("Checking for contrib/ltree");
 215
 216         snprintf(output_path, sizeof(output_path), "contrib_ltree.txt");
 217
 218         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 219         {
 220                 PGresult   *res;
 221                 bool            db_used = false;
 222                 int                     ntups;
 223                 int                     rowno;
 224                 int                     i_nspname,
 225                                         i_proname;
 226                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 227                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 228
 229                 /* Find any functions coming from contrib/ltree */
 230                 res = executeQueryOrDie(conn,
 231                                                                 "SELECT n.nspname, p.proname "
 232                                                                 "FROM   pg_catalog.pg_proc p, "
 233                                                                 "               pg_catalog.pg_namespace n "
 234                                                                 "WHERE  p.pronamespace = n.oid AND "
 235                                                                 "               p.probin = '$libdir/ltree'");
 236
 237                 ntups = PQntuples(res);
 238                 i_nspname = PQfnumber(res, "nspname");
 239                 i_proname = PQfnumber(res, "proname");
 240                 for (rowno = 0; rowno < ntups; rowno++)
 241                 {
 242                         found = true;
 243                         if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
 244                                 pg_fatal("Could not open file \"%s\": %s\n",
 245                                            output_path, getErrorText(errno));
 246                         if (!db_used)
 247                         {
 248                                 fprintf(script, "Database: %s\n", active_db->db_name);
 249                                 db_used = true;
 250                         }
 251                         fprintf(script, "  %s.%s\n",
 252                                         PQgetvalue(res, rowno, i_nspname),
 253                                         PQgetvalue(res, rowno, i_proname));
 254                 }
 255
 256                 PQclear(res);
 257
 258                 PQfinish(conn);
 259         }
 260
 261         if (script)
 262                 fclose(script);
 263
 264         if (found)
 265         {
 266                 pg_log(PG_REPORT, "fatal\n");
 267                 pg_fatal("Your installation contains the \"ltree\" data type.  This data type\n"
 268                            "changed its internal storage format between your old and new clusters so this\n"
 269                            "cluster cannot currently be upgraded.  You can manually upgrade databases\n"
 270                            "that use \"contrib/ltree\" facilities and remove \"contrib/ltree\" from the old\n"
 271                            "cluster and restart the upgrade.  A list of the problem functions is in the\n"
 272                            "file:\n"
 273                            "    %s\n\n", output_path);
 274         }
 275         else
 276                 check_ok();
 277 }
 278
 279
 280 /*
 281  * old_8_3_rebuild_tsvector_tables()
 282  *      8.3 -> 8.4
 283  * 8.3 sorts lexemes by its length and if lengths are the same then it uses
 284  * alphabetic order;  8.4 sorts lexemes in lexicographical order, e.g.
 285  *
 286  * => SELECT 'c bb aaa'::tsvector;
 287  *         tsvector
 288  * ----------------
 289  *      'aaa' 'bb' 'c'             -- 8.4
 290  *      'c' 'bb' 'aaa'             -- 8.3
 291  */
 292 void
 293 old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode)
 294 {
 295         int                     dbnum;
 296         FILE       *script = NULL;
 297         bool            found = false;
 298         char            output_path[MAXPGPATH];
 299
 300         prep_status("Checking for tsvector user columns");
 301
 302         snprintf(output_path, sizeof(output_path), "rebuild_tsvector_tables.sql");
 303
 304         for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
 305         {
 306                 PGresult   *res;
 307                 bool            db_used = false;
 308                 char            nspname[NAMEDATALEN] = "",
 309                                         relname[NAMEDATALEN] = "";
 310                 int                     ntups;
 311                 int                     rowno;
 312                 int                     i_nspname,
 313                                         i_relname,
 314                                         i_attname;
 315                 DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
 316                 PGconn     *conn = connectToServer(cluster, active_db->db_name);
 317
 318                 /* Find any user-defined tsvector columns */
 319                 res = executeQueryOrDie(conn,
 320                                                                 "SELECT n.nspname, c.relname, a.attname "
 321                                                                 "FROM   pg_catalog.pg_class c, "
 322                                                                 "               pg_catalog.pg_namespace n, "
 323                                                                 "               pg_catalog.pg_attribute a "
 324                 /* materialized views didn't exist in 8.3, so no need to check 'm' */
 325                                                                 "WHERE  c.relkind = 'r' AND "
 326                                                                 "               c.oid = a.attrelid AND "
 327                                                                 "               NOT a.attisdropped AND "
 328                                                                 "               a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
 329                                                                 "               c.relnamespace = n.oid AND "
 330                 /* exclude possible orphaned temp tables */
 331                                                                 "               n.nspname !~ '^pg_temp_' AND "
 332                                                  "              n.nspname !~ '^pg_toast_temp_' AND "
 333                                                                 "               n.nspname NOT IN ('pg_catalog', 'information_schema')");
 334
 335 /*
 336  *      This macro is used below to avoid reindexing indexes already rebuilt
 337  *      because of tsvector columns.
 338  */
 339 #define SKIP_TSVECTOR_TABLES \
 340                                                                 "i.indrelid NOT IN ( "                                  \
 341                                                                 "SELECT DISTINCT c.oid "                                \
 342                                                                 "FROM   pg_catalog.pg_class c, "                \
 343                                                                 "               pg_catalog.pg_namespace n, "    \
 344                                                                 "               pg_catalog.pg_attribute a "             \
 345                 /* materialized views didn't exist in 8.3, so no need to check 'm' */ \
 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_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 and GIN index binary format changed 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_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_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;
 679
 680         output_path = pg_strdup("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_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 }