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