postgres.git / summary / log / commit / refs

commit    41247cdf695b99eb4b6359ef3d6bdcfbad321847
Author:   Fujii Masao <fujii@postgresql.org>
Date:     Fri May 22 14:59:04 2026 +0000
First released in: 18.6

    Prevent setting NO INHERIT on partitioned NOT NULL constraints
    
    The documentation states that NOT NULL constraints on partitioned tables
    are always inherited by all partitions, and therefore cannot be declared
    NO INHERIT. While a check already existed to reject creating such
    constraints with NO INHERIT, previously the same check was missing for
    ALTER TABLE ... ALTER CONSTRAINT ... NO INHERIT.
    
    This commit adds the missing check so that attempting to set NO INHERIT
    on a partitioned NOT NULL constraint now fails.
    
    Backpatch to v18, where ALTER TABLE ... ALTER CONSTRAINT ... [NO] INHERIT
    was added.
    
    Author: Andreas Karlsson <andreas@proxel.se>
    Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
    Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
    Discussion: https://postgr.es/m/ecc985ad-6ec1-4094-a315-317943ca5f3f@proxel.se
    Backpatch-through: 18


 src/backend/commands/tablecmds.c          | 6 ++++++
 src/test/regress/expected/constraints.out | 4 ++++
 src/test/regress/sql/constraints.sql      | 3 +++
 3 files changed, 13 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cabe22d48be..5307157f0f3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12262,6 +12262,12 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
 				errcode(ERRCODE_WRONG_OBJECT_TYPE),
 				errmsg("constraint \"%s\" of relation \"%s\" is not a not-null constraint",
 					   cmdcon->conname, RelationGetRelationName(rel)));
+	if (cmdcon->alterInheritability &&
+		cmdcon->noinherit && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+		ereport(ERROR,
+				errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("not-null constraint \"%s\" on partitioned table \"%s\" cannot be NO INHERIT",
+					   cmdcon->conname, RelationGetRelationName(rel)));
 
 	/* Refuse to modify inheritability of inherited constraints */
 	if (cmdcon->alterInheritability &&
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index ebc892a2a42..e619459647c 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1042,6 +1042,10 @@ CREATE TABLE ATACC1 (a int NOT NULL NO INHERIT) PARTITION BY LIST (a);
 ERROR:  not-null constraints on partitioned tables cannot be NO INHERIT
 CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT) PARTITION BY LIST (a);
 ERROR:  not-null constraints on partitioned tables cannot be NO INHERIT
+CREATE TABLE ATACC1 (a int, CONSTRAINT a_is_not_null NOT NULL a) PARTITION BY LIST (a);
+ALTER TABLE ATACC1 ALTER CONSTRAINT a_is_not_null NO INHERIT;
+ERROR:  not-null constraint "a_is_not_null" on partitioned table "atacc1" cannot be NO INHERIT
+DROP TABLE ATACC1;
 -- it's not possible to override a no-inherit constraint with an inheritable one
 CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT);
 CREATE TABLE ATACC1 (a int);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1e9989698b6..99846e7cc6a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -702,6 +702,9 @@ DROP TABLE ATACC1, ATACC2, ATACC3;
 -- NOT NULL NO INHERIT is not possible on partitioned tables
 CREATE TABLE ATACC1 (a int NOT NULL NO INHERIT) PARTITION BY LIST (a);
 CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT) PARTITION BY LIST (a);
+CREATE TABLE ATACC1 (a int, CONSTRAINT a_is_not_null NOT NULL a) PARTITION BY LIST (a);
+ALTER TABLE ATACC1 ALTER CONSTRAINT a_is_not_null NO INHERIT;
+DROP TABLE ATACC1;
 
 -- it's not possible to override a no-inherit constraint with an inheritable one
 CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT);


[parent: 89b4b3ae35d2]