public inbox for [email protected]help / color / mirror / Atom feed
Prevent setting NO INHERIT on partitioned not-null constraints 6+ messages / 3 participants [nested] [flat]
* Prevent setting NO INHERIT on partitioned not-null constraints @ 2026-05-20 19:25 Andreas Karlsson <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andreas Karlsson @ 2026-05-20 19:25 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]> Hi! Me and Joel found a bug when working on another patch. We noticed that you cannot create not-null constraints with NO INHEIRT set on partitioned tables, but you can actually set it later by using ALTER CONSTRAINT. This must be an oversight so I have attached a patch which adds a check to prevent this. The SQL below should give an error but does not: CREATE TABLE t ( a int, CONSTRAINT a_is_not_null NOT NULL a ) PARTITION BY LIST (a); ALTER TABLE t ALTER CONSTRAINT a_is_not_null NO INHERIT; -- Andreas Karlsson Percona Attachments: [text/x-patch] v1-0001-Prevent-setting-NO-INHERIT-on-paritioned-not-null.patch (3.3K, 2-v1-0001-Prevent-setting-NO-INHERIT-on-paritioned-not-null.patch) download | inline diff: From 7618730c8eb138e49195970bffbca85e55f50169 Mon Sep 17 00:00:00 2001 From: Andreas Karlsson <[email protected]> Date: Wed, 20 May 2026 21:00:41 +0200 Subject: [PATCH v1] Prevent setting NO INHERIT on paritioned not-null constraints There is a check which prevents NOT NULL contraints from being created with NO INHEIRT on partitioned tables but the same check against it is missing for ALTER TABLE ... ALTER CONSTRAINT which clearly is an oversight. So this commit just adds the missing check. --- 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 92b0f38c353..edbf253f722 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12367,6 +12367,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_WRONG_OBJECT_TYPE), + 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 728ef2fd17e..e54fec7fb57 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -1130,6 +1130,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 483c1e98372..dc133b124bb 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -757,6 +757,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); -- 2.47.3 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Prevent setting NO INHERIT on partitioned not-null constraints @ 2026-05-21 15:01 Jim Jones <[email protected]> parent: Andreas Karlsson <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Jim Jones @ 2026-05-21 15:01 UTC (permalink / raw) To: Andreas Karlsson <[email protected]>; PostgreSQL Hackers <[email protected]> Hi Andreas On 20/05/2026 21:25, Andreas Karlsson wrote: > Me and Joel found a bug when working on another patch. We noticed that > you cannot create not-null constraints with NO INHEIRT set on > partitioned tables, but you can actually set it later by using ALTER > CONSTRAINT. This must be an oversight so I have attached a patch which > adds a check to prevent this. > > The SQL below should give an error but does not: > > CREATE TABLE t ( > a int, > CONSTRAINT a_is_not_null NOT NULL a > ) PARTITION BY LIST (a); > > ALTER TABLE t ALTER CONSTRAINT a_is_not_null NO INHERIT; +1 for the fix. postgres=# CREATE TABLE t ( a int, CONSTRAINT a_is_not_null NOT NULL a ) PARTITION BY LIST (a); CREATE TABLE postgres=# ALTER TABLE t ALTER CONSTRAINT a_is_not_null NO INHERIT; ERROR: not-null constraint "a_is_not_null" on partitioned table "t" cannot be NO INHERIT The errcode is most likely wrong: ERRCODE_WRONG_OBJECT_TYPE -> ERRCODE_FEATURE_NOT_SUPPORTED At least it is inconsistent with an equivalent check in parse_utilcmd.c: if (cxt->ispartitioned && constraint->is_no_inherit) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("not-null constraints on partitioned tables cannot be NO INHERIT")); Thanks! Best, Jim ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Prevent setting NO INHERIT on partitioned not-null constraints @ 2026-05-21 21:22 Andreas Karlsson <[email protected]> parent: Jim Jones <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andreas Karlsson @ 2026-05-21 21:22 UTC (permalink / raw) To: Jim Jones <[email protected]>; PostgreSQL Hackers <[email protected]> On 5/21/26 5:01 PM, Jim Jones wrote: > The errcode is most likely wrong: > > ERRCODE_WRONG_OBJECT_TYPE -> ERRCODE_FEATURE_NOT_SUPPORTED > > At least it is inconsistent with an equivalent check in parse_utilcmd.c: > > if (cxt->ispartitioned && constraint->is_no_inherit) > ereport(ERROR, > errcode(ERRCODE_FEATURE_NOT_SUPPORTED), > errmsg("not-null constraints on partitioned tables cannot be > NO INHERIT")); Thanks, that was a copy pasto. Version 2 is attached. -- Andreas Karlsson Percona Attachments: [text/x-patch] v2-0001-Prevent-setting-NO-INHERIT-on-paritioned-not-null.patch (3.4K, 2-v2-0001-Prevent-setting-NO-INHERIT-on-paritioned-not-null.patch) download | inline diff: From cfeb17aed0940eb40b8ac5c024899efb4cc78cb4 Mon Sep 17 00:00:00 2001 From: Andreas Karlsson <[email protected]> Date: Wed, 20 May 2026 21:00:41 +0200 Subject: [PATCH v2] Prevent setting NO INHERIT on paritioned not-null constraints There is a check which prevents NOT NULL contraints from being created with NO INHEIRT on partitioned tables but the same check against it is missing for ALTER TABLE ... ALTER CONSTRAINT which clearly is an oversight. So this commit just adds the missing check. --- 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 92b0f38c353..1e0bacf85fc 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12367,6 +12367,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 728ef2fd17e..e54fec7fb57 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -1130,6 +1130,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 483c1e98372..dc133b124bb 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -757,6 +757,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); -- 2.47.3 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Prevent setting NO INHERIT on partitioned not-null constraints @ 2026-05-22 01:10 Fujii Masao <[email protected]> parent: Andreas Karlsson <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Fujii Masao @ 2026-05-22 01:10 UTC (permalink / raw) To: Andreas Karlsson <[email protected]>; +Cc: Jim Jones <[email protected]>; PostgreSQL Hackers <[email protected]> On Fri, May 22, 2026 at 6:22 AM Andreas Karlsson <[email protected]> wrote: > > On 5/21/26 5:01 PM, Jim Jones wrote: > > The errcode is most likely wrong: > > > > ERRCODE_WRONG_OBJECT_TYPE -> ERRCODE_FEATURE_NOT_SUPPORTED > > > > At least it is inconsistent with an equivalent check in parse_utilcmd.c: > > > > if (cxt->ispartitioned && constraint->is_no_inherit) > > ereport(ERROR, > > errcode(ERRCODE_FEATURE_NOT_SUPPORTED), > > errmsg("not-null constraints on partitioned tables cannot be > > NO INHERIT")); > > Thanks, that was a copy pasto. Version 2 is attached. Thanks for the patch! It looks good to me. Barring any objections, I will commit it. This should be backpatched to v18, where ALTER TABLE ... ALTER CONSTRAINT ... [NO] INHERIT was added. Right? Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Prevent setting NO INHERIT on partitioned not-null constraints @ 2026-05-22 08:10 Andreas Karlsson <[email protected]> parent: Fujii Masao <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andreas Karlsson @ 2026-05-22 08:10 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Jim Jones <[email protected]>; PostgreSQL Hackers <[email protected]> On 5/22/26 3:10 AM, Fujii Masao wrote: > This should be backpatched to v18, where ALTER TABLE ... ALTER > CONSTRAINT ... [NO] INHERIT > was added. Right? Yes, as this is a bug I think it should be backpatched to 18. -- Andreas Karlsson Percona ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Prevent setting NO INHERIT on partitioned not-null constraints @ 2026-05-22 15:02 Fujii Masao <[email protected]> parent: Andreas Karlsson <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Fujii Masao @ 2026-05-22 15:02 UTC (permalink / raw) To: Andreas Karlsson <[email protected]>; +Cc: Jim Jones <[email protected]>; PostgreSQL Hackers <[email protected]> On Fri, May 22, 2026 at 5:10 PM Andreas Karlsson <[email protected]> wrote: > > On 5/22/26 3:10 AM, Fujii Masao wrote: > > This should be backpatched to v18, where ALTER TABLE ... ALTER > > CONSTRAINT ... [NO] INHERIT > > was added. Right? > Yes, as this is a bug I think it should be backpatched to 18. I've pushed the patch. Thanks! Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-05-22 15:02 UTC | newest] Thread overview: 6+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-05-20 19:25 Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <[email protected]> 2026-05-21 15:01 ` Jim Jones <[email protected]> 2026-05-21 21:22 ` Andreas Karlsson <[email protected]> 2026-05-22 01:10 ` Fujii Masao <[email protected]> 2026-05-22 08:10 ` Andreas Karlsson <[email protected]> 2026-05-22 15:02 ` Fujii Masao <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox