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]