Add ALTER TABLE ... ALTER CONSTRAINT ... SET [NO] INHERIT

This allows to redefine an existing non-inheritable constraint to be
inheritable, which allows to straighten up situations with NO INHERIT
constraints so that thay can become normal constraints without having to
re-verify existing data.  For existing inheritance children this may
require creating additional constraints, if they don't exist already.

It also allows to do the opposite, if only for symmetry.

Author: Suraj Kharage <suraj.kharage@enterprisedb.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CAF1DzPVfOW6Kk=7SSh7LbneQDJWh=PbJrEC_Wkzc24tHOyQWGg@mail.gmail.com

2 files changed, 207 insertions, 3 deletions

diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 420b6ae5996..e671975a281 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2505,8 +2505,10 @@ CREATE TABLE inh_nn3 (a int not null, b int,  not null a no inherit);
 ERROR:  conflicting NO INHERIT declaration for not-null constraint on column "a"
 CREATE TABLE inh_nn4 (a int not null no inherit, b int,  not null a);
 ERROR:  conflicting NO INHERIT declaration for not-null constraint on column "a"
-DROP TABLE inh_nn1, inh_nn2, inh_nn3, inh_nn4;
-ERROR:  table "inh_nn2" does not exist
+DROP TABLE IF EXISTS inh_nn1, inh_nn2, inh_nn3, inh_nn4;
+NOTICE:  table "inh_nn2" does not exist, skipping
+NOTICE:  table "inh_nn3" does not exist, skipping
+NOTICE:  table "inh_nn4" does not exist, skipping
 --
 -- test inherit/deinherit
 --
@@ -2744,6 +2746,131 @@ NOTICE:  drop cascades to 2 other objects
 DETAIL:  drop cascades to table inh_multiparent
 drop cascades to table inh_multiparent2
 --
+-- Test ALTER CONSTRAINT SET [NO] INHERIT
+--
+create table inh_nn1 (f1 int not null no inherit);
+create table inh_nn2 (f2 text, f3 int, f1 int);
+alter table inh_nn2 inherit inh_nn1;
+create table inh_nn3 (f4 float) inherits (inh_nn2);
+create table inh_nn4 (f5 int, f4 float, f2 text, f3 int, f1 int);
+alter table inh_nn4 inherit inh_nn2, inherit inh_nn1, inherit inh_nn3;
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+ conrelid |       conname       | conkey | coninhcount | conislocal | connoinherit 
+----------+---------------------+--------+-------------+------------+--------------
+ inh_nn1  | inh_nn1_f1_not_null | {1}    |           0 | t          | f
+ inh_nn2  | inh_nn1_f1_not_null | {3}    |           1 | f          | f
+ inh_nn3  | inh_nn1_f1_not_null | {3}    |           1 | f          | f
+ inh_nn4  | inh_nn1_f1_not_null | {5}    |           3 | f          | f
+(4 rows)
+
+-- ALTER CONSTRAINT SET NO INHERIT should work on top-level constraints
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set no inherit;
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+ conrelid |       conname       | conkey | coninhcount | conislocal | connoinherit 
+----------+---------------------+--------+-------------+------------+--------------
+ inh_nn1  | inh_nn1_f1_not_null | {1}    |           0 | t          | t
+ inh_nn2  | inh_nn1_f1_not_null | {3}    |           0 | t          | f
+ inh_nn3  | inh_nn1_f1_not_null | {3}    |           1 | f          | f
+ inh_nn4  | inh_nn1_f1_not_null | {5}    |           2 | t          | f
+(4 rows)
+
+-- A constraint that's NO INHERIT can be dropped without damaging children
+alter table inh_nn1 drop constraint inh_nn1_f1_not_null;
+select conrelid::regclass, conname, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+ conrelid |       conname       | coninhcount | conislocal | connoinherit 
+----------+---------------------+-------------+------------+--------------
+ inh_nn2  | inh_nn1_f1_not_null |           0 | t          | f
+ inh_nn3  | inh_nn1_f1_not_null |           1 | f          | f
+ inh_nn4  | inh_nn1_f1_not_null |           2 | t          | f
+(3 rows)
+
+drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4;
+-- Test inherit constraint and make sure it validates.
+create table inh_nn1 (f1 int not null no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+insert into inh_nn2 values(NULL, 'sample', 1);
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
+ERROR:  column "f1" of relation "inh_nn2" contains null values
+delete from inh_nn2;
+create table inh_nn3 () inherits (inh_nn2);
+create table inh_nn4 () inherits (inh_nn1, inh_nn2);
+NOTICE:  merging multiple inherited definitions of column "f1"
+alter table inh_nn1	-- test multicommand alter table while at it
+   alter constraint inh_nn1_f1_not_null set inherit,
+   alter constraint inh_nn1_f1_not_null set no inherit;
+select conrelid::regclass, conname, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+ conrelid |       conname       | coninhcount | conislocal | connoinherit 
+----------+---------------------+-------------+------------+--------------
+ inh_nn1  | inh_nn1_f1_not_null |           0 | t          | t
+ inh_nn2  | inh_nn1_f1_not_null |           0 | t          | f
+ inh_nn3  | inh_nn1_f1_not_null |           1 | f          | f
+ inh_nn4  | inh_nn1_f1_not_null |           1 | t          | f
+(4 rows)
+
+drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4;
+-- Test not null inherit constraint which already exists on child table.
+create table inh_nn1 (f1 int not null no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 (f4 float, constraint nn3_f1 not null f1 no inherit) inherits (inh_nn1, inh_nn2);
+NOTICE:  merging multiple inherited definitions of column "f1"
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3')
+ order by 2, 1;
+ conrelid |       conname       | conkey | coninhcount | conislocal | connoinherit 
+----------+---------------------+--------+-------------+------------+--------------
+ inh_nn1  | inh_nn1_f1_not_null | {1}    |           0 | t          | t
+ inh_nn3  | nn3_f1              | {1}    |           0 | t          | t
+(2 rows)
+
+-- error: inh_nn3 has an incompatible NO INHERIT constraint
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
+ERROR:  cannot change NO INHERIT status of NOT NULL constraint "nn3_f1" on relation "inh_nn3"
+alter table inh_nn3 alter constraint nn3_f1 set inherit;
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit; -- now it works
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3')
+ order by 2, 1;
+ conrelid |       conname       | conkey | coninhcount | conislocal | connoinherit 
+----------+---------------------+--------+-------------+------------+--------------
+ inh_nn1  | inh_nn1_f1_not_null | {1}    |           0 | t          | f
+ inh_nn2  | inh_nn1_f1_not_null | {1}    |           1 | f          | f
+ inh_nn3  | nn3_f1              | {1}    |           2 | t          | f
+(3 rows)
+
+drop table inh_nn1, inh_nn2, inh_nn3;
+-- Negative scenarios for alter constraint .. set inherit.
+create table inh_nn1 (f1 int check(f1 > 5) primary key references inh_nn1, f2 int not null);
+-- constraints other than not-null are not supported
+alter table inh_nn1 alter constraint inh_nn1_f1_check set inherit;
+ERROR:  constraint "inh_nn1_f1_check" of relation "inh_nn1" is not a not-null constraint
+alter table inh_nn1 alter constraint inh_nn1_pkey set inherit;
+ERROR:  constraint "inh_nn1_pkey" of relation "inh_nn1" is not a not-null constraint
+alter table inh_nn1 alter constraint inh_nn1_f1_fkey set inherit;
+ERROR:  constraint "inh_nn1_f1_fkey" of relation "inh_nn1" is not a not-null constraint
+-- try to drop a nonexistant constraint
+alter table inh_nn1 alter constraint foo set inherit;
+ERROR:  constraint "foo" of relation "inh_nn1" does not exist
+-- Can't modify inheritability of inherited constraints
+create table inh_nn2 () inherits (inh_nn1);
+alter table inh_nn2 alter constraint inh_nn1_f2_not_null set no inherit;
+ERROR:  cannot alter inherited constraint "inh_nn1_f2_not_null" on relation "inh_nn2"
+drop table inh_nn1, inh_nn2;
+--
 -- Mixed ownership inheritance tree
 --
 create role regress_alice;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 30fba16231c..4e73c70495c 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -957,7 +957,7 @@ CREATE TABLE inh_nn1 (a int not null);
 CREATE TABLE inh_nn2 (a int not null no inherit) INHERITS (inh_nn1);
 CREATE TABLE inh_nn3 (a int not null, b int,  not null a no inherit);
 CREATE TABLE inh_nn4 (a int not null no inherit, b int,  not null a);
-DROP TABLE inh_nn1, inh_nn2, inh_nn3, inh_nn4;
+DROP TABLE IF EXISTS inh_nn1, inh_nn2, inh_nn3, inh_nn4;
 
 --
 -- test inherit/deinherit
@@ -1091,6 +1091,83 @@ select conrelid::regclass, contype, conname,
 drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
 
 --
+-- Test ALTER CONSTRAINT SET [NO] INHERIT
+--
+create table inh_nn1 (f1 int not null no inherit);
+create table inh_nn2 (f2 text, f3 int, f1 int);
+alter table inh_nn2 inherit inh_nn1;
+create table inh_nn3 (f4 float) inherits (inh_nn2);
+create table inh_nn4 (f5 int, f4 float, f2 text, f3 int, f1 int);
+alter table inh_nn4 inherit inh_nn2, inherit inh_nn1, inherit inh_nn3;
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+-- ALTER CONSTRAINT SET NO INHERIT should work on top-level constraints
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set no inherit;
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+-- A constraint that's NO INHERIT can be dropped without damaging children
+alter table inh_nn1 drop constraint inh_nn1_f1_not_null;
+select conrelid::regclass, conname, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4;
+
+-- Test inherit constraint and make sure it validates.
+create table inh_nn1 (f1 int not null no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+insert into inh_nn2 values(NULL, 'sample', 1);
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
+delete from inh_nn2;
+create table inh_nn3 () inherits (inh_nn2);
+create table inh_nn4 () inherits (inh_nn1, inh_nn2);
+alter table inh_nn1	-- test multicommand alter table while at it
+   alter constraint inh_nn1_f1_not_null set inherit,
+   alter constraint inh_nn1_f1_not_null set no inherit;
+select conrelid::regclass, conname, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4')
+ order by 2, 1;
+drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4;
+
+-- Test not null inherit constraint which already exists on child table.
+create table inh_nn1 (f1 int not null no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 (f4 float, constraint nn3_f1 not null f1 no inherit) inherits (inh_nn1, inh_nn2);
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3')
+ order by 2, 1;
+-- error: inh_nn3 has an incompatible NO INHERIT constraint
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit;
+alter table inh_nn3 alter constraint nn3_f1 set inherit;
+alter table inh_nn1 alter constraint inh_nn1_f1_not_null set inherit; -- now it works
+select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3')
+ order by 2, 1;
+drop table inh_nn1, inh_nn2, inh_nn3;
+
+-- Negative scenarios for alter constraint .. set inherit.
+create table inh_nn1 (f1 int check(f1 > 5) primary key references inh_nn1, f2 int not null);
+-- constraints other than not-null are not supported
+alter table inh_nn1 alter constraint inh_nn1_f1_check set inherit;
+alter table inh_nn1 alter constraint inh_nn1_pkey set inherit;
+alter table inh_nn1 alter constraint inh_nn1_f1_fkey set inherit;
+-- try to drop a nonexistant constraint
+alter table inh_nn1 alter constraint foo set inherit;
+-- Can't modify inheritability of inherited constraints
+create table inh_nn2 () inherits (inh_nn1);
+alter table inh_nn2 alter constraint inh_nn1_f2_not_null set no inherit;
+
+drop table inh_nn1, inh_nn2;
+
+--
 -- Mixed ownership inheritance tree
 --
 create role regress_alice;