public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Karlsson <andreas@proxel.se>
To: Jim Jones <jim.jones@uni-muenster.de>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Prevent setting NO INHERIT on partitioned not-null constraints
Date: Thu, 21 May 2026 23:22:31 +0200
Message-ID: <7299e1c3-9cf5-4e72-94ae-1771d88fb0a3@proxel.se> (raw)
In-Reply-To: <24bfd6b7-d566-490c-812f-c61c9eb2ef69@uni-muenster.de>
References: <ecc985ad-6ec1-4094-a315-317943ca5f3f@proxel.se>
<24bfd6b7-d566-490c-812f-c61c9eb2ef69@uni-muenster.de>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andreas@proxel.se, jim.jones@uni-muenster.de, pgsql-hackers@lists.postgresql.org
Subject: Re: Prevent setting NO INHERIT on partitioned not-null constraints
In-Reply-To: <7299e1c3-9cf5-4e72-94ae-1771d88fb0a3@proxel.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox