Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit07da298

Browse files
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 NULLThis can only be fixed by marking the child column as NOT NULL beforethe upgrade, which could take an arbitrary amount of time (because ic'sdata must be scanned). Have pg_upgrade's check mode warn if thatcondition is found, so that users know what to adjust before running theupgrade for real.Author: Ali Akbar <the.apaan@gmail.com>Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>Backpatch-through: 13Discussion:https://postgr.es/m/CACQjQLoMsE+1pyLe98pi0KvPG2jQQ94LWJ+PTiLgVRK4B=i_jg@mail.gmail.com
1 parent29a4b63 commit07da298

File tree

1 file changed

+96
-0
lines changed

1 file changed

+96
-0
lines changed

‎src/bin/pg_upgrade/check.c‎

Lines changed: 96 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
2323
staticvoidcheck_for_user_defined_postfix_ops(ClusterInfo*cluster);
2424
staticvoidcheck_for_incompatible_polymorphics(ClusterInfo*cluster);
2525
staticvoidcheck_for_tables_with_oids(ClusterInfo*cluster);
26+
staticvoidcheck_for_not_null_inheritance(ClusterInfo*cluster);
2627
staticvoidcheck_for_pg_role_prefix(ClusterInfo*cluster);
2728
staticvoidcheck_for_new_tablespace_dir(void);
2829
staticvoidcheck_for_user_defined_encoding_conversions(ClusterInfo*cluster);
@@ -671,6 +672,14 @@ check_and_dump_old_cluster(void)
671672
if (GET_MAJOR_VERSION(old_cluster.major_version) <=1100)
672673
check_for_tables_with_oids(&old_cluster);
673674

675+
/*
676+
* Pre-PG 18 allowed child tables to omit not-null constraints that their
677+
* parents columns have, but schema restore fails for them. Verify there
678+
* are none, iff applicable.
679+
*/
680+
if (GET_MAJOR_VERSION(old_cluster.major_version) <=1800)
681+
check_for_not_null_inheritance(&old_cluster);
682+
674683
/*
675684
* Pre-PG 10 allowed tables with 'unknown' type columns and non WAL logged
676685
* hash indexes
@@ -1623,6 +1632,93 @@ check_for_tables_with_oids(ClusterInfo *cluster)
16231632
check_ok();
16241633
}
16251634

1635+
/*
1636+
* Callback function for processing results of query for
1637+
* check_for_not_null_inheritance.
1638+
*/
1639+
staticvoid
1640+
process_inconsistent_notnull(DbInfo*dbinfo,PGresult*res,void*arg)
1641+
{
1642+
UpgradeTaskReport*report= (UpgradeTaskReport*)arg;
1643+
intntups=PQntuples(res);
1644+
inti_nspname=PQfnumber(res,"nspname");
1645+
inti_relname=PQfnumber(res,"relname");
1646+
inti_attname=PQfnumber(res,"attname");
1647+
1648+
AssertVariableIsOfType(&process_inconsistent_notnull,
1649+
UpgradeTaskProcessCB);
1650+
1651+
if (ntups==0)
1652+
return;
1653+
1654+
if (report->file==NULL&&
1655+
(report->file=fopen_priv(report->path,"w"))==NULL)
1656+
pg_fatal("could not open file \"%s\": %m",report->path);
1657+
1658+
fprintf(report->file,"In database: %s\n",dbinfo->db_name);
1659+
1660+
for (introwno=0;rowno<ntups;rowno++)
1661+
{
1662+
fprintf(report->file," %s.%s.%s\n",
1663+
PQgetvalue(res,rowno,i_nspname),
1664+
PQgetvalue(res,rowno,i_relname),
1665+
PQgetvalue(res,rowno,i_attname));
1666+
}
1667+
}
1668+
1669+
/*
1670+
* check_for_not_null_inheritance()
1671+
*
1672+
* An attempt to create child tables lacking not-null constraints that are
1673+
* present in their parents errors out. This can no longer occur since 18,
1674+
* but previously there were various ways for that to happen. Check that
1675+
* the cluster to be upgraded doesn't have any of those problems.
1676+
*/
1677+
staticvoid
1678+
check_for_not_null_inheritance(ClusterInfo*cluster)
1679+
{
1680+
UpgradeTaskReportreport;
1681+
UpgradeTask*task;
1682+
constchar*query;
1683+
1684+
prep_status("Checking for not-null constraint inconsistencies");
1685+
1686+
report.file=NULL;
1687+
snprintf(report.path,sizeof(report.path),"%s/%s",
1688+
log_opts.basedir,
1689+
"not_null_inconsistent_columns.txt");
1690+
1691+
query="SELECT cc.relnamespace::pg_catalog.regnamespace AS nspname, "
1692+
" cc.relname, ac.attname "
1693+
"FROM pg_catalog.pg_inherits i, pg_catalog.pg_attribute ac, "
1694+
" pg_catalog.pg_attribute ap, pg_catalog.pg_class cc "
1695+
"WHERE cc.oid = ac.attrelid AND i.inhrelid = ac.attrelid "
1696+
" AND i.inhparent = ap.attrelid AND ac.attname = ap.attname "
1697+
" AND ap.attnum > 0 and ap.attnotnull AND NOT ac.attnotnull";
1698+
1699+
task=upgrade_task_create();
1700+
upgrade_task_add_step(task,query,
1701+
process_inconsistent_notnull,
1702+
true,&report);
1703+
upgrade_task_run(task,cluster);
1704+
upgrade_task_free(task);
1705+
1706+
if (report.file)
1707+
{
1708+
fclose(report.file);
1709+
pg_log(PG_REPORT,"fatal");
1710+
pg_fatal("Your installation contains inconsistent NOT NULL constraints.\n"
1711+
"If the parent column(s) are NOT NULL, then the child column must\n"
1712+
"also be marked NOT NULL, or the upgrade will fail.\n"
1713+
"You can fix this by running\n"
1714+
" ALTER TABLE tablename ALTER column SET NOT NULL;\n"
1715+
"on each column listed in the file:\n"
1716+
" %s",report.path);
1717+
}
1718+
else
1719+
check_ok();
1720+
}
1721+
16261722

16271723
/*
16281724
* check_for_pg_role_prefix()

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp