8000 pg_upgrade: check for inconsistencies in not-null constraints w/inher… · postgres/postgres@b0f0e22 · GitHub
[go: up one dir, main page]
Skip to content
65FA

Commit b0f0e22

Browse files
author
Álvaro Herrera
committed
pg_upgrade: check for inconsistencies in not-null constraints w/inheritance
With tables defined like this, CREATE TABLE ip (id int PRIMARY KEY); CREATE TABLE ic (id int) INHERITS (ip); ALTER TABLE ic ALTER id DROP NOT NULL; pg_upgrade fails during the schema restore phase due to this error: ERROR: column "id" in child table must be marked NOT NULL This can only be fixed by marking the child column as NOT NULL before the upgrade, which could take an arbitrary amount of time (because ic's data must be scanned). Have pg_upgrade's check mode warn if that condition is found, so that users know what to adjust before running the upgrade for real. Author: Ali Akbar <the.apaan@gmail.com> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Backpatch-through: 13 Discussion: https://postgr.es/m/CACQjQLoMsE+1pyLe98pi0KvPG2jQQ94LWJ+PTiLgVRK4B=i_jg@mail.gmail.com
1 parent 8bca447 commit b0f0e22

File tree

1 file changed

+85
-0
lines changed

1 file changed

+85
-0
lines changed

‎src/bin/pg_upgrade/check.c

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ static void check_proper_datallowconn(ClusterInfo *cluster);
2323
static void check_for_prepared_transactions(ClusterInfo *cluster);
2424
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
2525
static void check_for_tables_with_oids(ClusterInfo *cluster);
26+
static void check_for_not_null_inheritance(ClusterInfo *cluster);
2627
static void check_for_composite_data_type_usage(ClusterInfo *cluster);
2728
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
2829
static void check_for_removed_data_type_usage(ClusterInfo *cluster,
@@ -123,6 +124,13 @@ check_and_dump_old_cluster(bool live_check)
123124
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1100)
124125
check_for_tables_with_oids(&old_cluster);
125126

127+
/*
128+
* Pre-PG 18 allowed child tables to omit not-null constraints that their
129+
* parents columns have, but schema restore fails for them. Verify there
130+
* are none.
131+
*/
132+
check_for_not_null_inheritance(&old_cluster);
133+
126134
/*
127135
* PG 12 changed the 'sql_identifier' type storage to be based on name,
128136
* not varchar, which breaks on-disk format for existing data. So we need
@@ -1021,6 +1029,83 @@ check_for_tables_with_oids(ClusterInfo *cluster)
10211029
check_ok();
10221030
}
10231031

1032+
/*
1033+
* check_for_not_null_inheritance()
1034+
*
1035+
* An attempt to create child tables lacking not-null constraints that are
1036+
* present in their parents errors out. This can no longer occur since 18,
1037+
* but previously there were various ways for that to happen. Check that
1038+
* the cluster to be upgraded doesn't have any of those problems.
1039+
*/
1040+
static void
1041+
check_for_not_null_inheritance(ClusterInfo *cluster)
1042+
{
1043+
FILE *script = NULL;
1044+
char output_path[MAXPGPATH];
1045+
int ntup;
1046+
1047+
prep_status("Checking for not-null constraint inconsistencies");
1048+
1049+
snprintf(output_path, sizeof(output_path),
1050+
"not_null_inconsistent_columns.txt");
1051+
for (int dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
1052+
{
1053+
PGresult *res;
1054+
bool db_used = false;
1055+
int i_nspname,
1056+
i_relname,
1057+
i_attname;
1058+
DbInfo *active_db = &old_cluster.dbarr.dbs[dbnum];
1059+
PGconn *conn = connectToServer(&old_cluster, active_db->db_name);
1060+
1061+
res = executeQueryOrDie(conn,
1062+
"SELECT cc.relnamespace::pg_catalog.regnamespace AS nspname, "
1063+
" cc.relname, ac.attname "
1064+
"FROM pg_catalog.pg_inherits i, pg_catalog.pg_attribute ac, "
1065+
" pg_catalog.pg_attribute ap, pg_catalog.pg_class cc "
1066+
"WHERE cc.oid = ac.attrelid AND i.inhrelid = ac.attrelid "
1067+
" AND i.inhparent = ap.attrelid AND ac.attname = ap.attname "
1068+
" AND ap.attnum > 0 and ap.attnotnull AND NOT ac.attnotnull");
1069+
1070+
ntup = PQntuples(res);
1071+
i_nspname = PQfnumber(res, "nspname");
1072+
i_relname = PQfnumber(res, "relname");
1073+
i_attname = PQfnumber(res, "attname");
1074+
for (int i = 0; i < ntup; i++)
1075+
{
1076+
if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
1077+
pg_fatal("could not open file \"%s\": %m", output_path);
1078+
if (!db_used)
1079+
{
1080+
fprintf(script, "In database: %s\n", active_db->db_name);
1081+
db_used = true;
1082+
}
1083+
1084+
fprintf(script, " %s.%s.%s\n",
1085+
PQgetvalue(res, i, i_nspname),
1086+
PQgetvalue(res, i, i_relname),
1087+
PQgetvalue(res, i, i_attname));
1088+
}
1089+
1090+
PQclear(res);
1091+
PQfinish(conn);
1092+
}
1093+
1094+
if (script)
1095+
{
1096+
fclose(script);
1097+
pg_log(PG_REPORT, "fatal");
1098+
pg_fatal("Your installation contains inconsistent NOT NULL constraints.\n"
1099+
"If the parent column(s) are NOT NULL, then the child column must\n"
1100+
"also be marked NOT NULL, or the upgrade will fail.\n"
1101+
"You can fix this by running\n"
1102+
" ALTER TABLE tablename ALTER column SET NOT NULL;\n"
1103+
"on each column listed in the file:\n"
1104+
" %s\n\n", output_path);
1105+
}
1106+
else
1107+
check_ok();
1108+
}
10241109

10251110
/*
10261111
* check_for_composite_data_type_usage()

0 commit comments

Comments
 (0)
0