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