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