From 92363783fa8bdd23e4a34d147a173354d7025e67 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 8 May 2024 18:38:20 +0200 Subject: [PATCH 6/6] Don't accept NO INHERIT changes to INHERIT in normal mode --- src/backend/catalog/heap.c | 20 +++------- src/test/regress/expected/constraints.out | 48 ++++------------------- src/test/regress/expected/inherit.out | 13 ++---- src/test/regress/sql/constraints.sql | 22 ++--------- src/test/regress/sql/inherit.sql | 6 +-- 5 files changed, 21 insertions(+), 88 deletions(-) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 922ba79ac2..38df163edb 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2549,23 +2549,15 @@ AddRelationNewConstraints(Relation rel, /* * If the column already has an inheritable not-null constraint, - * we need only adjust its coninhcount and we're done. In certain - * cases (see below), if the constraint is there but marked NO - * INHERIT, then we mark it as no longer such and coninhcount - * updated, plus we must also recurse to the children (if any) to - * add the constraint there. - * - * We only allow the inheritability status to change during binary - * upgrade (where it's used to add the not-null constraints for - * children of tables with primary keys), or when we're recursing - * processing a table down an inheritance hierarchy; directly - * allowing a constraint to change from NO INHERIT to INHERIT - * during ALTER TABLE ADD CONSTRAINT would be far too surprising - * behavior. + * we need only adjust its coninhcount and we're done. In binary + * upgrade, if the constraint is there but marked NO INHERIT, then + * we mark it as no longer such and coninhcount updated, plus we + * must also recurse to the children (if any) to add the + * constraint there. */ existing = AdjustNotNullInheritance1(RelationGetRelid(rel), colnum, cdef->inhcount, cdef->is_no_inherit, - IsBinaryUpgrade || allow_merge); + IsBinaryUpgrade); if (existing == 1) continue; /* all done! */ else if (existing == -1) diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index ec7c9e53d0..0ffe4fd3f7 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -321,29 +321,22 @@ ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; Inherits: atacc1 DROP TABLE ATACC1, ATACC2; --- no can do +-- partitioned tables don't accept NO INHERIT constraints 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 --- overridding a no-inherit constraint with an inheritable one +-- 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); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -NOTICE: merging column "a" with inherited definition -INSERT INTO ATACC3 VALUES (null); -- make sure we scan atacc3 ALTER TABLE ATACC2 INHERIT ATACC1; ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -ERROR: column "a" of relation "atacc3" contains null values -DELETE FROM ATACC3; -ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -\d+ ATACC[123] +ERROR: cannot change NO INHERIT status of NOT NULL constraint "a_is_not_null" on relation "atacc2" +\d+ ATACC[12] Table "public.atacc1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "ditto" NOT NULL "a" + a | integer | | | | plain | | Child tables: atacc2 Table "public.atacc2" @@ -351,37 +344,10 @@ Child tables: atacc2 --------+---------+-----------+----------+---------+---------+--------------+------------- a | integer | | not null | | plain | | Not-null constraints: - "a_is_not_null" NOT NULL "a" (local, inherited) + "a_is_not_null" NOT NULL "a" NO INHERIT Inherits: atacc1 -Child tables: atacc3 - Table "public.atacc3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "ditto" NOT NULL "a" (inherited) -Inherits: atacc2 - -ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null; -ALTER TABLE ATACC1 DROP CONSTRAINT ditto; -\d+ ATACC3 - Table "public.atacc3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Inherits: atacc2 - -DROP TABLE ATACC1, ATACC2, ATACC3; --- The same cannot be achieved this way -CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); -CREATE TABLE ATACC1 (a int, CONSTRAINT ditto NOT NULL a); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -NOTICE: merging column "a" with inherited definition -ALTER TABLE ATACC2 INHERIT ATACC1; -ERROR: cannot add NOT NULL constraint to column "a" of relation "atacc2" with inheritance children -DETAIL: Existing constraint "a_is_not_null" is marked NO INHERIT. -DROP TABLE ATACC1, ATACC2, ATACC3; +DROP TABLE ATACC1, ATACC2; -- -- Check constraints on INSERT INTO -- diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index a621db0aa3..1d40468015 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -2290,21 +2290,14 @@ NOTICE: drop cascades to table inh_nn_child CREATE TABLE inh_nn_lvl1 (a int); CREATE TABLE inh_nn_lvl2 () INHERITS (inh_nn_lvl1); CREATE TABLE inh_nn_lvl3 (CONSTRAINT foo NOT NULL a NO INHERIT) INHERITS (inh_nn_lvl2); -CREATE TABLE inh_nn_lvl4 () INHERITS (inh_nn_lvl3); -CREATE TABLE inh_nn_lvl5 () INHERITS (inh_nn_lvl4); -INSERT INTO inh_nn_lvl2 VALUES (NULL); ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); -ERROR: column "a" of relation "inh_nn_lvl2" contains null values -DELETE FROM inh_nn_lvl2; -INSERT INTO inh_nn_lvl5 VALUES (NULL); +ERROR: cannot change NO INHERIT status of NOT NULL constraint "foo" on relation "inh_nn_lvl3" +ALTER TABLE inh_nn_lvl3 DROP CONSTRAINT foo; ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); -ERROR: column "a" of relation "inh_nn_lvl5" contains null values DROP TABLE inh_nn_lvl1 CASCADE; -NOTICE: drop cascades to 4 other objects +NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table inh_nn_lvl2 drop cascades to table inh_nn_lvl3 -drop cascades to table inh_nn_lvl4 -drop cascades to table inh_nn_lvl5 -- -- test inherit/deinherit -- diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index e753b8c345..f8ca855e8b 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -212,31 +212,17 @@ ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; \d+ ATACC2 DROP TABLE ATACC1, ATACC2; --- no can do +-- partitioned tables don't accept NO INHERIT constraints 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); --- overridding a no-inherit constraint with an inheritable one +-- 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); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -INSERT INTO ATACC3 VALUES (null); -- make sure we scan atacc3 ALTER TABLE ATACC2 INHERIT ATACC1; ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -DELETE FROM ATACC3; -ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -\d+ ATACC[123] -ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null; -ALTER TABLE ATACC1 DROP CONSTRAINT ditto; -\d+ ATACC3 -DROP TABLE ATACC1, ATACC2, ATACC3; - --- The same cannot be achieved this way -CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); -CREATE TABLE ATACC1 (a int, CONSTRAINT ditto NOT NULL a); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -ALTER TABLE ATACC2 INHERIT ATACC1; -DROP TABLE ATACC1, ATACC2, ATACC3; +\d+ ATACC[12] +DROP TABLE ATACC1, ATACC2; -- -- Check constraints on INSERT INTO diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 2205e59aff..d37d2d3ce8 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -855,12 +855,8 @@ DROP TABLE inh_nn_parent cascade; CREATE TABLE inh_nn_lvl1 (a int); CREATE TABLE inh_nn_lvl2 () INHERITS (inh_nn_lvl1); CREATE TABLE inh_nn_lvl3 (CONSTRAINT foo NOT NULL a NO INHERIT) INHERITS (inh_nn_lvl2); -CREATE TABLE inh_nn_lvl4 () INHERITS (inh_nn_lvl3); -CREATE TABLE inh_nn_lvl5 () INHERITS (inh_nn_lvl4); -INSERT INTO inh_nn_lvl2 VALUES (NULL); ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); -DELETE FROM inh_nn_lvl2; -INSERT INTO inh_nn_lvl5 VALUES (NULL); +ALTER TABLE inh_nn_lvl3 DROP CONSTRAINT foo; ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); DROP TABLE inh_nn_lvl1 CASCADE; -- 2.39.2