postgres.git / summary / log / commit / refs
commit 35f60e5ceab72b96ef3eb8cf68d45934f2d6479e
Author: David Rowley <drowley@postgresql.org>
Date: Tue Oct 27 06:47:09 2026 +0000
First released in: 18.7, 19.0
Disallow NOT NULL NO INHERIT constraints on partitioned tables
This was blocked during normal CREATE TABLE, but cases such as CREATE
TABLE (LIKE) would copy the source table's NOT NULL NO INHERIT constraints
and apply them to the partitioned table being created. This could mean
that partitions didn't correctly receive the NOT NULL constraint when they
were created, thus could contain NULL values.
cacef1722 added a test to ensure CREATE TABLE (LIKE) PARTITION BY fails
when the source table has any CHECK NO INHERIT constraints. Here we do
the same for NOT NULL constraints.
It's probably unlikely that people have commonly done this, but the
release notes may wish to note that people should check if this has
happened to them. The following query can used each database to identify
problematic constraints:
SELECT conrelid::regclass, conname, connoinherit
FROM pg_constraint
WHERE contype = 'n'
AND connoinherit = true
AND conrelid IN (SELECT oid FROM pg_class WHERE relkind = 'p');
If no results are shown, the queried database isn't affected.
To resolve these issues, unwanted constraints can simply be dropped from
the partitioned table with:
ALTER TABLE ... DROP CONSTRAINT ...;
To remove the NO INHERIT flag, use:
ALTER TABLE ... ALTER CONSTRAINT ... INHERIT;
This will fail if any partition contains a NULL value in the given
column. Finding the cause of failures is made more difficult by the
planner trusting that "WHERE col IS NULL" cannot be true because of the
presence of a NOT NULL constraint. Affected users may need to resort to
tricks like WHERE col+0 IS NULL to find these. If it was thought to be a
common problem, then we could adjust get_relation_notnullatts(), and the
equivalent functions on older branches to ignore constraints marked as NO
INHERIT on partitioned tables, but currently that would require an
expensive lookup to find the pg_constraint row.
Backpatch to v18 where NOT NULL NO INHERIT constraints were added by
14e87ffa5.
Bug: #19770
Reported-by: Ann C <anncalla@163.com>
Author: David Rowley <dgrowleyml@gmail.com>
Author: Nitin Motiani <nitinmotiani@google.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Nitin Motiani <nitinmotiani@google.com>
Discussion: https://postgr.es/m/19770-7c6e75fc30df7e06@postgresql.org
Discussion: https://postgr.es/m/CAH5HC96YCzOy+9JZLQ4n0sMchVnharTOXrr4sbYuh=_d+ffjAA@mail.gmail.com
Backpatch-through: 18
src/backend/catalog/heap.c | 10 ++++++++++
src/test/regress/expected/create_table_like.out | 21 +++++++++++++++++----
src/test/regress/sql/create_table_like.sql | 23 +++++++++++++++++++----
3 files changed, 46 insertions(+), 8 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 8f204c94e88..e58e298d38b 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2279,6 +2279,16 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
Assert(attnum > InvalidAttrNumber);
+ /*
+ * Partitioned tables do not contain any rows themselves, so a NO INHERIT
+ * constraint makes no sense.
+ */
+ if (is_no_inherit &&
+ rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("not-null constraints on partitioned tables cannot be NO INHERIT"));
+
constrOid =
CreateConstraintEntry(nnname,
RelationGetNamespace(rel),
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index feeb961fadd..b3637f22750 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -544,8 +544,11 @@ ROLLBACK;
DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE;
NOTICE: drop cascades to table inhe
-- LIKE must respect NO INHERIT property of constraints
-CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT, b int not null,
- c int not null no inherit);
+CREATE TABLE noinh_con_copy (
+ a int CONSTRAINT noinh_con_copy_a_check CHECK (a > 0) NO INHERIT,
+ b int not null,
+ c int not null no inherit
+);
COMMENT ON CONSTRAINT noinh_con_copy_b_not_null ON noinh_con_copy IS 'not null b';
COMMENT ON CONSTRAINT noinh_con_copy_c_not_null ON noinh_con_copy IS 'not null c no inherit';
CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS INCLUDING COMMENTS);
@@ -573,11 +576,21 @@ ORDER BY conname COLLATE "C";
noinh_con_copy_c_not_null | not null c no inherit
(2 rows)
--- fail, as partitioned tables don't allow NO INHERIT constraints
+-- ensure this fails due to the NOT NULL NO INHERIT constraint
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
+ PARTITION BY LIST (a);
+ERROR: not-null constraints on partitioned tables cannot be NO INHERIT
+ALTER TABLE noinh_con_copy ALTER COLUMN c DROP NOT NULL;
+-- ensure this fails due to the NO INHERIT CHECK constraint
CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
PARTITION BY LIST (a);
ERROR: cannot add NO INHERIT constraint to partitioned table "noinh_con_copy1_parted"
-DROP TABLE noinh_con_copy, noinh_con_copy1;
+-- drop the CHECK constraint and try again
+ALTER TABLE noinh_con_copy DROP CONSTRAINT noinh_con_copy_a_check;
+-- Ensure this creates the table
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
+ PARTITION BY LIST (a);
+DROP TABLE noinh_con_copy, noinh_con_copy1, noinh_con_copy1_parted;
/* LIKE with other relation kinds */
CREATE TABLE ctlt4 (a int, b text);
CREATE SEQUENCE ctlseq1;
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index d52a93ef131..5dd81f4eaf9 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -198,8 +198,11 @@ ROLLBACK;
DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE;
-- LIKE must respect NO INHERIT property of constraints
-CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT, b int not null,
- c int not null no inherit);
+CREATE TABLE noinh_con_copy (
+ a int CONSTRAINT noinh_con_copy_a_check CHECK (a > 0) NO INHERIT,
+ b int not null,
+ c int not null no inherit
+);
COMMENT ON CONSTRAINT noinh_con_copy_b_not_null ON noinh_con_copy IS 'not null b';
COMMENT ON CONSTRAINT noinh_con_copy_c_not_null ON noinh_con_copy IS 'not null c no inherit';
@@ -213,12 +216,24 @@ WHERE classoid = 'pg_constraint'::regclass
AND objoid = c.oid AND c.conrelid = 'noinh_con_copy1'::regclass
ORDER BY conname COLLATE "C";
--- fail, as partitioned tables don't allow NO INHERIT constraints
+-- ensure this fails due to the NOT NULL NO INHERIT constraint
CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
PARTITION BY LIST (a);
-DROP TABLE noinh_con_copy, noinh_con_copy1;
+ALTER TABLE noinh_con_copy ALTER COLUMN c DROP NOT NULL;
+-- ensure this fails due to the NO INHERIT CHECK constraint
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
+ PARTITION BY LIST (a);
+
+-- drop the CHECK constraint and try again
+ALTER TABLE noinh_con_copy DROP CONSTRAINT noinh_con_copy_a_check;
+
+-- Ensure this creates the table
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
+ PARTITION BY LIST (a);
+
+DROP TABLE noinh_con_copy, noinh_con_copy1, noinh_con_copy1_parted;
/* LIKE with other relation kinds */
[parent: b09b26ac8e2e]