public inbox for [email protected]
help / color / mirror / Atom feedFrom: Daniel Gustafsson <[email protected]>
Subject: [PATCH v4 1/1] pg_upgrade: run all data type checks per connection
Date: Mon, 13 Mar 2023 14:46:24 +0100
The checks for data type usage were each connecting to all databases
in the cluster and running their query. On cluster which have a lot
of databases this can become unnecessarily expensive. This moves the
checks to run in a single connection instead to minimize connection
setup/teardown overhead.
Reviewed-by: Nathan Bossart <[email protected]>
Reviewed-by: Justin Pryzby <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/bin/pg_upgrade/check.c | 575 ++++++++++++++++++++------------
src/bin/pg_upgrade/pg_upgrade.h | 29 +-
src/bin/pg_upgrade/version.c | 289 +++-------------
3 files changed, 433 insertions(+), 460 deletions(-)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 64024e3b9e..c829aed26e 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -10,6 +10,7 @@
#include "postgres_fe.h"
#include "catalog/pg_authid_d.h"
+#include "catalog/pg_class_d.h"
#include "catalog/pg_collation.h"
#include "fe_utils/string_utils.h"
#include "mb/pg_wchar.h"
@@ -23,14 +24,375 @@ static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_user_defined_postfix_ops(ClusterInfo *cluster);
static void check_for_incompatible_polymorphics(ClusterInfo *cluster);
static void check_for_tables_with_oids(ClusterInfo *cluster);
-static void check_for_composite_data_type_usage(ClusterInfo *cluster);
-static void check_for_reg_data_type_usage(ClusterInfo *cluster);
-static void check_for_aclitem_data_type_usage(ClusterInfo *cluster);
-static void check_for_jsonb_9_4_usage(ClusterInfo *cluster);
static void check_for_pg_role_prefix(ClusterInfo *cluster);
static void check_for_new_tablespace_dir(ClusterInfo *new_cluster);
static void check_for_user_defined_encoding_conversions(ClusterInfo *cluster);
+/*
+ * Data type usage checks. Each check for problematic data type usage is
+ * defined in this array with metadata, SQL query for finding the data type
+ * and a function pointer for determining if the check should be executed
+ * for the current version.
+ */
+static int n_data_types_usage_checks = 7;
+static DataTypesUsageChecks data_types_usage_checks[] = {
+ /*
+ * Look for composite types that were made during initdb *or* belong to
+ * information_schema; that's important in case information_schema was
+ * dropped and reloaded.
+ *
+ * The cutoff OID here should match the source cluster's value of
+ * FirstNormalObjectId. We hardcode it rather than using that C #define
+ * because, if that #define is ever changed, our own version's value is
+ * NOT what to use. Eventually we may need a test on the source cluster's
+ * version to select the correct value.
+ */
+ {.status = "Checking for system-defined composite types in user tables",
+ .report_filename = "tables_using_composite.txt",
+ .base_query =
+ "SELECT t.oid FROM pg_catalog.pg_type t "
+ "LEFT JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid "
+ " WHERE typtype = 'c' AND (t.oid < 16384 OR nspname = 'information_schema')",
+ .report_text =
+ "Your installation contains system-defined composite type(s) in user tables.\n"
+ "These type OIDs are not stable across PostgreSQL versions,\n"
+ "so this cluster cannot currently be upgraded. You can\n"
+ "drop the problem columns and restart the upgrade.\n"
+ "A list of the problem columns is in the file:",
+ .version_hook = NULL},
+
+ /*
+ * 9.3 -> 9.4
+ * Fully implement the 'line' data type in 9.4, which previously returned
+ * "not enabled" by default and was only functionally enabled with a
+ * compile-time switch; as of 9.4 "line" has a different on-disk
+ * representation format.
+ */
+ {.status = "Checking for incompatible \"line\" data type",
+ .report_filename = "tables_using_line.txt",
+ .base_query =
+ "SELECT 'pg_catalog.line'::pg_catalog.regtype AS oid",
+ .report_text =
+ "your installation contains the \"line\" data type in user tables.\n"
+ "this data type changed its internal and input/output format\n"
+ "between your old and new versions so this\n"
+ "cluster cannot currently be upgraded. you can\n"
+ "drop the problem columns and restart the upgrade.\n"
+ "a list of the problem columns is in the file:",
+ .version_hook = old_9_3_check_for_line_data_type_usage},
+
+ /*
+ * pg_upgrade only preserves these system values:
+ * pg_class.oid
+ * pg_type.oid
+ * pg_enum.oid
+ *
+ * Many of the reg* data types reference system catalog info that is
+ * not preserved, and hence these data types cannot be used in user
+ * tables upgraded by pg_upgrade.
+ */
+ {.status = "Checking for reg* data types in user tables",
+ .report_filename = "tables_using_reg.txt",
+ /*
+ * Note: older servers will not have all of these reg* types, so we have
+ * to write the query like this rather than depending on casts to regtype.
+ */
+ .base_query =
+ "SELECT oid FROM pg_catalog.pg_type t "
+ "WHERE t.typnamespace = "
+ " (SELECT oid FROM pg_catalog.pg_namespace "
+ " WHERE nspname = 'pg_catalog') "
+ " AND t.typname IN ( "
+ /* pg_class.oid is preserved, so 'regclass' is OK */
+ " 'regcollation', "
+ " 'regconfig', "
+ " 'regdictionary', "
+ " 'regnamespace', "
+ " 'regoper', "
+ " 'regoperator', "
+ " 'regproc', "
+ " 'regprocedure' "
+ /* pg_authid.oid is preserved, so 'regrole' is OK */
+ /* pg_type.oid is (mostly) preserved, so 'regtype' is OK */
+ " )",
+ .report_text =
+ "Your installation contains one of the reg* data types in user tables.\n"
+ "These data types reference system OIDs that are not preserved by\n"
+ "pg_upgrade, so this cluster cannot currently be upgraded. You can\n"
+ "drop the problem columns and restart the upgrade.\n"
+ "A list of the problem columns is in the file:",
+ .version_hook = NULL},
+
+ /*
+ * PG 16 increased the size of the 'aclitem' type, which breaks the on-disk
+ * format for existing data.
+ */
+ {.status = "Checking for incompatible aclitem data type in user tables",
+ .report_filename = "tables_using_aclitem.txt",
+ .base_query =
+ "SELECT 'pg_catalog.aclitem'::pg_catalog.regtype AS oid",
+ .report_text =
+ "Your installation contains the \"aclitem\" data type in user tables.\n"
+ "The internal format of \"aclitem\" changed in PostgreSQL version 16\n"
+ "so this cluster cannot currently be upgraded. You can drop the\n"
+ "problem columns and restart the upgrade. A list of the problem\n"
+ "columns is in the file:",
+ .version_hook = check_for_aclitem_data_type_usage},
+
+ /*
+ * It's no longer allowed to create tables or views with "unknown"-type
+ * columns. We do not complain about views with such columns, because
+ * they should get silently converted to "text" columns during the DDL
+ * dump and reload; it seems unlikely to be worth making users do that
+ * by hand. However, if there's a table with such a column, the DDL
+ * reload will fail, so we should pre-detect that rather than failing
+ * mid-upgrade. Worse, if there's a matview with such a column, the
+ * DDL reload will silently change it to "text" which won't match the
+ * on-disk storage (which is like "cstring"). So we *must* reject that.
+ */
+ {.status = "Checking for invalid \"unknown\" user columns",
+ .report_filename = "tables_using_unknown.txt",
+ .base_query =
+ "SELECT 'pg_catalog.unknown'::pg_catalog.regtype AS oid",
+ .report_text =
+ "Your installation contains the \"unknown\" data type in user tables.\n"
+ "This data type is no longer allowed in tables, so this\n"
+ "cluster cannot currently be upgraded. You can\n"
+ "drop the problem columns and restart the upgrade.\n"
+ "A list of the problem columns is in the file:",
+ .version_hook = old_9_6_check_for_unknown_data_type_usage},
+
+ /*
+ * PG 12 changed the 'sql_identifier' type storage to be based on name,
+ * not varchar, which breaks on-disk format for existing data. So we need
+ * to prevent upgrade when used in user objects (tables, indexes, ...).
+ * In 12, the sql_identifier data type was switched from name to varchar,
+ * which does affect the storage (name is by-ref, but not varlena). This
+ * means user tables using sql_identifier for columns are broken because
+ * the on-disk format is different.
+ */
+ {.status = "Checking for invalid \"sql_identifier\" user columns",
+ .report_filename = "tables_using_sql_identifier.txt",
+ .base_query =
+ "SELECT 'information_schema.sql_identifier'::pg_catalog.regtype AS oid",
+ .report_text =
+ "Your installation contains the \"sql_identifier\" data type in user tables.\n"
+ "The on-disk format for this data type has changed, so this\n"
+ "cluster cannot currently be upgraded. You can\n"
+ "drop the problem columns and restart the upgrade.\n"
+ "A list of the problem columns is in the file:",
+ .version_hook = old_11_check_for_sql_identifier_data_type_usage},
+
+ /*
+ * JSONB changed its storage format during 9.4 beta, so check for it.
+ */
+ {.status = "Checking for incompatible \"jsonb\" data type",
+ .report_filename = "tables_using_jsonb.txt",
+ .base_query =
+ "SELECT 'pg_catalog.jsonb'::pg_catalog.regtype AS oid",
+ .report_text =
+ "Your installation contains the \"jsonb\" data type in user tables.\n"
+ "The internal format of \"jsonb\" changed during 9.4 beta so this\n"
+ "cluster cannot currently be upgraded. You can\n"
+ "drop the problem columns and restart the upgrade.\n"
+ "A list of the problem columns is in the file:",
+ .version_hook = check_for_jsonb_9_4_usage},
+};
+
+/*
+ * check_for_data_types_usage()
+ * Detect whether there are any stored columns depending on given type(s)
+ *
+ * If so, write a report to the given file name and signal a failure to the
+ * user.
+ *
+ * The checks to run are defined in a DataTypesUsageChecks structure where
+ * each check has a metadata for explaining errors to the user, a base_query,
+ * a report filename and a function pointer hook for validating if the check
+ * should be executed given the cluster at hand.
+ *
+ * base_query should be a SELECT yielding a single column named "oid",
+ * containing the pg_type OIDs of one or more types that are known to have
+ * inconsistent on-disk representations across server versions.
+ *
+ * We check for the type(s) in tables, matviews, and indexes, but not views;
+ * there's no storage involved in a view.
+ */
+static void
+check_for_data_types_usage(ClusterInfo *cluster, DataTypesUsageChecks *checks)
+{
+ bool found = false;
+ bool *results;
+ PQExpBufferData report;
+
+ prep_status("Checking for data type usage");
+
+ /* Prepare an array to store the results of checks in */
+ results = pg_malloc(sizeof(bool) * n_data_types_usage_checks);
+ memset(results, true, sizeof(*results));
+
+ prep_status_progress("checking all databases");
+
+ /*
+ * Connect to each database in the cluster and run all defined checks
+ * against that database before trying the next one.
+ */
+ for (int dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
+ {
+ DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
+ PGconn *conn = connectToServer(cluster, active_db->db_name);
+
+ for (int checknum = 0; checknum < n_data_types_usage_checks; checknum++)
+ {
+ PGresult *res;
+ int ntups;
+ int i_nspname;
+ int i_relname;
+ int i_attname;
+ FILE *script = NULL;
+ bool db_used = false;
+ char output_path[MAXPGPATH];
+ DataTypesUsageChecks *cur_check = &checks[checknum];
+
+ /*
+ * Make sure that the check applies to the current cluster version
+ * and skip if not. If no check hook has been defined we run the
+ * check for all versions.
+ */
+ if (cur_check->version_hook && !cur_check->version_hook(cluster))
+ {
+ cur_check++;
+ continue;
+ }
+
+ snprintf(output_path, sizeof(output_path), "%s/%s",
+ log_opts.basedir,
+ cur_check->report_filename);
+
+ /*
+ * The type(s) of interest might be wrapped in a domain, array,
+ * composite, or range, and these container types can be nested (to
+ * varying extents depending on server version, but that's not of
+ * concern here). To handle all these cases we need a recursive CTE.
+ */
+ res = executeQueryOrDie(conn,
+ "WITH RECURSIVE oids AS ( "
+ /* start with the type(s) returned by base_query */
+ " %s "
+ " UNION ALL "
+ " SELECT * FROM ( "
+ /* inner WITH because we can only reference the CTE once */
+ " WITH x AS (SELECT oid FROM oids) "
+ /* domains on any type selected so far */
+ " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
+ " UNION ALL "
+ /* arrays over any type selected so far */
+ " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typelem = x.oid AND typtype = 'b' "
+ " UNION ALL "
+ /* composite types containing any type selected so far */
+ " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
+ " WHERE t.typtype = 'c' AND "
+ " t.oid = c.reltype AND "
+ " c.oid = a.attrelid AND "
+ " NOT a.attisdropped AND "
+ " a.atttypid = x.oid "
+ " UNION ALL "
+ /* ranges containing any type selected so far */
+ " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_range r, x "
+ " WHERE t.typtype = 'r' AND r.rngtypid = t.oid AND r.rngsubtype = x.oid"
+ " ) foo "
+ ") "
+ /* now look for stored columns of any such type */
+ "SELECT n.nspname, c.relname, a.attname "
+ "FROM pg_catalog.pg_class c, "
+ " pg_catalog.pg_namespace n, "
+ " pg_catalog.pg_attribute a "
+ "WHERE c.oid = a.attrelid AND "
+ " NOT a.attisdropped AND "
+ " a.atttypid IN (SELECT oid FROM oids) AND "
+ " c.relkind IN ("
+ CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_MATVIEW) ", "
+ CppAsString2(RELKIND_INDEX) ") AND "
+ " c.relnamespace = n.oid AND "
+ /* exclude possible orphaned temp tables */
+ " n.nspname !~ '^pg_temp_' AND "
+ " n.nspname !~ '^pg_toast_temp_' AND "
+ /* exclude system catalogs, too */
+ " n.nspname NOT IN ('pg_catalog', 'information_schema')",
+ cur_check->base_query);
+
+ ntups = PQntuples(res);
+
+ /*
+ * The datatype was found, so extract the data and log to the
+ * requested filename. We need to open the file for appending
+ * since the check might have already found the type in another
+ * database earlier in the loop.
+ */
+ if (ntups)
+ {
+ /*
+ * Make sure we have a buffer to save reports to now that we
+ * found a first failing check.
+ */
+ if (!found)
+ initPQExpBuffer(&report);
+ found = true;
+
+ /*
+ * If this is the first time we see an error for the check in
+ * question then print a status message of the failure.
+ */
+ if (results[checknum])
+ {
+ pg_log(PG_REPORT, " failed check: %s", cur_check->status);
+ appendPQExpBuffer(&report, "\n%s\n %s\n",
+ cur_check->report_text, output_path);
+ }
+ results[checknum] = false;
+
+ i_nspname = PQfnumber(res, "nspname");
+ i_relname = PQfnumber(res, "relname");
+ i_attname = PQfnumber(res, "attname");
+
+ for (int rowno = 0; rowno < ntups; rowno++)
+ {
+ found = true;
+ if (script == NULL && (script = fopen_priv(output_path, "a")) == NULL)
+ pg_fatal("could not open file \"%s\": %s",
+ output_path,
+ strerror(errno));
+ if (!db_used)
+ {
+ fprintf(script, "In database: %s\n", active_db->db_name);
+ db_used = true;
+ }
+ fprintf(script, " %s.%s.%s\n",
+ PQgetvalue(res, rowno, i_nspname),
+ PQgetvalue(res, rowno, i_relname),
+ PQgetvalue(res, rowno, i_attname));
+ }
+
+ if (script)
+ {
+ fclose(script);
+ script = NULL;
+ }
+ }
+
+ PQclear(res);
+ cur_check++;
+ }
+
+ PQfinish(conn);
+ }
+
+ if (found)
+ pg_fatal("Data type checks failed: %s", report.data);
+
+ check_ok();
+}
/*
* fix_path_separator
@@ -100,16 +462,9 @@ check_and_dump_old_cluster(bool live_check)
check_is_install_user(&old_cluster);
check_proper_datallowconn(&old_cluster);
check_for_prepared_transactions(&old_cluster);
- check_for_composite_data_type_usage(&old_cluster);
- check_for_reg_data_type_usage(&old_cluster);
check_for_isn_and_int8_passing_mismatch(&old_cluster);
- /*
- * PG 16 increased the size of the 'aclitem' type, which breaks the
- * on-disk format for existing data.
- */
- if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1500)
- check_for_aclitem_data_type_usage(&old_cluster);
+ check_for_data_types_usage(&old_cluster, data_types_usage_checks);
/*
* PG 14 changed the function signature of encoding conversion functions.
@@ -141,21 +496,12 @@ check_and_dump_old_cluster(bool live_check)
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1100)
check_for_tables_with_oids(&old_cluster);
- /*
- * PG 12 changed the 'sql_identifier' type storage to be based on name,
- * not varchar, which breaks on-disk format for existing data. So we need
- * to prevent upgrade when used in user objects (tables, indexes, ...).
- */
- if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1100)
- old_11_check_for_sql_identifier_data_type_usage(&old_cluster);
-
/*
* 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);
}
@@ -164,14 +510,6 @@ check_and_dump_old_cluster(bool live_check)
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 905)
check_for_pg_role_prefix(&old_cluster);
- if (GET_MAJOR_VERSION(old_cluster.major_version) == 904 &&
- old_cluster.controldata.cat_ver < JSONB_FORMAT_CHANGE_CAT_VER)
- check_for_jsonb_9_4_usage(&old_cluster);
-
- /* Pre-PG 9.4 had a different 'line' data type internal format */
- if (GET_MAJOR_VERSION(old_cluster.major_version) <= 903)
- old_9_3_check_for_line_data_type_usage(&old_cluster);
-
/*
* While not a check option, we do this now because this is the only time
* the old server is running.
@@ -1084,185 +1422,6 @@ check_for_tables_with_oids(ClusterInfo *cluster)
check_ok();
}
-
-/*
- * check_for_composite_data_type_usage()
- * Check for system-defined composite types used in user tables.
- *
- * The OIDs of rowtypes of system catalogs and information_schema views
- * can change across major versions; unlike user-defined types, we have
- * no mechanism for forcing them to be the same in the new cluster.
- * Hence, if any user table uses one, that's problematic for pg_upgrade.
- */
-static void
-check_for_composite_data_type_usage(ClusterInfo *cluster)
-{
- bool found;
- Oid firstUserOid;
- char output_path[MAXPGPATH];
- char *base_query;
-
- prep_status("Checking for system-defined composite types in user tables");
-
- snprintf(output_path, sizeof(output_path), "%s/%s",
- log_opts.basedir,
- "tables_using_composite.txt");
-
- /*
- * Look for composite types that were made during initdb *or* belong to
- * information_schema; that's important in case information_schema was
- * dropped and reloaded.
- *
- * The cutoff OID here should match the source cluster's value of
- * FirstNormalObjectId. We hardcode it rather than using that C #define
- * because, if that #define is ever changed, our own version's value is
- * NOT what to use. Eventually we may need a test on the source cluster's
- * version to select the correct value.
- */
- firstUserOid = 16384;
-
- base_query = psprintf("SELECT t.oid FROM pg_catalog.pg_type t "
- "LEFT JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid "
- " WHERE typtype = 'c' AND (t.oid < %u OR nspname = 'information_schema')",
- firstUserOid);
-
- found = check_for_data_types_usage(cluster, base_query, output_path);
-
- free(base_query);
-
- if (found)
- {
- pg_log(PG_REPORT, "fatal");
- pg_fatal("Your installation contains system-defined composite type(s) in user tables.\n"
- "These type OIDs are not stable across PostgreSQL versions,\n"
- "so this cluster cannot currently be upgraded. You can\n"
- "drop the problem columns and restart the upgrade.\n"
- "A list of the problem columns is in the file:\n"
- " %s", output_path);
- }
- else
- check_ok();
-}
-
-/*
- * check_for_reg_data_type_usage()
- * pg_upgrade only preserves these system values:
- * pg_class.oid
- * pg_type.oid
- * pg_enum.oid
- *
- * Many of the reg* data types reference system catalog info that is
- * not preserved, and hence these data types cannot be used in user
- * tables upgraded by pg_upgrade.
- */
-static void
-check_for_reg_data_type_usage(ClusterInfo *cluster)
-{
- bool found;
- char output_path[MAXPGPATH];
-
- prep_status("Checking for reg* data types in user tables");
-
- snprintf(output_path, sizeof(output_path), "%s/%s",
- log_opts.basedir,
- "tables_using_reg.txt");
-
- /*
- * Note: older servers will not have all of these reg* types, so we have
- * to write the query like this rather than depending on casts to regtype.
- */
- found = check_for_data_types_usage(cluster,
- "SELECT oid FROM pg_catalog.pg_type t "
- "WHERE t.typnamespace = "
- " (SELECT oid FROM pg_catalog.pg_namespace "
- " WHERE nspname = 'pg_catalog') "
- " AND t.typname IN ( "
- /* pg_class.oid is preserved, so 'regclass' is OK */
- " 'regcollation', "
- " 'regconfig', "
- " 'regdictionary', "
- " 'regnamespace', "
- " 'regoper', "
- " 'regoperator', "
- " 'regproc', "
- " 'regprocedure' "
- /* pg_authid.oid is preserved, so 'regrole' is OK */
- /* pg_type.oid is (mostly) preserved, so 'regtype' is OK */
- " )",
- output_path);
-
- if (found)
- {
- pg_log(PG_REPORT, "fatal");
- pg_fatal("Your installation contains one of the reg* data types in user tables.\n"
- "These data types reference system OIDs that are not preserved by\n"
- "pg_upgrade, so this cluster cannot currently be upgraded. You can\n"
- "drop the problem columns and restart the upgrade.\n"
- "A list of the problem columns is in the file:\n"
- " %s", output_path);
- }
- else
- check_ok();
-}
-
-/*
- * check_for_aclitem_data_type_usage
- *
- * aclitem changed its storage format in 16, so check for it.
- */
-static void
-check_for_aclitem_data_type_usage(ClusterInfo *cluster)
-{
- char output_path[MAXPGPATH];
-
- prep_status("Checking for incompatible \"aclitem\" data type in user tables");
-
- snprintf(output_path, sizeof(output_path), "tables_using_aclitem.txt");
-
- if (check_for_data_type_usage(cluster, "pg_catalog.aclitem", output_path))
- {
- pg_log(PG_REPORT, "fatal");
- pg_fatal("Your installation contains the \"aclitem\" data type in user tables.\n"
- "The internal format of \"aclitem\" changed in PostgreSQL version 16\n"
- "so this cluster cannot currently be upgraded. You can drop the\n"
- "problem columns and restart the upgrade. A list of the problem\n"
- "columns is in the file:\n"
- " %s", output_path);
- }
- else
- check_ok();
-}
-
-/*
- * check_for_jsonb_9_4_usage()
- *
- * JSONB changed its storage format during 9.4 beta, so check for it.
- */
-static void
-check_for_jsonb_9_4_usage(ClusterInfo *cluster)
-{
- char output_path[MAXPGPATH];
-
- prep_status("Checking for incompatible \"jsonb\" data type");
-
- snprintf(output_path, sizeof(output_path), "%s/%s",
- log_opts.basedir,
- "tables_using_jsonb.txt");
-
- if (check_for_data_type_usage(cluster, "pg_catalog.jsonb", output_path))
- {
- pg_log(PG_REPORT, "fatal");
- pg_fatal("Your installation contains the \"jsonb\" data type in user tables.\n"
- "The internal format of \"jsonb\" changed during 9.4 beta so this\n"
- "cluster cannot currently be upgraded. You can\n"
- "drop the problem columns and restart the upgrade.\n"
- "A list of the problem columns is in the file:\n"
- " %s", output_path);
- }
- else
- check_ok();
-}
-
/*
* check_for_pg_role_prefix()
*
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 3eea0139c7..208bfbb68e 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -328,6 +328,21 @@ typedef struct
} OSInfo;
+/* Function signature for data type check version hook */
+typedef bool (*DataTypesUsageVersionCheck)(ClusterInfo *cluster);
+
+/*
+ * DataTypesUsageChecks
+ */
+typedef struct
+{
+ const char *status; /* status line to print to the user */
+ const char *report_filename; /* filename to store report to */
+ const char *base_query; /* Query to extract the oid of the datatype */
+ const char *report_text; /* Text to store to report in case of error */
+ DataTypesUsageVersionCheck version_hook;
+} DataTypesUsageChecks;
+
/*
* Global variables
*/
@@ -450,19 +465,15 @@ unsigned int str2uint(const char *str);
/* version.c */
-bool check_for_data_types_usage(ClusterInfo *cluster,
- const char *base_query,
- const char *output_path);
-bool check_for_data_type_usage(ClusterInfo *cluster,
- const char *type_name,
- const char *output_path);
-void old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster);
-void old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster);
+bool old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster);
+bool check_for_jsonb_9_4_usage(ClusterInfo *cluster);
+bool old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster);
+bool old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster);
void old_9_6_invalidate_hash_indexes(ClusterInfo *cluster,
bool check_mode);
-void old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster);
void report_extension_updates(ClusterInfo *cluster);
+bool check_for_aclitem_data_type_usage(ClusterInfo *cluster);
/* 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 403a6d7cfa..828a975ac0 100644
--- a/src/bin/pg_upgrade/version.c
+++ b/src/bin/pg_upgrade/version.c
@@ -9,236 +9,41 @@
#include "postgres_fe.h"
-#include "catalog/pg_class_d.h"
#include "fe_utils/string_utils.h"
#include "pg_upgrade.h"
-
-/*
- * check_for_data_types_usage()
- * Detect whether there are any stored columns depending on given type(s)
- *
- * If so, write a report to the given file name, and return true.
- *
- * base_query should be a SELECT yielding a single column named "oid",
- * containing the pg_type OIDs of one or more types that are known to have
- * inconsistent on-disk representations across server versions.
- *
- * We check for the type(s) in tables, matviews, and indexes, but not views;
- * there's no storage involved in a view.
- */
bool
-check_for_data_types_usage(ClusterInfo *cluster,
- const char *base_query,
- const char *output_path)
+old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
{
- bool found = false;
- FILE *script = NULL;
- int dbnum;
-
- for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
- {
- DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
- PGconn *conn = connectToServer(cluster, active_db->db_name);
- PQExpBufferData querybuf;
- PGresult *res;
- bool db_used = false;
- int ntups;
- int rowno;
- int i_nspname,
- i_relname,
- i_attname;
-
- /*
- * The type(s) of interest might be wrapped in a domain, array,
- * composite, or range, and these container types can be nested (to
- * varying extents depending on server version, but that's not of
- * concern here). To handle all these cases we need a recursive CTE.
- */
- initPQExpBuffer(&querybuf);
- appendPQExpBuffer(&querybuf,
- "WITH RECURSIVE oids AS ( "
- /* start with the type(s) returned by base_query */
- " %s "
- " UNION ALL "
- " SELECT * FROM ( "
- /* inner WITH because we can only reference the CTE once */
- " WITH x AS (SELECT oid FROM oids) "
- /* domains on any type selected so far */
- " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
- " UNION ALL "
- /* arrays over any type selected so far */
- " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typelem = x.oid AND typtype = 'b' "
- " UNION ALL "
- /* composite types containing any type selected so far */
- " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
- " WHERE t.typtype = 'c' AND "
- " t.oid = c.reltype AND "
- " c.oid = a.attrelid AND "
- " NOT a.attisdropped AND "
- " a.atttypid = x.oid "
- " UNION ALL "
- /* ranges containing any type selected so far */
- " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_range r, x "
- " WHERE t.typtype = 'r' AND r.rngtypid = t.oid AND r.rngsubtype = x.oid"
- " ) foo "
- ") "
- /* now look for stored columns of any such type */
- "SELECT n.nspname, c.relname, a.attname "
- "FROM pg_catalog.pg_class c, "
- " pg_catalog.pg_namespace n, "
- " pg_catalog.pg_attribute a "
- "WHERE c.oid = a.attrelid AND "
- " NOT a.attisdropped AND "
- " a.atttypid IN (SELECT oid FROM oids) AND "
- " c.relkind IN ("
- CppAsString2(RELKIND_RELATION) ", "
- CppAsString2(RELKIND_MATVIEW) ", "
- CppAsString2(RELKIND_INDEX) ") AND "
- " c.relnamespace = n.oid AND "
- /* exclude possible orphaned temp tables */
- " n.nspname !~ '^pg_temp_' AND "
- " n.nspname !~ '^pg_toast_temp_' AND "
- /* exclude system catalogs, too */
- " n.nspname NOT IN ('pg_catalog', 'information_schema')",
- base_query);
-
- res = executeQueryOrDie(conn, "%s", querybuf.data);
-
- ntups = PQntuples(res);
- i_nspname = PQfnumber(res, "nspname");
- i_relname = PQfnumber(res, "relname");
- i_attname = PQfnumber(res, "attname");
- for (rowno = 0; rowno < ntups; rowno++)
- {
- found = true;
- if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
- pg_fatal("could not open file \"%s\": %s", output_path,
- strerror(errno));
- if (!db_used)
- {
- fprintf(script, "In database: %s\n", active_db->db_name);
- db_used = true;
- }
- fprintf(script, " %s.%s.%s\n",
- PQgetvalue(res, rowno, i_nspname),
- PQgetvalue(res, rowno, i_relname),
- PQgetvalue(res, rowno, i_attname));
- }
-
- PQclear(res);
-
- termPQExpBuffer(&querybuf);
-
- PQfinish(conn);
- }
-
- if (script)
- fclose(script);
+ /* Pre-PG 9.4 had a different 'line' data type internal format */
+ if (GET_MAJOR_VERSION(cluster->major_version) <= 903)
+ return true;
- return found;
+ return false;
}
/*
- * check_for_data_type_usage()
- * Detect whether there are any stored columns depending on the given type
- *
- * If so, write a report to the given file name, and return true.
+ * check_for_jsonb_9_4_usage()
*
- * type_name should be a fully qualified type name. This is just a
- * trivial wrapper around check_for_data_types_usage() to convert a
- * type name into a base query.
+ * JSONB changed its storage format during 9.4 beta, so check for it.
*/
bool
-check_for_data_type_usage(ClusterInfo *cluster,
- const char *type_name,
- const char *output_path)
-{
- bool found;
- char *base_query;
-
- base_query = psprintf("SELECT '%s'::pg_catalog.regtype AS oid",
- type_name);
-
- found = check_for_data_types_usage(cluster, base_query, output_path);
-
- free(base_query);
-
- return found;
-}
-
-
-/*
- * old_9_3_check_for_line_data_type_usage()
- * 9.3 -> 9.4
- * Fully implement the 'line' data type in 9.4, which previously returned
- * "not enabled" by default and was only functionally enabled with a
- * compile-time switch; as of 9.4 "line" has a different on-disk
- * representation format.
- */
-void
-old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
+check_for_jsonb_9_4_usage(ClusterInfo *cluster)
{
- char output_path[MAXPGPATH];
+ if (GET_MAJOR_VERSION(cluster->major_version) == 904 &&
+ cluster->controldata.cat_ver < JSONB_FORMAT_CHANGE_CAT_VER)
+ return true;
- prep_status("Checking for incompatible \"line\" data type");
-
- snprintf(output_path, sizeof(output_path), "%s/%s",
- log_opts.basedir,
- "tables_using_line.txt");
-
- if (check_for_data_type_usage(cluster, "pg_catalog.line", output_path))
- {
- pg_log(PG_REPORT, "fatal");
- pg_fatal("Your installation contains the \"line\" data type in user tables.\n"
- "This data type changed its internal and input/output format\n"
- "between your old and new versions so this\n"
- "cluster cannot currently be upgraded. You can\n"
- "drop the problem columns and restart the upgrade.\n"
- "A list of the problem columns is in the file:\n"
- " %s", output_path);
- }
- else
- check_ok();
+ return false;
}
-
-/*
- * old_9_6_check_for_unknown_data_type_usage()
- * 9.6 -> 10
- * It's no longer allowed to create tables or views with "unknown"-type
- * columns. We do not complain about views with such columns, because
- * they should get silently converted to "text" columns during the DDL
- * dump and reload; it seems unlikely to be worth making users do that
- * by hand. However, if there's a table with such a column, the DDL
- * reload will fail, so we should pre-detect that rather than failing
- * mid-upgrade. Worse, if there's a matview with such a column, the
- * DDL reload will silently change it to "text" which won't match the
- * on-disk storage (which is like "cstring"). So we *must* reject that.
- */
-void
+bool
old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster)
{
- char output_path[MAXPGPATH];
-
- prep_status("Checking for invalid \"unknown\" user columns");
-
- snprintf(output_path, sizeof(output_path), "%s/%s",
- log_opts.basedir,
- "tables_using_unknown.txt");
-
- if (check_for_data_type_usage(cluster, "pg_catalog.unknown", output_path))
- {
- pg_log(PG_REPORT, "fatal");
- pg_fatal("Your installation contains the \"unknown\" data type in user tables.\n"
- "This data type is no longer allowed in tables, so this\n"
- "cluster cannot currently be upgraded. You can\n"
- "drop the problem columns and restart the upgrade.\n"
- "A list of the problem columns is in the file:\n"
- " %s", output_path);
- }
- else
- check_ok();
+ /* Pre-PG 10 allowed tables with 'unknown' type columns */
+ if (GET_MAJOR_VERSION(cluster->major_version) <= 906)
+ return true;
+ return false;
}
/*
@@ -353,41 +158,20 @@ old_9_6_invalidate_hash_indexes(ClusterInfo *cluster, bool check_mode)
check_ok();
}
-/*
- * old_11_check_for_sql_identifier_data_type_usage()
- * 11 -> 12
- * In 12, the sql_identifier data type was switched from name to varchar,
- * which does affect the storage (name is by-ref, but not varlena). This
- * means user tables using sql_identifier for columns are broken because
- * the on-disk format is different.
- */
-void
+bool
old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster)
{
- char output_path[MAXPGPATH];
-
- prep_status("Checking for invalid \"sql_identifier\" user columns");
-
- snprintf(output_path, sizeof(output_path), "%s/%s",
- log_opts.basedir,
- "tables_using_sql_identifier.txt");
-
- if (check_for_data_type_usage(cluster, "information_schema.sql_identifier",
- output_path))
- {
- pg_log(PG_REPORT, "fatal");
- pg_fatal("Your installation contains the \"sql_identifier\" data type in user tables.\n"
- "The on-disk format for this data type has changed, so this\n"
- "cluster cannot currently be upgraded. You can\n"
- "drop the problem columns and restart the upgrade.\n"
- "A list of the problem columns is in the file:\n"
- " %s", output_path);
- }
- else
- check_ok();
+ /*
+ * PG 12 changed the 'sql_identifier' type storage to be based on name,
+ * not varchar, which breaks on-disk format for existing data. So we need
+ * to prevent upgrade when used in user objects (tables, indexes, ...).
+ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1100)
+ return true;
+
+ return false;
}
-
/*
* report_extension_updates()
* Report extensions that should be updated.
@@ -459,3 +243,22 @@ report_extension_updates(ClusterInfo *cluster)
else
check_ok();
}
+
+/*
+ * check_for_aclitem_data_type_usage
+ *
+ * aclitem changed its storage format in 16, so check for it.
+ */
+bool
+check_for_aclitem_data_type_usage(ClusterInfo *cluster)
+{
+ /*
+ * PG 16 increased the size of the 'aclitem' type, which breaks the on-disk
+ * format for existing data.
+ */
+ if (GET_MAJOR_VERSION(cluster->major_version) <= 1500)
+ return true;
+
+ return false;
+}
+
--
2.25.1
--tKW2IUtsqtDRztdT--
view thread (28+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v4 1/1] pg_upgrade: run all data type checks per connection
In-Reply-To: <no-message-id-1856797@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox