Header And Logo

PostgreSQL
| The world's most advanced open source database.

version_old_8_3.c

Go to the documentation of this file.
00001 /*
00002  *  version.c
00003  *
00004  *  Postgres-version-specific routines
00005  *
00006  *  Copyright (c) 2010-2013, PostgreSQL Global Development Group
00007  *  contrib/pg_upgrade/version_old_8_3.c
00008  */
00009 
00010 #include "postgres_fe.h"
00011 
00012 #include "pg_upgrade.h"
00013 
00014 #include "access/transam.h"
00015 
00016 
00017 /*
00018  * old_8_3_check_for_name_data_type_usage()
00019  *  8.3 -> 8.4
00020  *  Alignment for the 'name' data type changed to 'char' in 8.4;
00021  *  checks tables and indexes.
00022  */
00023 void
00024 old_8_3_check_for_name_data_type_usage(ClusterInfo *cluster)
00025 {
00026     int         dbnum;
00027     FILE       *script = NULL;
00028     bool        found = false;
00029     char        output_path[MAXPGPATH];
00030 
00031     prep_status("Checking for invalid \"name\" user columns");
00032 
00033     snprintf(output_path, sizeof(output_path), "tables_using_name.txt");
00034 
00035     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
00036     {
00037         PGresult   *res;
00038         bool        db_used = false;
00039         int         ntups;
00040         int         rowno;
00041         int         i_nspname,
00042                     i_relname,
00043                     i_attname;
00044         DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
00045         PGconn     *conn = connectToServer(cluster, active_db->db_name);
00046 
00047         /*
00048          * With a smaller alignment in 8.4, 'name' cannot be used in a
00049          * non-pg_catalog table, except as the first column. (We could tighten
00050          * that condition with enough analysis, but it seems not worth the
00051          * trouble.)
00052          */
00053         res = executeQueryOrDie(conn,
00054                                 "SELECT n.nspname, c.relname, a.attname "
00055                                 "FROM   pg_catalog.pg_class c, "
00056                                 "       pg_catalog.pg_namespace n, "
00057                                 "       pg_catalog.pg_attribute a "
00058                                 "WHERE  c.oid = a.attrelid AND "
00059                                 "       a.attnum > 1 AND "
00060                                 "       NOT a.attisdropped AND "
00061                                 "       a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
00062                                 "       c.relnamespace = n.oid AND "
00063         /* exclude possible orphaned temp tables */
00064                                 "       n.nspname !~ '^pg_temp_' AND "
00065                          "      n.nspname !~ '^pg_toast_temp_' AND "
00066                                 "       n.nspname NOT IN ('pg_catalog', 'information_schema')");
00067 
00068         ntups = PQntuples(res);
00069         i_nspname = PQfnumber(res, "nspname");
00070         i_relname = PQfnumber(res, "relname");
00071         i_attname = PQfnumber(res, "attname");
00072         for (rowno = 0; rowno < ntups; rowno++)
00073         {
00074             found = true;
00075             if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
00076                 pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
00077             if (!db_used)
00078             {
00079                 fprintf(script, "Database: %s\n", active_db->db_name);
00080                 db_used = true;
00081             }
00082             fprintf(script, "  %s.%s.%s\n",
00083                     PQgetvalue(res, rowno, i_nspname),
00084                     PQgetvalue(res, rowno, i_relname),
00085                     PQgetvalue(res, rowno, i_attname));
00086         }
00087 
00088         PQclear(res);
00089 
00090         PQfinish(conn);
00091     }
00092 
00093     if (script)
00094         fclose(script);
00095 
00096     if (found)
00097     {
00098         pg_log(PG_REPORT, "fatal\n");
00099         pg_log(PG_FATAL,
00100                "Your installation contains the \"name\" data type in user tables.  This\n"
00101         "data type changed its internal alignment between your old and new\n"
00102                "clusters so this cluster cannot currently be upgraded.  You can remove\n"
00103         "the problem tables and restart the upgrade.  A list of the problem\n"
00104                "columns is in the file:\n"
00105                "    %s\n\n", output_path);
00106     }
00107     else
00108         check_ok();
00109 }
00110 
00111 
00112 /*
00113  * old_8_3_check_for_tsquery_usage()
00114  *  8.3 -> 8.4
00115  *  A new 'prefix' field was added to the 'tsquery' data type in 8.4
00116  *  so upgrading of such fields is impossible.
00117  */
00118 void
00119 old_8_3_check_for_tsquery_usage(ClusterInfo *cluster)
00120 {
00121     int         dbnum;
00122     FILE       *script = NULL;
00123     bool        found = false;
00124     char        output_path[MAXPGPATH];
00125 
00126     prep_status("Checking for tsquery user columns");
00127 
00128     snprintf(output_path, sizeof(output_path), "tables_using_tsquery.txt");
00129 
00130     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
00131     {
00132         PGresult   *res;
00133         bool        db_used = false;
00134         int         ntups;
00135         int         rowno;
00136         int         i_nspname,
00137                     i_relname,
00138                     i_attname;
00139         DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
00140         PGconn     *conn = connectToServer(cluster, active_db->db_name);
00141 
00142         /* Find any user-defined tsquery columns */
00143         res = executeQueryOrDie(conn,
00144                                 "SELECT n.nspname, c.relname, a.attname "
00145                                 "FROM   pg_catalog.pg_class c, "
00146                                 "       pg_catalog.pg_namespace n, "
00147                                 "       pg_catalog.pg_attribute a "
00148         /* materialized views didn't exist in 8.3, so no need to check 'm' */
00149                                 "WHERE  c.relkind = 'r' AND "
00150                                 "       c.oid = a.attrelid AND "
00151                                 "       NOT a.attisdropped AND "
00152                                 "       a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
00153                                 "       c.relnamespace = n.oid AND "
00154         /* exclude possible orphaned temp tables */
00155                                 "       n.nspname !~ '^pg_temp_' AND "
00156                          "      n.nspname !~ '^pg_toast_temp_' AND "
00157                                 "       n.nspname NOT IN ('pg_catalog', 'information_schema')");
00158 
00159         ntups = PQntuples(res);
00160         i_nspname = PQfnumber(res, "nspname");
00161         i_relname = PQfnumber(res, "relname");
00162         i_attname = PQfnumber(res, "attname");
00163         for (rowno = 0; rowno < ntups; rowno++)
00164         {
00165             found = true;
00166             if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
00167                 pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
00168             if (!db_used)
00169             {
00170                 fprintf(script, "Database: %s\n", active_db->db_name);
00171                 db_used = true;
00172             }
00173             fprintf(script, "  %s.%s.%s\n",
00174                     PQgetvalue(res, rowno, i_nspname),
00175                     PQgetvalue(res, rowno, i_relname),
00176                     PQgetvalue(res, rowno, i_attname));
00177         }
00178 
00179         PQclear(res);
00180 
00181         PQfinish(conn);
00182     }
00183 
00184     if (script)
00185         fclose(script);
00186 
00187     if (found)
00188     {
00189         pg_log(PG_REPORT, "fatal\n");
00190         pg_log(PG_FATAL,
00191                "Your installation contains the \"tsquery\" data type.    This data type\n"
00192                "added a new internal field between your old and new clusters so this\n"
00193         "cluster cannot currently be upgraded.  You can remove the problem\n"
00194                "columns and restart the upgrade.  A list of the problem columns is in the\n"
00195                "file:\n"
00196                "    %s\n\n", output_path);
00197     }
00198     else
00199         check_ok();
00200 }
00201 
00202 
00203 /*
00204  *  old_8_3_check_ltree_usage()
00205  *  8.3 -> 8.4
00206  *  The internal ltree structure was changed in 8.4 so upgrading is impossible.
00207  */
00208 void
00209 old_8_3_check_ltree_usage(ClusterInfo *cluster)
00210 {
00211     int         dbnum;
00212     FILE       *script = NULL;
00213     bool        found = false;
00214     char        output_path[MAXPGPATH];
00215 
00216     prep_status("Checking for contrib/ltree");
00217 
00218     snprintf(output_path, sizeof(output_path), "contrib_ltree.txt");
00219 
00220     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
00221     {
00222         PGresult   *res;
00223         bool        db_used = false;
00224         int         ntups;
00225         int         rowno;
00226         int         i_nspname,
00227                     i_proname;
00228         DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
00229         PGconn     *conn = connectToServer(cluster, active_db->db_name);
00230 
00231         /* Find any functions coming from contrib/ltree */
00232         res = executeQueryOrDie(conn,
00233                                 "SELECT n.nspname, p.proname "
00234                                 "FROM   pg_catalog.pg_proc p, "
00235                                 "       pg_catalog.pg_namespace n "
00236                                 "WHERE  p.pronamespace = n.oid AND "
00237                                 "       p.probin = '$libdir/ltree'");
00238 
00239         ntups = PQntuples(res);
00240         i_nspname = PQfnumber(res, "nspname");
00241         i_proname = PQfnumber(res, "proname");
00242         for (rowno = 0; rowno < ntups; rowno++)
00243         {
00244             found = true;
00245             if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
00246                 pg_log(PG_FATAL, "Could not open file \"%s\": %s\n",
00247                        output_path, getErrorText(errno));
00248             if (!db_used)
00249             {
00250                 fprintf(script, "Database: %s\n", active_db->db_name);
00251                 db_used = true;
00252             }
00253             fprintf(script, "  %s.%s\n",
00254                     PQgetvalue(res, rowno, i_nspname),
00255                     PQgetvalue(res, rowno, i_proname));
00256         }
00257 
00258         PQclear(res);
00259 
00260         PQfinish(conn);
00261     }
00262 
00263     if (script)
00264         fclose(script);
00265 
00266     if (found)
00267     {
00268         pg_log(PG_REPORT, "fatal\n");
00269         pg_log(PG_FATAL,
00270                "Your installation contains the \"ltree\" data type.  This data type\n"
00271                "changed its internal storage format between your old and new clusters so this\n"
00272                "cluster cannot currently be upgraded.  You can manually upgrade databases\n"
00273                "that use \"contrib/ltree\" facilities and remove \"contrib/ltree\" from the old\n"
00274                "cluster and restart the upgrade.  A list of the problem functions is in the\n"
00275                "file:\n"
00276                "    %s\n\n", output_path);
00277     }
00278     else
00279         check_ok();
00280 }
00281 
00282 
00283 /*
00284  * old_8_3_rebuild_tsvector_tables()
00285  *  8.3 -> 8.4
00286  * 8.3 sorts lexemes by its length and if lengths are the same then it uses
00287  * alphabetic order;  8.4 sorts lexemes in lexicographical order, e.g.
00288  *
00289  * => SELECT 'c bb aaa'::tsvector;
00290  *     tsvector
00291  * ----------------
00292  *  'aaa' 'bb' 'c'         -- 8.4
00293  *  'c' 'bb' 'aaa'         -- 8.3
00294  */
00295 void
00296 old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode)
00297 {
00298     int         dbnum;
00299     FILE       *script = NULL;
00300     bool        found = false;
00301     char        output_path[MAXPGPATH];
00302 
00303     prep_status("Checking for tsvector user columns");
00304 
00305     snprintf(output_path, sizeof(output_path), "rebuild_tsvector_tables.sql");
00306 
00307     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
00308     {
00309         PGresult   *res;
00310         bool        db_used = false;
00311         char        nspname[NAMEDATALEN] = "",
00312                     relname[NAMEDATALEN] = "";
00313         int         ntups;
00314         int         rowno;
00315         int         i_nspname,
00316                     i_relname,
00317                     i_attname;
00318         DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
00319         PGconn     *conn = connectToServer(cluster, active_db->db_name);
00320 
00321         /* Find any user-defined tsvector columns */
00322         res = executeQueryOrDie(conn,
00323                                 "SELECT n.nspname, c.relname, a.attname "
00324                                 "FROM   pg_catalog.pg_class c, "
00325                                 "       pg_catalog.pg_namespace n, "
00326                                 "       pg_catalog.pg_attribute a "
00327         /* materialized views didn't exist in 8.3, so no need to check 'm' */
00328                                 "WHERE  c.relkind = 'r' AND "
00329                                 "       c.oid = a.attrelid AND "
00330                                 "       NOT a.attisdropped AND "
00331                                 "       a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
00332                                 "       c.relnamespace = n.oid AND "
00333         /* exclude possible orphaned temp tables */
00334                                 "       n.nspname !~ '^pg_temp_' AND "
00335                          "      n.nspname !~ '^pg_toast_temp_' AND "
00336                                 "       n.nspname NOT IN ('pg_catalog', 'information_schema')");
00337 
00338 /*
00339  *  This macro is used below to avoid reindexing indexes already rebuilt
00340  *  because of tsvector columns.
00341  */
00342 #define SKIP_TSVECTOR_TABLES \
00343                                 "i.indrelid NOT IN ( "                  \
00344                                 "SELECT DISTINCT c.oid "                \
00345                                 "FROM   pg_catalog.pg_class c, "        \
00346                                 "       pg_catalog.pg_namespace n, "    \
00347                                 "       pg_catalog.pg_attribute a "     \
00348         /* materialized views didn't exist in 8.3, so no need to check 'm' */ \
00349                                 "WHERE  c.relkind = 'r' AND "           \
00350                                 "       c.oid = a.attrelid AND "        \
00351                                 "       NOT a.attisdropped AND "        \
00352                                 "       a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
00353                                 "       c.relnamespace = n.oid AND "    \
00354                                 "       n.nspname !~ '^pg_' AND "       \
00355                                 "       n.nspname != 'information_schema') "
00356 
00357         ntups = PQntuples(res);
00358         i_nspname = PQfnumber(res, "nspname");
00359         i_relname = PQfnumber(res, "relname");
00360         i_attname = PQfnumber(res, "attname");
00361         for (rowno = 0; rowno < ntups; rowno++)
00362         {
00363             found = true;
00364             if (!check_mode)
00365             {
00366                 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
00367                     pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
00368                 if (!db_used)
00369                 {
00370                     fprintf(script, "\\connect %s\n\n",
00371                             quote_identifier(active_db->db_name));
00372                     db_used = true;
00373                 }
00374 
00375                 /* Rebuild all tsvector collumns with one ALTER TABLE command */
00376                 if (strcmp(PQgetvalue(res, rowno, i_nspname), nspname) != 0 ||
00377                     strcmp(PQgetvalue(res, rowno, i_relname), relname) != 0)
00378                 {
00379                     if (strlen(nspname) != 0 || strlen(relname) != 0)
00380                         fprintf(script, ";\n\n");
00381                     fprintf(script, "ALTER TABLE %s.%s\n",
00382                          quote_identifier(PQgetvalue(res, rowno, i_nspname)),
00383                         quote_identifier(PQgetvalue(res, rowno, i_relname)));
00384                 }
00385                 else
00386                     fprintf(script, ",\n");
00387                 strlcpy(nspname, PQgetvalue(res, rowno, i_nspname), sizeof(nspname));
00388                 strlcpy(relname, PQgetvalue(res, rowno, i_relname), sizeof(relname));
00389 
00390                 fprintf(script, "ALTER COLUMN %s "
00391                 /* This could have been a custom conversion function call. */
00392                         "TYPE pg_catalog.tsvector USING %s::pg_catalog.text::pg_catalog.tsvector",
00393                         quote_identifier(PQgetvalue(res, rowno, i_attname)),
00394                         quote_identifier(PQgetvalue(res, rowno, i_attname)));
00395             }
00396         }
00397         if (strlen(nspname) != 0 || strlen(relname) != 0)
00398             fprintf(script, ";\n\n");
00399 
00400         PQclear(res);
00401 
00402         /* XXX Mark tables as not accessible somehow */
00403 
00404         PQfinish(conn);
00405     }
00406 
00407     if (script)
00408         fclose(script);
00409 
00410     if (found)
00411     {
00412         report_status(PG_WARNING, "warning");
00413         if (check_mode)
00414             pg_log(PG_WARNING, "\n"
00415                    "Your installation contains tsvector columns.  The tsvector internal\n"
00416                    "storage format changed between your old and new clusters so the tables\n"
00417                    "must be rebuilt.  After upgrading, you will be given instructions.\n\n");
00418         else
00419             pg_log(PG_WARNING, "\n"
00420                    "Your installation contains tsvector columns.  The tsvector internal\n"
00421                    "storage format changed between your old and new clusters so the tables\n"
00422                    "must be rebuilt.  The file:\n"
00423                    "    %s\n"
00424                    "when executed by psql by the database superuser will rebuild all tables\n"
00425                    "with tsvector columns.\n\n",
00426                    output_path);
00427     }
00428     else
00429         check_ok();
00430 }
00431 
00432 
00433 /*
00434  * old_8_3_invalidate_hash_gin_indexes()
00435  *  8.3 -> 8.4
00436  *  Hash, Gin, and GiST index binary format has changes from 8.3->8.4
00437  */
00438 void
00439 old_8_3_invalidate_hash_gin_indexes(ClusterInfo *cluster, bool check_mode)
00440 {
00441     int         dbnum;
00442     FILE       *script = NULL;
00443     bool        found = false;
00444     char        output_path[MAXPGPATH];
00445 
00446     prep_status("Checking for hash and GIN indexes");
00447 
00448     snprintf(output_path, sizeof(output_path), "reindex_hash_and_gin.sql");
00449 
00450     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
00451     {
00452         PGresult   *res;
00453         bool        db_used = false;
00454         int         ntups;
00455         int         rowno;
00456         int         i_nspname,
00457                     i_relname;
00458         DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
00459         PGconn     *conn = connectToServer(cluster, active_db->db_name);
00460 
00461         /* find hash and gin indexes */
00462         res = executeQueryOrDie(conn,
00463                                 "SELECT n.nspname, c.relname "
00464                                 "FROM   pg_catalog.pg_class c, "
00465                                 "       pg_catalog.pg_index i, "
00466                                 "       pg_catalog.pg_am a, "
00467                                 "       pg_catalog.pg_namespace n "
00468                                 "WHERE  i.indexrelid = c.oid AND "
00469                                 "       c.relam = a.oid AND "
00470                                 "       c.relnamespace = n.oid AND "
00471                             "       a.amname IN ('hash', 'gin') AND "
00472                                 SKIP_TSVECTOR_TABLES);
00473 
00474         ntups = PQntuples(res);
00475         i_nspname = PQfnumber(res, "nspname");
00476         i_relname = PQfnumber(res, "relname");
00477         for (rowno = 0; rowno < ntups; rowno++)
00478         {
00479             found = true;
00480             if (!check_mode)
00481             {
00482                 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
00483                     pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
00484                 if (!db_used)
00485                 {
00486                     fprintf(script, "\\connect %s\n",
00487                             quote_identifier(active_db->db_name));
00488                     db_used = true;
00489                 }
00490                 fprintf(script, "REINDEX INDEX %s.%s;\n",
00491                         quote_identifier(PQgetvalue(res, rowno, i_nspname)),
00492                         quote_identifier(PQgetvalue(res, rowno, i_relname)));
00493             }
00494         }
00495 
00496         PQclear(res);
00497 
00498         if (!check_mode && found)
00499             /* mark hash and gin indexes as invalid */
00500             PQclear(executeQueryOrDie(conn,
00501                                       "UPDATE pg_catalog.pg_index i "
00502                                       "SET  indisvalid = false "
00503                                       "FROM pg_catalog.pg_class c, "
00504                                       "     pg_catalog.pg_am a, "
00505                                       "     pg_catalog.pg_namespace n "
00506                                       "WHERE    i.indexrelid = c.oid AND "
00507                                       "     c.relam = a.oid AND "
00508                                       "     c.relnamespace = n.oid AND "
00509                                     "       a.amname IN ('hash', 'gin')"));
00510 
00511         PQfinish(conn);
00512     }
00513 
00514     if (script)
00515         fclose(script);
00516 
00517     if (found)
00518     {
00519         report_status(PG_WARNING, "warning");
00520         if (check_mode)
00521             pg_log(PG_WARNING, "\n"
00522                    "Your installation contains hash and/or GIN indexes.  These indexes have\n"
00523                    "different internal formats between your old and new clusters, so they\n"
00524                    "must be reindexed with the REINDEX command.  After upgrading, you will\n"
00525                    "be given REINDEX instructions.\n\n");
00526         else
00527             pg_log(PG_WARNING, "\n"
00528                    "Your installation contains hash and/or GIN indexes.  These indexes have\n"
00529                    "different internal formats between your old and new clusters, so they\n"
00530                    "must be reindexed with the REINDEX command.  The file:\n"
00531                    "    %s\n"
00532                    "when executed by psql by the database superuser will recreate all invalid\n"
00533               "indexes; until then, none of these indexes will be used.\n\n",
00534                    output_path);
00535     }
00536     else
00537         check_ok();
00538 }
00539 
00540 
00541 /*
00542  * old_8_3_invalidate_bpchar_pattern_ops_indexes()
00543  *  8.3 -> 8.4
00544  *  8.4 bpchar_pattern_ops no longer sorts based on trailing spaces
00545  */
00546 void
00547 old_8_3_invalidate_bpchar_pattern_ops_indexes(ClusterInfo *cluster,
00548                                               bool check_mode)
00549 {
00550     int         dbnum;
00551     FILE       *script = NULL;
00552     bool        found = false;
00553     char        output_path[MAXPGPATH];
00554 
00555     prep_status("Checking for bpchar_pattern_ops indexes");
00556 
00557     snprintf(output_path, sizeof(output_path), "reindex_bpchar_ops.sql");
00558 
00559     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
00560     {
00561         PGresult   *res;
00562         bool        db_used = false;
00563         int         ntups;
00564         int         rowno;
00565         int         i_nspname,
00566                     i_relname;
00567         DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
00568         PGconn     *conn = connectToServer(cluster, active_db->db_name);
00569 
00570         /* find bpchar_pattern_ops indexes */
00571 
00572         /*
00573          * Do only non-hash, non-gin indexees;  we already invalidated them
00574          * above; no need to reindex twice
00575          */
00576         res = executeQueryOrDie(conn,
00577                                 "SELECT n.nspname, c.relname "
00578                                 "FROM   pg_catalog.pg_index i, "
00579                                 "       pg_catalog.pg_class c, "
00580                                 "       pg_catalog.pg_namespace n "
00581                                 "WHERE  indexrelid = c.oid AND "
00582                                 "       c.relnamespace = n.oid AND "
00583                                 "       ( "
00584                                 "           SELECT  o.oid "
00585                    "            FROM    pg_catalog.pg_opclass o, "
00586                   "                 pg_catalog.pg_am a"
00587         "           WHERE   a.amname NOT IN ('hash', 'gin') AND "
00588             "                   a.oid = o.opcmethod AND "
00589                                 "                   o.opcname = 'bpchar_pattern_ops') "
00590                                 "       = ANY (i.indclass) AND "
00591                                 SKIP_TSVECTOR_TABLES);
00592 
00593         ntups = PQntuples(res);
00594         i_nspname = PQfnumber(res, "nspname");
00595         i_relname = PQfnumber(res, "relname");
00596         for (rowno = 0; rowno < ntups; rowno++)
00597         {
00598             found = true;
00599             if (!check_mode)
00600             {
00601                 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
00602                     pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
00603                 if (!db_used)
00604                 {
00605                     fprintf(script, "\\connect %s\n",
00606                             quote_identifier(active_db->db_name));
00607                     db_used = true;
00608                 }
00609                 fprintf(script, "REINDEX INDEX %s.%s;\n",
00610                         quote_identifier(PQgetvalue(res, rowno, i_nspname)),
00611                         quote_identifier(PQgetvalue(res, rowno, i_relname)));
00612             }
00613         }
00614 
00615         PQclear(res);
00616 
00617         if (!check_mode && found)
00618             /* mark bpchar_pattern_ops indexes as invalid */
00619             PQclear(executeQueryOrDie(conn,
00620                                       "UPDATE pg_catalog.pg_index i "
00621                                       "SET  indisvalid = false "
00622                                       "FROM pg_catalog.pg_class c, "
00623                                       "     pg_catalog.pg_namespace n "
00624                                       "WHERE    indexrelid = c.oid AND "
00625                                       "     c.relnamespace = n.oid AND "
00626                                       "     ( "
00627                                       "         SELECT  o.oid "
00628                          "          FROM    pg_catalog.pg_opclass o, "
00629                         "                   pg_catalog.pg_am a"
00630               "         WHERE   a.amname NOT IN ('hash', 'gin') AND "
00631                   "                 a.oid = o.opcmethod AND "
00632                                       "                 o.opcname = 'bpchar_pattern_ops') "
00633                                       "     = ANY (i.indclass)"));
00634 
00635         PQfinish(conn);
00636     }
00637 
00638     if (script)
00639         fclose(script);
00640 
00641     if (found)
00642     {
00643         report_status(PG_WARNING, "warning");
00644         if (check_mode)
00645             pg_log(PG_WARNING, "\n"
00646                    "Your installation contains indexes using \"bpchar_pattern_ops\".  These\n"
00647                    "indexes have different internal formats between your old and new clusters\n"
00648                    "so they must be reindexed with the REINDEX command.  After upgrading, you\n"
00649                    "will be given REINDEX instructions.\n\n");
00650         else
00651             pg_log(PG_WARNING, "\n"
00652                    "Your installation contains indexes using \"bpchar_pattern_ops\".  These\n"
00653                    "indexes have different internal formats between your old and new clusters\n"
00654             "so they must be reindexed with the REINDEX command.  The file:\n"
00655                    "    %s\n"
00656                    "when executed by psql by the database superuser will recreate all invalid\n"
00657               "indexes; until then, none of these indexes will be used.\n\n",
00658                    output_path);
00659     }
00660     else
00661         check_ok();
00662 }
00663 
00664 
00665 /*
00666  * old_8_3_create_sequence_script()
00667  *  8.3 -> 8.4
00668  *  8.4 added the column "start_value" to all sequences.  For this reason,
00669  *  we don't transfer sequence files but instead use the CREATE SEQUENCE
00670  *  command from the schema dump, and use setval() to restore the sequence
00671  *  value and 'is_called' from the old database.  This is safe to run
00672  *  by pg_upgrade because sequence files are not transferred from the old
00673  *  server, even in link mode.
00674  */
00675 char *
00676 old_8_3_create_sequence_script(ClusterInfo *cluster)
00677 {
00678     int         dbnum;
00679     FILE       *script = NULL;
00680     bool        found = false;
00681     char       *output_path = pg_malloc(MAXPGPATH);
00682 
00683     snprintf(output_path, MAXPGPATH, "adjust_sequences.sql");
00684 
00685     prep_status("Creating script to adjust sequences");
00686 
00687     for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
00688     {
00689         PGresult   *res;
00690         bool        db_used = false;
00691         int         ntups;
00692         int         rowno;
00693         int         i_nspname,
00694                     i_relname;
00695         DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];
00696         PGconn     *conn = connectToServer(cluster, active_db->db_name);
00697 
00698         /* Find any sequences */
00699         res = executeQueryOrDie(conn,
00700                                 "SELECT n.nspname, c.relname "
00701                                 "FROM   pg_catalog.pg_class c, "
00702                                 "       pg_catalog.pg_namespace n "
00703                                 "WHERE  c.relkind = 'S' AND "
00704                                 "       c.relnamespace = n.oid AND "
00705         /* exclude possible orphaned temp tables */
00706                                 "       n.nspname !~ '^pg_temp_' AND "
00707                          "      n.nspname !~ '^pg_toast_temp_' AND "
00708                                 "       n.nspname NOT IN ('pg_catalog', 'information_schema')");
00709 
00710         ntups = PQntuples(res);
00711         i_nspname = PQfnumber(res, "nspname");
00712         i_relname = PQfnumber(res, "relname");
00713         for (rowno = 0; rowno < ntups; rowno++)
00714         {
00715             PGresult   *seq_res;
00716             int         i_last_value,
00717                         i_is_called;
00718             const char *nspname = PQgetvalue(res, rowno, i_nspname);
00719             const char *relname = PQgetvalue(res, rowno, i_relname);
00720 
00721             found = true;
00722 
00723             if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
00724                 pg_log(PG_FATAL, "could not open file \"%s\": %s\n", output_path, getErrorText(errno));
00725             if (!db_used)
00726             {
00727                 fprintf(script, "\\connect %s\n\n",
00728                         quote_identifier(active_db->db_name));
00729                 db_used = true;
00730             }
00731 
00732             /* Find the desired sequence */
00733             seq_res = executeQueryOrDie(conn,
00734                                         "SELECT s.last_value, s.is_called "
00735                                         "FROM   %s.%s s",
00736                                         quote_identifier(nspname),
00737                                         quote_identifier(relname));
00738 
00739             assert(PQntuples(seq_res) == 1);
00740             i_last_value = PQfnumber(seq_res, "last_value");
00741             i_is_called = PQfnumber(seq_res, "is_called");
00742 
00743             fprintf(script, "SELECT setval('%s.%s', %s, '%s');\n",
00744                     quote_identifier(nspname), quote_identifier(relname),
00745                     PQgetvalue(seq_res, 0, i_last_value), PQgetvalue(seq_res, 0, i_is_called));
00746             PQclear(seq_res);
00747         }
00748         if (db_used)
00749             fprintf(script, "\n");
00750 
00751         PQclear(res);
00752 
00753         PQfinish(conn);
00754     }
00755 
00756     if (script)
00757         fclose(script);
00758 
00759     check_ok();
00760 
00761     if (found)
00762         return output_path;
00763     else
00764     {
00765         pg_free(output_path);
00766         return NULL;
00767     }
00768 }