public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedPrevent 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 <andreas@proxel.se>
2026-05-21 15:01 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Jim Jones <jim.jones@uni-muenster.de>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Karlsson @ 2026-05-20 19:25 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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 <andreas@proxel.se>
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-20 19:25 Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
@ 2026-05-21 15:01 ` Jim Jones <jim.jones@uni-muenster.de>
2026-05-21 21:22 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
0 siblings, 1 reply; 6+ messages in thread
From: Jim Jones @ 2026-05-21 15:01 UTC (permalink / raw)
To: Andreas Karlsson <andreas@proxel.se>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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-20 19:25 Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
2026-05-21 15:01 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Jim Jones <jim.jones@uni-muenster.de>
@ 2026-05-21 21:22 ` Andreas Karlsson <andreas@proxel.se>
2026-05-22 01:10 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Karlsson @ 2026-05-21 21:22 UTC (permalink / raw)
To: Jim Jones <jim.jones@uni-muenster.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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 <andreas@proxel.se>
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-20 19:25 Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
2026-05-21 15:01 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Jim Jones <jim.jones@uni-muenster.de>
2026-05-21 21:22 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
@ 2026-05-22 01:10 ` Fujii Masao <masao.fujii@gmail.com>
2026-05-22 08:10 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
0 siblings, 1 reply; 6+ messages in thread
From: Fujii Masao @ 2026-05-22 01:10 UTC (permalink / raw)
To: Andreas Karlsson <andreas@proxel.se>; +Cc: Jim Jones <jim.jones@uni-muenster.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, May 22, 2026 at 6:22 AM Andreas Karlsson <andreas@proxel.se> 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-20 19:25 Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
2026-05-21 15:01 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Jim Jones <jim.jones@uni-muenster.de>
2026-05-21 21:22 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
2026-05-22 01:10 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Fujii Masao <masao.fujii@gmail.com>
@ 2026-05-22 08:10 ` Andreas Karlsson <andreas@proxel.se>
2026-05-22 15:02 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Karlsson @ 2026-05-22 08:10 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Jim Jones <jim.jones@uni-muenster.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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-20 19:25 Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
2026-05-21 15:01 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Jim Jones <jim.jones@uni-muenster.de>
2026-05-21 21:22 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
2026-05-22 01:10 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Fujii Masao <masao.fujii@gmail.com>
2026-05-22 08:10 ` Re: Prevent setting NO INHERIT on partitioned not-null constraints Andreas Karlsson <andreas@proxel.se>
@ 2026-05-22 15:02 ` Fujii Masao <masao.fujii@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Fujii Masao @ 2026-05-22 15:02 UTC (permalink / raw)
To: Andreas Karlsson <andreas@proxel.se>; +Cc: Jim Jones <jim.jones@uni-muenster.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, May 22, 2026 at 5:10 PM Andreas Karlsson <andreas@proxel.se> 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 <andreas@proxel.se>
2026-05-21 15:01 ` Jim Jones <jim.jones@uni-muenster.de>
2026-05-21 21:22 ` Andreas Karlsson <andreas@proxel.se>
2026-05-22 01:10 ` Fujii Masao <masao.fujii@gmail.com>
2026-05-22 08:10 ` Andreas Karlsson <andreas@proxel.se>
2026-05-22 15:02 ` Fujii Masao <masao.fujii@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox