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