From a95410e2ec39b6776381fd01198dc57a063e8185 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Fri, 19 May 2017 16:49:38 -0400 Subject: [PATCH] pg_upgrade: Handle hash index upgrades more smoothly. Mark any old hash indexes as invalid so that they don't get used, and create a script to run REINDEX on all of them. Without this, we'd still try to use any upgraded hash indexes, but it would fail. Amit Kapila, reviewed by me. Per a suggestion from Tom Lane. Discussion: http://postgr.es/m/CAA4eK1Jidtagm7Q81q-WoegOVgkotv0OxvHOjFxcvFRP4X=mSw@mail.gmail.com --- src/bin/pg_upgrade/check.c | 17 ++++- src/bin/pg_upgrade/pg_upgrade.h | 2 + src/bin/pg_upgrade/version.c | 112 ++++++++++++++++++++++++++++++++ 3 files changed, 130 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c index d52c81e249..8b9e81eb40 100644 --- a/src/bin/pg_upgrade/check.c +++ b/src/bin/pg_upgrade/check.c @@ -98,9 +98,16 @@ check_and_dump_old_cluster(bool live_check) check_for_reg_data_type_usage(&old_cluster); check_for_isn_and_int8_passing_mismatch(&old_cluster); - /* Pre-PG 10 allowed tables with 'unknown' type columns */ + /* + * Pre-PG 10 allowed tables with 'unknown' type columns and non WAL logged + * hash indexes + */ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906) + { old_9_6_check_for_unknown_data_type_usage(&old_cluster); + if (user_opts.check) + old_9_6_invalidate_hash_indexes(&old_cluster, true); + } /* 9.5 and below should not have roles starting with pg_ */ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 905) @@ -176,6 +183,14 @@ issue_warnings(void) new_9_0_populate_pg_largeobject_metadata(&new_cluster, false); stop_postmaster(false); } + + /* Reindex hash indexes for old < 10.0 */ + if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906) + { + start_postmaster(&new_cluster, true); + old_9_6_invalidate_hash_indexes(&new_cluster, false); + stop_postmaster(false); + } } diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h index b78b877903..8fbf8acd7e 100644 --- a/src/bin/pg_upgrade/pg_upgrade.h +++ b/src/bin/pg_upgrade/pg_upgrade.h @@ -441,6 +441,8 @@ void new_9_0_populate_pg_largeobject_metadata(ClusterInfo *cluster, bool check_mode); void old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster); void old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster); +void old_9_6_invalidate_hash_indexes(ClusterInfo *cluster, + bool check_mode); /* parallel.c */ void parallel_exec_prog(const char *log_file, const char *opt_log_file, diff --git a/src/bin/pg_upgrade/version.c b/src/bin/pg_upgrade/version.c index a3651aaded..814eaa522c 100644 --- a/src/bin/pg_upgrade/version.c +++ b/src/bin/pg_upgrade/version.c @@ -287,3 +287,115 @@ old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster) else check_ok(); } + +/* + * old_9_6_invalidate_hash_indexes() + * 9.6 -> 10 + * Hash index binary format has changed from 9.6->10.0 + */ +void +old_9_6_invalidate_hash_indexes(ClusterInfo *cluster, bool check_mode) +{ + int dbnum; + FILE *script = NULL; + bool found = false; + char *output_path = "reindex_hash.sql"; + + prep_status("Checking for hash indexes"); + + for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++) + { + PGresult *res; + bool db_used = false; + int ntups; + int rowno; + int i_nspname, + i_relname; + DbInfo *active_db = &cluster->dbarr.dbs[dbnum]; + PGconn *conn = connectToServer(cluster, active_db->db_name); + + /* find hash indexes */ + res = executeQueryOrDie(conn, + "SELECT n.nspname, c.relname " + "FROM pg_catalog.pg_class c, " + " pg_catalog.pg_index i, " + " pg_catalog.pg_am a, " + " pg_catalog.pg_namespace n " + "WHERE i.indexrelid = c.oid AND " + " c.relam = a.oid AND " + " c.relnamespace = n.oid AND " + " a.amname = 'hash'" + ); + + ntups = PQntuples(res); + i_nspname = PQfnumber(res, "nspname"); + i_relname = PQfnumber(res, "relname"); + for (rowno = 0; rowno < ntups; rowno++) + { + found = true; + if (!check_mode) + { + if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL) + pg_fatal("could not open file \"%s\": %s\n", output_path, + strerror(errno)); + if (!db_used) + { + PQExpBufferData connectbuf; + + initPQExpBuffer(&connectbuf); + appendPsqlMetaConnect(&connectbuf, active_db->db_name); + fputs(connectbuf.data, script); + termPQExpBuffer(&connectbuf); + db_used = true; + } + fprintf(script, "REINDEX INDEX %s.%s;\n", + quote_identifier(PQgetvalue(res, rowno, i_nspname)), + quote_identifier(PQgetvalue(res, rowno, i_relname))); + } + } + + PQclear(res); + + if (!check_mode && db_used) + { + /* mark hash indexes as invalid */ + PQclear(executeQueryOrDie(conn, + "UPDATE pg_catalog.pg_index i " + "SET indisvalid = false " + "FROM pg_catalog.pg_class c, " + " pg_catalog.pg_am a, " + " pg_catalog.pg_namespace n " + "WHERE i.indexrelid = c.oid AND " + " c.relam = a.oid AND " + " c.relnamespace = n.oid AND " + " a.amname = 'hash'")); + } + + PQfinish(conn); + } + + if (script) + fclose(script); + + if (found) + { + report_status(PG_WARNING, "warning"); + if (check_mode) + pg_log(PG_WARNING, "\n" + "Your installation contains hash indexes. These indexes have different\n" + "internal formats between your old and new clusters, so they must be\n" + "reindexed with the REINDEX command. After upgrading, you will be given\n" + "REINDEX instructions.\n\n"); + else + pg_log(PG_WARNING, "\n" + "Your installation contains hash indexes. These indexes have different\n" + "internal formats between your old and new clusters, so they must be\n" + "reindexed with the REINDEX command. The file:\n" + " %s\n" + "when executed by psql by the database superuser will recreate all invalid\n" + "indexes; until then, none of these indexes will be used.\n\n", + output_path); + } + else + check_ok(); +} -- 2.40.0