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