Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
Started by Suraj Kharageabout 1 year ago20 messages
Hi,
Upstream commit 14e87ffa5c543b5f30ead7413084c25f7735039f
<https://github.com/postgres/postgres/commit/14e87ffa5c543b5f30ead7413084c25f7735039f>
added the support for named NOT NULL constraints which are INHERIT by
default.
We can declare those as NO INHERIT which means those constraints will not
be inherited to child tables and after this state, we don't have the
functionality to change the state back to INHERIT.
This patch adds this support where named NOT NULL constraint defined as NO
INHERIT can be changed to INHERIT.
For this, introduced the new syntax something like -
ALTER TABLE <tabname> ALTER CONSTRAINT <constrname> INHERIT;
Once the not null constraints are altered to INHERIT from NO INHERIT,
recurse to all children and propagate the constraint if it doesn't exist.
Alvaro stated that allowing a not null constraint state to be modified from
INHERIT to NO INHERIT is going to be quite problematic because of the
number of weird cases to avoid, so for now that support is not added.
Please share your thoughts on the same.
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/>
Attachments:
v1-alter_not_null_constraint_to_inherit.patchapplication/x-patch; name=v1-alter_not_null_constraint_to_inherit.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 6098ebe..348ac38 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,7 +58,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
- ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -108,7 +108,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [INHERIT]
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -553,7 +553,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key and not null constraints may be
+ altered. Only Not null constraints can be modified to INHERIT from
+ NO INHERIT which creates constraints in all inherited childrens and
+ validates the same.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ccd9645..360fcdb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -389,7 +389,7 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved,
LOCKMODE lockmode);
-static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
+static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
Relation rel, HeapTuple contuple, List **otherrelids,
@@ -5400,7 +5400,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
lockmode);
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
- address = ATExecAlterConstraint(rel, cmd, false, false, lockmode);
+ address = ATExecAlterConstraint(wqueue, rel, cmd, false, false,
+ lockmode);
break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
address = ATExecValidateConstraint(wqueue, rel, cmd->name, cmd->recurse,
@@ -11622,8 +11623,8 @@ GetForeignKeyCheckTriggers(Relation trigrel,
* InvalidObjectAddress.
*/
static ObjectAddress
-ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
- bool recursing, LOCKMODE lockmode)
+ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
+ bool recurse, bool recursing, LOCKMODE lockmode)
{
Constraint *cmdcon;
Relation conrel;
@@ -11666,7 +11667,74 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
cmdcon->conname, RelationGetRelationName(rel))));
+ contuple = heap_copytuple(contuple);
+
currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ /* Not null constraint */
+ if (cmdcon->contype == CONSTR_NOTNULL)
+ {
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a not null constraint",
+ cmdcon->conname, RelationGetRelationName(rel))));
+
+ address = InvalidObjectAddress;
+
+ /* Return if constraint is already marked as INHERIT. */
+ if (!currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as false. */
+ currcon->connoinherit = false;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ CommandCounterIncrement();
+
+ ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, true, true, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
if (currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -11742,6 +11810,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
systable_endscan(scan);
+ heap_freetuple(contuple);
+
table_close(tgrel, RowExclusiveLock);
table_close(conrel, RowExclusiveLock);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 67eb963..24c50a1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2661,6 +2661,20 @@ alter_table_cmd:
NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT INHERIT*/
+ | ALTER CONSTRAINT name INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ Constraint *c = makeNode(Constraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->contype = CONSTR_NOTNULL;
+ c->conname = $3;
+ c->is_no_inherit = false;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index bb81f6d..74b6ea2 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2666,6 +2666,383 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Negative scenarios for alter constraint .. inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check inherit;
+ERROR: constraint "part1_f1_check" of relation "part1" is not a not null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table foo alter constraint part1_id_not_nul inherit;
+ERROR: constraint "part1_id_not_nul" of relation "foo" does not exist
+drop table part1;
+--
-- 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 f51c70d..ba0f997 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1046,6 +1046,118 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Negative scenarios for alter constraint .. inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table foo alter constraint part1_id_not_nul inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On Thu, Nov 14, 2024 at 12:02 AM Suraj Kharage <
suraj.kharage@enterprisedb.com> wrote:
Alvaro stated that allowing a not null constraint state to be modified
from INHERIT to NO INHERIT is going to be quite problematic because of the
number of weird cases to avoid, so for now that support is not added.
What's the reasoning behind that restriction? What are the weird cases?
--
Robert Haas
EDB: http://www.enterprisedb.com
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On Fri, Nov 15, 2024 at 11:15 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Nov 14, 2024 at 12:02 AM Suraj Kharage <suraj.kharage@enterprisedb.com> wrote:
Alvaro stated that allowing a not null constraint state to be modified from INHERIT to NO INHERIT is going to be quite problematic because of the number of weird cases to avoid, so for now that support is not added.
What's the reasoning behind that restriction? What are the weird cases?
current status:
drop table if exists idxpart,idxpart0,idxpart1 cascade;
create table idxpart (a int not null) partition by list (a);
create table idxpart0 (a int constraint foo not null no inherit);
alter table idxpart attach partition idxpart0 for values in (0,1);
ERROR: constraint "foo" conflicts with non-inherited constraint on
child table "idxpart0"
to make it attach to the partition, we need to drop and recreate the
not-null constraint "foo".
that would be very expensive, since recreate, we need to revalidate
the previous row is not null or not.
related post:
/messages/by-id/202410021219.bvjmxzdspif2@alvherre.pgsql
with
alter table idxpart0 alter constraint foo inherit;
then we can
alter table idxpart attach partition idxpart0 for values in (0,1);
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On Thu, Nov 14, 2024 at 1:02 PM Suraj Kharage
<suraj.kharage@enterprisedb.com> wrote:
Hi,
Upstream commit 14e87ffa5c543b5f30ead7413084c25f7735039f added the support for named NOT NULL constraints which are INHERIT by default.
We can declare those as NO INHERIT which means those constraints will not be inherited to child tables and after this state, we don't have the functionality to change the state back to INHERIT.This patch adds this support where named NOT NULL constraint defined as NO INHERIT can be changed to INHERIT.
For this, introduced the new syntax something like -ALTER TABLE <tabname> ALTER CONSTRAINT <constrname> INHERIT;
/* ALTER TABLE <name> ALTER CONSTRAINT INHERIT*/
| ALTER CONSTRAINT name INHERIT
{
AlterTableCmd *n = makeNode(AlterTableCmd);
Constraint *c = makeNode(Constraint);
n->subtype = AT_AlterConstraint;
n->def = (Node *) c;
c->contype = CONSTR_NOTNULL;
in gram.y, adding a comment saying this only supports not-null would be great.
comments " * Currently only works for Foreign Key constraints."
above ATExecAlterConstraint need change?
ATExecAlterConstraint
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a not null constraint",
+ cmdcon->conname, RelationGetRelationName(rel))));
the error message is not helpful?we should instead saying
ALTER TABLE <tabname> ALTER CONSTRAINT <constrname> INHERIT;
only support not-null constraint.
in ATExecSetNotNull we already have:
if (recurse)
{
List *children;
children = find_inheritance_children(RelationGetRelid(rel),
lockmode);
foreach_oid(childoid, children)
{
Relation childrel = table_open(childoid, NoLock);
CommandCounterIncrement();
ATExecSetNotNull(wqueue, childrel, conName, colName,
recurse, true, lockmode);
table_close(childrel, NoLock);
}
}
so we don't need another CommandCounterIncrement()
in the `foreach_oid(childoid, children)` loop?
maybe we need a CommandCounterIncrement() for
+ /* Update the constraint tuple and mark connoinherit as false. */
+ currcon->connoinherit = false;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table foo alter constraint part1_id_not_nul inherit;
+ERROR: constraint "part1_id_not_nul" of relation "foo" does not exist
+drop table part1;
i think you mean:
+alter table part1 alter constraint foo inherit;Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/>
On Tue, Nov 19, 2024 at 6:52 PM jian he <jian.universality@gmail.com> wrote:
On Fri, Nov 15, 2024 at 11:15 AM Robert Haas <robertmhaas@gmail.com>
wrote:On Thu, Nov 14, 2024 at 12:02 AM Suraj Kharage <
suraj.kharage@enterprisedb.com> wrote:
Alvaro stated that allowing a not null constraint state to be modified
from INHERIT to NO INHERIT is going to be quite problematic because of the
number of weird cases to avoid, so for now that support is not added.What's the reasoning behind that restriction? What are the weird cases?
current status:
drop table if exists idxpart,idxpart0,idxpart1 cascade;
create table idxpart (a int not null) partition by list (a);
create table idxpart0 (a int constraint foo not null no inherit);alter table idxpart attach partition idxpart0 for values in (0,1);
ERROR: constraint "foo" conflicts with non-inherited constraint on
child table "idxpart0"to make it attach to the partition, we need to drop and recreate the
not-null constraint "foo".
that would be very expensive, since recreate, we need to revalidate
the previous row is not null or not.
related post:
Right.
Another case which needs conclusion is -
When changing from INHERIT to NO INHERIT, we need to walk all children and
decrement coninhcount for the corresponding constraint. If a constraint in
one child reaches zero, should we drop it? not sure. If we do, make sure
to reset the corresponding attnotnull bit too. We could decide not to drop
the constraint, in which case you don’t need to reset attnotnull.
Show quoted text
with
alter table idxpart0 alter constraint foo inherit;then we can
alter table idxpart attach partition idxpart0 for values in (0,1);
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
Thanks for the review comments.
On Wed, Nov 20, 2024 at 9:13 AM jian he <jian.universality@gmail.com> wrote:
On Thu, Nov 14, 2024 at 1:02 PM Suraj Kharage
<suraj.kharage@enterprisedb.com> wrote:Hi,
Upstream commit 14e87ffa5c543b5f30ead7413084c25f7735039f added the
support for named NOT NULL constraints which are INHERIT by default.
We can declare those as NO INHERIT which means those constraints will
not be inherited to child tables and after this state, we don't have the
functionality to change the state back to INHERIT.This patch adds this support where named NOT NULL constraint defined as
NO INHERIT can be changed to INHERIT.
For this, introduced the new syntax something like -
ALTER TABLE <tabname> ALTER CONSTRAINT <constrname> INHERIT;
/* ALTER TABLE <name> ALTER CONSTRAINT INHERIT*/
| ALTER CONSTRAINT name INHERIT
{
AlterTableCmd *n = makeNode(AlterTableCmd);
Constraint *c = makeNode(Constraint);n->subtype = AT_AlterConstraint;
n->def = (Node *) c;
c->contype = CONSTR_NOTNULL;in gram.y, adding a comment saying this only supports not-null would be
great.
Fixed.
comments " * Currently only works for Foreign Key constraints."
above ATExecAlterConstraint need change?
Modified the comment.
ATExecAlterConstraint + if (currcon->contype != CONSTRAINT_NOTNULL) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("constraint \"%s\" of relation \"%s\" is not a not null constraint", + cmdcon->conname, RelationGetRelationName(rel)))); the error message is not helpful?we should instead saying
ALTER TABLE <tabname> ALTER CONSTRAINT <constrname> INHERIT;
only support not-null constraint.
Modified as per your suggestion.
in ATExecSetNotNull we already have:
if (recurse)
{
List *children;
children = find_inheritance_children(RelationGetRelid(rel),
lockmode);
foreach_oid(childoid, children)
{
Relation childrel = table_open(childoid, NoLock);
CommandCounterIncrement();
ATExecSetNotNull(wqueue, childrel, conName, colName,
recurse, true, lockmode);
table_close(childrel, NoLock);
}
}
so we don't need another CommandCounterIncrement()
in the `foreach_oid(childoid, children)` loop?
I have added that conditional to avoid tuple already updated by self error.
ATExecSetNotNull() is updating tuple and its coninhcount if a constraint
already exists.
Since we have a recursive call to all childrens
from ATExecAlterConstraint(), the recursive call to children doesn't go
through CommandCounterIncrement().
maybe we need a CommandCounterIncrement() for + /* Update the constraint tuple and mark connoinherit as false. */ + currcon->connoinherit = false; + + CatalogTupleUpdate(conrel, &contuple->t_self, contuple); + ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
Added.
+-- error out when provided not null constarint does not exists. +create table part1(f1 int not null no inherit); +alter table foo alter constraint part1_id_not_nul inherit; +ERROR: constraint "part1_id_not_nul" of relation "foo" does not exist +drop table part1; i think you mean: +alter table part1 alter constraint foo inherit;
Fixed.
Please find the attached v2 patch.
Attachments:
v2-alter_not_null_constraint_to_inherit.patchapplication/octet-stream; name=v2-alter_not_null_constraint_to_inherit.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c8f7ab7..a1e1104 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,7 +58,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
- ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -108,7 +108,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [INHERIT]
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -553,7 +553,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key and not null constraints may be
+ altered. Only Not null constraints can be modified to INHERIT from
+ NO INHERIT which creates constraints in all inherited childrens and
+ validates the same.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1af2e2b..593b428 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -389,7 +389,7 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved,
LOCKMODE lockmode);
-static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
+static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
Relation rel, HeapTuple contuple, List **otherrelids,
@@ -5400,7 +5400,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
lockmode);
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
- address = ATExecAlterConstraint(rel, cmd, false, false, lockmode);
+ address = ATExecAlterConstraint(wqueue, rel, cmd, false, false,
+ lockmode);
break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
address = ATExecValidateConstraint(wqueue, rel, cmd->name, cmd->recurse,
@@ -11664,14 +11665,14 @@ GetForeignKeyCheckTriggers(Relation trigrel,
*
* Update the attributes of a constraint.
*
- * Currently only works for Foreign Key constraints.
+ * Currently only works for Foreign Key and not null constraints.
*
* If the constraint is modified, returns its address; otherwise, return
* InvalidObjectAddress.
*/
static ObjectAddress
-ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
- bool recursing, LOCKMODE lockmode)
+ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
+ bool recurse, bool recursing, LOCKMODE lockmode)
{
Constraint *cmdcon;
Relation conrel;
@@ -11714,7 +11715,77 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
cmdcon->conname, RelationGetRelationName(rel))));
+ contuple = heap_copytuple(contuple);
+
currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ /* Not null constraint */
+ if (cmdcon->contype == CONSTR_NOTNULL)
+ {
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" INHERIT only supports not null constraint",
+ RelationGetRelationName(rel), cmdcon->conname)));
+
+ address = InvalidObjectAddress;
+
+ /* Return if constraint is already marked as INHERIT. */
+ if (!currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as false. */
+ currcon->connoinherit = false;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+ CommandCounterIncrement();
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+ ObjectAddress addr;
+
+ addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, true, true, lockmode);
+ if (OidIsValid(addr.objectId))
+ CommandCounterIncrement();
+
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
if (currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -11790,6 +11861,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
systable_endscan(scan);
+ heap_freetuple(contuple);
+
table_close(tgrel, RowExclusiveLock);
table_close(conrel, RowExclusiveLock);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 67eb963..aa0fac8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2661,6 +2661,23 @@ alter_table_cmd:
NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /*
+ * ALTER TABLE <name> ALTER CONSTRAINT INHERIT, supports only
+ * not null constraint.
+ */
+ | ALTER CONSTRAINT name INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ Constraint *c = makeNode(Constraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->contype = CONSTR_NOTNULL;
+ c->conname = $3;
+ c->is_no_inherit = false;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index bb81f6d..58df1f3 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2666,6 +2666,383 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Negative scenarios for alter constraint .. inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check inherit;
+ERROR: ALTER TABLE "part1" ALTER CONSTRAINT "part1_f1_check" INHERIT only supports not null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo inherit;
+ERROR: constraint "foo" of relation "part1" does not exist
+drop table part1;
+--
-- 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 f51c70d..a2b7ece 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1046,6 +1046,118 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Negative scenarios for alter constraint .. inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On Tue, Nov 19, 2024 at 8:22 AM jian he <jian.universality@gmail.com> wrote:
current status:
drop table if exists idxpart,idxpart0,idxpart1 cascade;
create table idxpart (a int not null) partition by list (a);
create table idxpart0 (a int constraint foo not null no inherit);alter table idxpart attach partition idxpart0 for values in (0,1);
ERROR: constraint "foo" conflicts with non-inherited constraint on
child table "idxpart0"to make it attach to the partition, we need to drop and recreate the
not-null constraint "foo".
that would be very expensive, since recreate, we need to revalidate
the previous row is not null or not.
In a simple implementation of ALTER TABLE this would be true, but I
don't see why it should need to be true in ours. It should be possible
to notice that there's an existing NOT NULL constraint and use that as
evidence that the new one can be added without needing to revalidate
the table contents. ALTER TABLE does similar things already. For
instance, TryReuseIndex() can attempt to attach an existing index file
to a new index definition without rebuilding it; TryReuseForeignKey
can attempt to re-add a foreign key constraint without needing to
revalidate it. But even more to the point, ATAddCheckNNConstraint and
MergeWithExistingConstraint know about merging a newly-added
constraint with a preexisting one without needing to revalidate the
table.
--
Robert Haas
EDB: http://www.enterprisedb.com
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 2024-Nov-25, Suraj Kharage wrote:
Another case which needs conclusion is -
When changing from INHERIT to NO INHERIT, we need to walk all children and
decrement coninhcount for the corresponding constraint. If a constraint in
one child reaches zero, should we drop it? not sure. If we do, make sure
to reset the corresponding attnotnull bit too. We could decide not to drop
the constraint, in which case you don’t need to reset attnotnull.
I think it's more useful if we keep such a constraint (but of course
change its conislocal to true, if it isn't that already).
There are arguments for doing both things (drop it or leave it); but if
you drop it, there's no way to put it back without scanning the table
again. If you keep it, it's easy to drop it afterwards.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Postgres is bloatware by design: it was built to house
PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 2024-Nov-25, Robert Haas wrote:
In a simple implementation of ALTER TABLE this would be true, but I
don't see why it should need to be true in ours. It should be possible
to notice that there's an existing NOT NULL constraint and use that as
evidence that the new one can be added without needing to revalidate
the table contents. ALTER TABLE does similar things already. For
instance, TryReuseIndex() can attempt to attach an existing index file
to a new index definition without rebuilding it; TryReuseForeignKey
can attempt to re-add a foreign key constraint without needing to
revalidate it. But even more to the point, ATAddCheckNNConstraint and
MergeWithExistingConstraint know about merging a newly-added
constraint with a preexisting one without needing to revalidate the
table.
I think you're explaining why we need this patch, which seems a bit
useless in the thread where this patch was posted.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On Wed, Jan 8, 2025 at 2:43 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
On 2024-Nov-25, Suraj Kharage wrote:
Another case which needs conclusion is -
When changing from INHERIT to NO INHERIT, we need to walk all childrenand
decrement coninhcount for the corresponding constraint. If a constraint
in
one child reaches zero, should we drop it? not sure. If we do, make sure
to reset the corresponding attnotnull bit too. We could decide not todrop
the constraint, in which case you don’t need to reset attnotnull.
I think it's more useful if we keep such a constraint (but of course
change its conislocal to true, if it isn't that already).There are arguments for doing both things (drop it or leave it); but if
you drop it, there's no way to put it back without scanning the table
again. If you keep it, it's easy to drop it afterwards.
Thanks Alvaro.
Please find attached revised version of patch which added the INHERIT to NO
INHERIT state change for not null constraint.
Keep the constraint (instead of dropping) when we mark NO INHERIT.
As Alvaro mentioned above, we can take others opinion on this behavior.
Also, changed the syntax to ALTER TABLE <tabname> ALTER CONSTRAINT
<constrname> SET INHERIT/NO INHERIT;
just to avoid grammer conflicts but we can decide that as well.
Thanks,
Suraj
Attachments:
v3-alter_not_null_constraint_to_inherit_no_inherit.patchapplication/octet-stream; name=v3-alter_not_null_constraint_to_inherit_no_inherit.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c8f7ab7..1b719b8 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [INHERIT | NO INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -108,7 +109,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ INHERIT | NO INHERIT]
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -553,7 +554,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key and not null constraints may be
+ altered. Only Not null constraints can be modified to INHERIT from
+ NO INHERIT which creates constraints in all inherited childrens and
+ validates the same. Not null constraints can be also modified to
+ NO INHERIT from INHERIT.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 54575fc..fb08dad 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -389,7 +389,7 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved,
LOCKMODE lockmode);
-static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
+static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
Relation rel, HeapTuple contuple, List **otherrelids,
@@ -465,6 +465,8 @@ static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *constrname, char *colName,
bool recurse, bool recursing,
LOCKMODE lockmode);
+static ObjectAddress ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode);
static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr);
static bool ConstraintImpliedByRelConstraint(Relation scanrel,
List *testConstraint, List *provenConstraint);
@@ -5405,7 +5407,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
lockmode);
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
- address = ATExecAlterConstraint(rel, cmd, false, false, lockmode);
+ address = ATExecAlterConstraint(wqueue, rel, cmd, false, false,
+ lockmode);
break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
address = ATExecValidateConstraint(wqueue, rel, cmd->name, cmd->recurse,
@@ -11674,14 +11677,14 @@ GetForeignKeyCheckTriggers(Relation trigrel,
*
* Update the attributes of a constraint.
*
- * Currently only works for Foreign Key constraints.
+ * Currently only works for Foreign Key and not null constraints.
*
* If the constraint is modified, returns its address; otherwise, return
* InvalidObjectAddress.
*/
static ObjectAddress
-ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
- bool recursing, LOCKMODE lockmode)
+ATExecAlterConstraint(List **wqueue, Relation rel, AlterTableCmd *cmd,
+ bool recurse, bool recursing, LOCKMODE lockmode)
{
Constraint *cmdcon;
Relation conrel;
@@ -11724,7 +11727,104 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
cmdcon->conname, RelationGetRelationName(rel))));
+ contuple = heap_copytuple(contuple);
+
currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ /* Not null constraint */
+ if (cmdcon->contype == CONSTR_NOTNULL)
+ {
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET %s only supports not null constraint",
+ RelationGetRelationName(rel), cmdcon->conname,
+ cmdcon->is_no_inherit ? "NO INHERIT" : "INHERIT")));
+
+ address = InvalidObjectAddress;
+
+ if (cmdcon->is_no_inherit)
+ {
+ /* Return if constraint is already marked as NO INHERIT. */
+ if (currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as true. */
+ currcon->connoinherit = true;
+ }
+ else if (!cmdcon->is_no_inherit)
+ {
+
+ /* Return if constraint is already marked as INHERIT. */
+ if (!currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+ /* Update the constraint tuple and mark connoinherit as false. */
+ currcon->connoinherit = false;
+ }
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+ CommandCounterIncrement();
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+ ObjectAddress addr;
+
+ if (!cmdcon->is_no_inherit)
+ {
+ addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, true, true, lockmode);
+ if (OidIsValid(addr.objectId))
+ CommandCounterIncrement();
+ }
+ else if (cmdcon->is_no_inherit)
+ addr = ATExecSetNotNullNoInherit(childrel, NameStr(currcon->conname),
+ colName, lockmode);
+
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
if (currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -11800,6 +11900,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
systable_endscan(scan);
+ heap_freetuple(contuple);
+
table_close(tgrel, RowExclusiveLock);
table_close(conrel, RowExclusiveLock);
@@ -11807,6 +11909,104 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, bool recurse,
}
/*
+ * Find out the not null constraint from provided relation and decrement the
+ * coninhcount count if constraint exists since the parent constraint marked as
+ * no inherit.
+ *
+ * We must recurse to child tables during execution.
+ */
+static ObjectAddress
+ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode)
+{
+
+ HeapTuple tuple;
+ AttrNumber attnum;
+ ObjectAddress address;
+ List *children;
+
+ /* Guard against stack overflow due to overly deep inheritance tree. */
+ check_stack_depth();
+
+ ATSimplePermissions(AT_AddConstraint, rel,
+ ATT_PARTITIONED_TABLE | ATT_TABLE | ATT_FOREIGN_TABLE);
+
+ attnum = get_attnum(RelationGetRelid(rel), colName);
+ if (attnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ /* Prevent them from altering a system attribute */
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ /* See if there's already a constraint */
+ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
+ if (HeapTupleIsValid(tuple))
+ {
+ Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ bool changed = false;
+
+ /* Decrement the constraint inheritance count */
+ if (conForm->coninhcount > 0)
+ {
+ conForm->coninhcount--;
+ changed = true;
+ }
+ /* Mark the constraint as local defined once coninhcount = 0 */
+ if (conForm->coninhcount == 0)
+ {
+ conForm->conislocal = true;
+ changed = true;
+ }
+
+ if (changed)
+ {
+ Relation constr_rel;
+
+ constr_rel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ CatalogTupleUpdate(constr_rel, &tuple->t_self, tuple);
+ ObjectAddressSet(address, ConstraintRelationId, conForm->oid);
+ table_close(constr_rel, RowExclusiveLock);
+
+ /* Make update visible */
+ CommandCounterIncrement();
+ }
+ }
+ else
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
+ colName, RelationGetRelid(rel));
+
+ if (tuple)
+ heap_freetuple(tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ /*
+ * Recurse to child tables.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ ATExecSetNotNullNoInherit(childrel, conName, colName, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ return address;
+}
+
+
+/*
* Recursive subroutine of ATExecAlterConstraint. Returns true if the
* constraint is altered.
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b4c1e2c..b232204 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2661,6 +2661,40 @@ alter_table_cmd:
NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /*
+ * ALTER TABLE <name> ALTER CONSTRAINT INHERIT, supports only
+ * not null constraint.
+ */
+ | ALTER CONSTRAINT name SET INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ Constraint *c = makeNode(Constraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->contype = CONSTR_NOTNULL;
+ c->conname = $3;
+ c->is_no_inherit = false;
+
+ $$ = (Node *) n;
+ }
+ /*
+ * ALTER TABLE <name> ALTER CONSTRAINT SET NO INHERIT, supports
+ * only not null constraint.
+ */
+ | ALTER CONSTRAINT name SET NO INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ Constraint *c = makeNode(Constraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->contype = CONSTR_NOTNULL;
+ c->conname = $3;
+ c->is_no_inherit = true;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index bb81f6d..087c84a 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2666,6 +2666,587 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 3 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ part2
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+drop table part2 cascade;
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+ERROR: ALTER TABLE "part1" ALTER CONSTRAINT "part1_f1_check" SET INHERIT only supports not null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+ERROR: constraint "foo" of relation "part1" does not exist
+drop table part1;
+--
-- 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 f51c70d..bd26bf2 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1046,6 +1046,167 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+drop table part2 cascade;
+
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 2025-Jan-13, Suraj Kharage wrote:
Please find attached revised version of patch which added the INHERIT to NO
INHERIT state change for not null constraint.
Thanks!
I find the doc changes a little odd. First, you seem to have added a
[INHERIT/NO INHERIT] flag in the wrong place (line 112); that stuff
already has the NO INHERIT flag next to the constraint types that allow
it, so that change should be removed from the patch. I think the
addition in line 62 are sufficient. Second, adding the explanation for
what this does to the existing varlistentry for ALTER CONSTRAINT looks
out of place. I would add a separate one, something like this perhaps:
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index f9576da435e..10614bcdbd6 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [ NO ] INHERIT
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -551,7 +552,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key constraints may be altered in
+ this fashion, but see below.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-altertable-desc-alter-constraint-inherit">
+ <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term>
+ <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term>
+ <listitem>
+ <para>
+ This form modifies a inheritable constraint so that it becomes not
+ inheritable, or vice-versa. Only not-null constraints may be altered
+ in this fashion at present.
+ In addition to changing the inheritability status of the constraint,
+ in the case where a non-inheritable constraint is being marked
+ inheritable, if the table has children, an equivalent constraint
+ is added to them. If marking an inheritable constraint as
+ non-inheritable on a table with children, then the corresponding
+ constraint on children will be marked as no longer inherited,
+ but not removed.
</para>
</listitem>
</varlistentry>I don't think reusing AT_AlterConstraint for this is a good idea. I
would rather add a new AT_AlterConstraintInherit /
AT_AlterConstraintNoInherit, which takes only a constraint name in
n->name rather than a Constraint in n->def. So gram.y would look like
/*
* ALTER TABLE <name> ALTER CONSTRAINT SET [NO] INHERIT
*/
| ALTER CONSTRAINT name SET INHERIT
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AlterConstraintInherit;
n->name = $3;
$$ = (Node *) n;
}
| ALTER CONSTRAINT name SET NO INHERIT
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AlterConstraintNoInherit;
n->name = $3;
$$ = (Node *) n;
}
This avoids hardcoding in the grammar that we only support this for
not-null constraints -- I'm sure we'll want to implement this for CHECK
constraints later, and at the grammar level there just wouldn't be any
way to implement that the way you have it.
It's a pity that bison doesn't like having unadorned NO INHERIT here.
That would align better with the other use of INHERIT / NO INHERIT we
have in alter table -- requiring a SET there looks ugly. I tried to
change it and the shift/reduce conflict is annoying. I don't have any
bright ideas on fixing that.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo"
(Barón Vladimir Harkonnen)
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
Thanks, Alvaro, for the review.
I have addressed your comments per the above suggestions in the attached v4
patch.
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/>
On Wed, Feb 5, 2025 at 12:11 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
Show quoted text
On 2025-Jan-13, Suraj Kharage wrote:
Please find attached revised version of patch which added the INHERIT to
NO
INHERIT state change for not null constraint.
Thanks!
I find the doc changes a little odd. First, you seem to have added a
[INHERIT/NO INHERIT] flag in the wrong place (line 112); that stuff
already has the NO INHERIT flag next to the constraint types that allow
it, so that change should be removed from the patch. I think the
addition in line 62 are sufficient. Second, adding the explanation for
what this does to the existing varlistentry for ALTER CONSTRAINT looks
out of place. I would add a separate one, something like this perhaps:diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index f9576da435e..10614bcdbd6 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ] ADD <replaceable class="parameter">table_constraint_using_index</replaceable> ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] + ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [ NO ] INHERIT VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ] DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ] @@ -551,7 +552,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <listitem> <para> This form alters the attributes of a constraint that was previously - created. Currently only foreign key constraints may be altered. + created. Currently only foreign key constraints may be altered in + this fashion, but see below. + </para> + </listitem> + </varlistentry> + + <varlistentry id="sql-altertable-desc-alter-constraint-inherit"> + <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term> + <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term> + <listitem> + <para> + This form modifies a inheritable constraint so that it becomes not + inheritable, or vice-versa. Only not-null constraints may be altered + in this fashion at present. + In addition to changing the inheritability status of the constraint, + in the case where a non-inheritable constraint is being marked + inheritable, if the table has children, an equivalent constraint + is added to them. If marking an inheritable constraint as + non-inheritable on a table with children, then the corresponding + constraint on children will be marked as no longer inherited, + but not removed. </para> </listitem> </varlistentry>I don't think reusing AT_AlterConstraint for this is a good idea. I
would rather add a new AT_AlterConstraintInherit /
AT_AlterConstraintNoInherit, which takes only a constraint name in
n->name rather than a Constraint in n->def. So gram.y would look like/*
* ALTER TABLE <name> ALTER CONSTRAINT SET [NO]
INHERIT
*/
| ALTER CONSTRAINT name SET INHERIT
{
AlterTableCmd *n =
makeNode(AlterTableCmd);n->subtype =
AT_AlterConstraintInherit;
n->name = $3;$$ = (Node *) n;
}
| ALTER CONSTRAINT name SET NO INHERIT
{
AlterTableCmd *n =
makeNode(AlterTableCmd);n->subtype =
AT_AlterConstraintNoInherit;
n->name = $3;$$ = (Node *) n;
}This avoids hardcoding in the grammar that we only support this for
not-null constraints -- I'm sure we'll want to implement this for CHECK
constraints later, and at the grammar level there just wouldn't be any
way to implement that the way you have it.It's a pity that bison doesn't like having unadorned NO INHERIT here.
That would align better with the other use of INHERIT / NO INHERIT we
have in alter table -- requiring a SET there looks ugly. I tried to
change it and the shift/reduce conflict is annoying. I don't have any
bright ideas on fixing that.--
Álvaro Herrera 48°01'N 7°57'E —
https://www.EnterpriseDB.com/
"Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo"
(Barón Vladimir Harkonnen)
Attachments:
v4-alter_not_null_constraint_to_inherit_no_inherit.patchapplication/octet-stream; name=v4-alter_not_null_constraint_to_inherit_no_inherit.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index f9576da..59f939c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [INHERIT | NO INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -551,11 +552,31 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key constraints may be altered in
+ this fashion, but see below.
</para>
</listitem>
</varlistentry>
+ <varlistentry id="sql-altertable-desc-alter-constraint-inherit">
+ <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term>
+ <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term>
+ <listitem>
+ <para>
+ This form modifies a inheritable constraint so that it becomes not
+ inheritable, or vice-versa. Only not-null constraints may be altered
+ in this fashion at present.
+ In addition to changing the inheritability status of the constraint,
+ in the case where a non-inheritable constraint is being marked
+ inheritable, if the table has children, an equivalent constraint
+ is added to them. If marking an inheritable constraint as
+ non-inheritable on a table with children, then the corresponding
+ constraint on children will be marked as no longer inherited,
+ but not removed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-altertable-desc-validate-constraint">
<term><literal>VALIDATE CONSTRAINT</literal></term>
<listitem>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 18f64db..fa6231a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -391,6 +391,10 @@ static void AlterSeqNamespaces(Relation classRel, Relation rel,
LOCKMODE lockmode);
static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
+static ObjectAddress ATExecAlterConstraintInherit(List **wqueue, Relation rel, AlterTableCmd *cmd,
+ bool recurse, bool recursing, LOCKMODE lockmode);
+static ObjectAddress ATExecAlterConstraintNoInherit(Relation rel, AlterTableCmd *cmd,
+ LOCKMODE lockmode);
static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
Relation rel, HeapTuple contuple, List **otherrelids,
LOCKMODE lockmode);
@@ -476,6 +480,8 @@ static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *constrname, char *colName,
bool recurse, bool recursing,
LOCKMODE lockmode);
+static ObjectAddress ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode);
static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr);
static bool ConstraintImpliedByRelConstraint(Relation scanrel,
List *testConstraint, List *provenConstraint);
@@ -4660,6 +4666,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetExpression:
case AT_DropExpression:
case AT_SetCompression:
+ case AT_AlterConstraintInherit:
+ case AT_AlterConstraintNoInherit:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -5154,6 +5162,18 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* Recursion occurs during execution phase */
pass = AT_PASS_MISC;
break;
+ case AT_AlterConstraintInherit: /* ALTER CONSTRAINT SET INHERIT*/
+ ATSimplePermissions(cmd->subtype, rel,
+ ATT_TABLE | ATT_PARTITIONED_TABLE);
+ /* Recursion occurs during execution phase */
+ pass = AT_PASS_MISC;
+ break;
+ case AT_AlterConstraintNoInherit: /* ALTER CONSTRAINT SET NO INHERIT */
+ ATSimplePermissions(cmd->subtype, rel,
+ ATT_TABLE | ATT_PARTITIONED_TABLE);
+ /* Recursion occurs during execution phase */
+ pass = AT_PASS_MISC;
+ break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
ATSimplePermissions(cmd->subtype, rel,
ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE);
@@ -5434,6 +5454,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_AlterConstraint: /* ALTER CONSTRAINT */
address = ATExecAlterConstraint(rel, cmd, false, false, lockmode);
break;
+ case AT_AlterConstraintInherit: /* ALTER CONSTRAINT SET INHERIT */
+ address = ATExecAlterConstraintInherit(wqueue, rel, cmd,
+ true, true, lockmode);
+ break;
+ case AT_AlterConstraintNoInherit: /* ALTER CONSTRAINT SET NO INHERIT */
+ address = ATExecAlterConstraintNoInherit(rel, cmd, lockmode);
+ break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
address = ATExecValidateConstraint(wqueue, rel, cmd->name, cmd->recurse,
false, lockmode);
@@ -6518,6 +6545,10 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ADD CONSTRAINT";
case AT_AlterConstraint:
return "ALTER CONSTRAINT";
+ case AT_AlterConstraintInherit:
+ return "ALTER CONSTRAINT ... SET INHERIT";
+ case AT_AlterConstraintNoInherit:
+ return "ALTER CONSTRAINT ... SET NO INHERIT";
case AT_ValidateConstraint:
return "VALIDATE CONSTRAINT";
case AT_DropConstraint:
@@ -21034,3 +21065,331 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
return cstorage;
}
+
+/*
+ * ALTER TABLE ALTER CONSTRAINT SET INHERIT
+ *
+ * Make the constraint as inherit and recurse the constraint to childrens if
+ * any.
+ *
+ * Currently only works for not null constraints.
+ *
+ * If the constraint is modified, returns its address; otherwise, return
+ * InvalidObjectAddress.
+ */
+static ObjectAddress
+ATExecAlterConstraintInherit(List **wqueue, Relation rel,
+ AlterTableCmd *cmd, bool recurse,
+ bool recursing, LOCKMODE lockmode)
+{
+ Relation conrel;
+ Relation tgrel;
+ SysScanDesc scan;
+ ScanKeyData skey[3];
+ HeapTuple contuple;
+ Form_pg_constraint currcon;
+ ObjectAddress address = InvalidObjectAddress;
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+ tgrel = table_open(TriggerRelationId, RowExclusiveLock);
+
+ /*
+ * Find and check the target constraint
+ */
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(cmd->name));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
+
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(contuple = systable_getnext(scan)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ cmd->name, RelationGetRelationName(rel))));
+
+ contuple = heap_copytuple(contuple);
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET INHERIT only supports not null constraint",
+ RelationGetRelationName(rel), cmd->name)));
+
+ /* Return if constraint is already marked as INHERIT. */
+ if (!currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as false. */
+ currcon->connoinherit = false;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+ CommandCounterIncrement();
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+ ObjectAddress addr;
+
+ addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, recurse, recursing, lockmode);
+ if (OidIsValid(addr.objectId))
+ CommandCounterIncrement();
+
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+}
+
+
+/*
+ * ALTER TABLE ALTER CONSTRAINT SET NO INHERIT
+ *
+ * Make the constraint as inherit and recurse to the childrens and decrement
+ * their inheritance count.
+ *
+ * Currently only works for not null constraints.
+ *
+ * If the constraint is modified, returns its address; otherwise, return
+ * InvalidObjectAddress.
+ */
+static ObjectAddress
+ATExecAlterConstraintNoInherit(Relation rel, AlterTableCmd *cmd,
+ LOCKMODE lockmode)
+{
+ Relation conrel;
+ Relation tgrel;
+ SysScanDesc scan;
+ ScanKeyData skey[3];
+ HeapTuple contuple;
+ Form_pg_constraint currcon;
+ ObjectAddress address = InvalidObjectAddress;
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+ tgrel = table_open(TriggerRelationId, RowExclusiveLock);
+
+ /*
+ * Find and check the target constraint
+ */
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(cmd->name));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
+
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(contuple = systable_getnext(scan)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ cmd->name, RelationGetRelationName(rel))));
+
+ contuple = heap_copytuple(contuple);
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
+ if (currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET NO INHERIT only supports not null constraint",
+ RelationGetRelationName(rel), cmd->name)));
+
+ /* Return if constraint is already marked as NO INHERIT. */
+ if (currcon->connoinherit)
+ {
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+ heap_freetuple(contuple);
+
+ return address;
+ }
+
+ /* Update the constraint tuple and mark connoinherit as true. */
+ currcon->connoinherit = true;
+
+ CatalogTupleUpdate(conrel, &contuple->t_self, contuple);
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+ CommandCounterIncrement();
+
+ systable_endscan(scan);
+
+ table_close(tgrel, RowExclusiveLock);
+ table_close(conrel, RowExclusiveLock);
+
+ /* fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ ATExecSetNotNullNoInherit(childrel, NameStr(currcon->conname),
+ colName, lockmode);
+
+ table_close(childrel, NoLock);
+ }
+
+ heap_freetuple(contuple);
+
+ return address;
+}
+
+/*
+ * Find out the not null constraint from provided relation and decrement the
+ * coninhcount count if constraint exists since the parent constraint marked as
+ * no inherit.
+ *
+ * We must recurse to child tables during execution.
+ */
+static ObjectAddress
+ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode)
+{
+
+ HeapTuple tuple;
+ AttrNumber attnum;
+ ObjectAddress address;
+ List *children;
+
+ /* Guard against stack overflow due to overly deep inheritance tree. */
+ check_stack_depth();
+
+ ATSimplePermissions(AT_AddConstraint, rel,
+ ATT_PARTITIONED_TABLE | ATT_TABLE | ATT_FOREIGN_TABLE);
+
+ attnum = get_attnum(RelationGetRelid(rel), colName);
+ if (attnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ /* Prevent them from altering a system attribute */
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ /* See if there's already a constraint */
+ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
+ if (HeapTupleIsValid(tuple))
+ {
+ Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ bool changed = false;
+
+ /* Decrement the constraint inheritance count */
+ if (conForm->coninhcount > 0)
+ {
+ conForm->coninhcount--;
+ changed = true;
+ }
+ /* Mark the constraint as local defined once coninhcount = 0 */
+ if (conForm->coninhcount == 0)
+ {
+ conForm->conislocal = true;
+ changed = true;
+ }
+
+ if (changed)
+ {
+ Relation constr_rel;
+
+ constr_rel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ CatalogTupleUpdate(constr_rel, &tuple->t_self, tuple);
+ ObjectAddressSet(address, ConstraintRelationId, conForm->oid);
+ table_close(constr_rel, RowExclusiveLock);
+
+ /* Make update visible */
+ CommandCounterIncrement();
+ }
+ }
+ else
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
+ colName, RelationGetRelid(rel));
+
+ if (tuple)
+ heap_freetuple(tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ /*
+ * Recurse to child tables.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ ATExecSetNotNullNoInherit(childrel, conName, colName, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ return address;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00..d96989a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2669,6 +2669,26 @@ alter_table_cmd:
NULL, NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT INHERIT */
+ | ALTER CONSTRAINT name SET INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_AlterConstraintInherit;
+ n->name = $3;
+
+ $$ = (Node *) n;
+ }
+ /* ALTER TABLE <name> ALTER CONSTRAINT SET NO INHERIT */
+ | ALTER CONSTRAINT name SET NO INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_AlterConstraintNoInherit;
+ n->name = $3;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ffe155e..6ba542b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2467,6 +2467,8 @@ typedef enum AlterTableType
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_AlterConstraintInherit, /* alter constraint set inherit */
+ AT_AlterConstraintNoInherit, /* alter constraint set no inherit */
} AlterTableType;
typedef struct ReplicaIdentityStmt
diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
index 193669f..46281ec 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -168,6 +168,8 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
strtype = "(re) ADD DOMAIN CONSTRAINT";
break;
case AT_AlterConstraint:
+ case AT_AlterConstraintInherit:
+ case AT_AlterConstraintNoInherit:
strtype = "ALTER CONSTRAINT";
break;
case AT_ValidateConstraint:
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index dbf3835..2d76aba 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2744,6 +2744,587 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 3 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ part2
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+drop table part2 cascade;
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+ERROR: ALTER TABLE "part1" ALTER CONSTRAINT "part1_f1_check" SET INHERIT only supports not null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+ERROR: constraint "foo" of relation "part1" does not exist
+drop table part1;
+--
-- 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 49aae42..a4e7cb7 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1091,6 +1091,167 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+drop table part2 cascade;
+
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 2025-Feb-10, Suraj Kharage wrote:
Thanks, Alvaro, for the review.
I have addressed your comments per the above suggestions in the attached v4
patch.
Okay, thanks. It looks good to me, but I realized a few days ago that
this patch affects the same code as the patch from Amul Sul to change
enforcedness of constraints[1]/messages/by-id/CAAJ_b96gEVfK5MVe5YRVwBuobMFr_CKGvz683zFLNeF8gAN5_Q@mail.gmail.com, and it would be good to structure all
these in a sensible manner to avoid creating a mess of routines that
work against each other.
So I have pushed patch 0001 from Amul, which restructures the way ALTER
TABLE .. ALTER CONSTRAINT works. This should make it possible to use
the same infrastructure for his NOT ENFORCED constraint change as well
as NO INHERIT. The way I see this working for your patch is that you'd
remove the new AT_AlterConstraintInherit code I had suggested
previously, and instead add a new flag to the ATAlterConstraint struct
to carry the information of the state change we want; then the state
change would actually be implemented inside ATExecAlterConstraintInternal.
I think you'll also need a new member in ATAlterConstraint to carry the
column name that's being modified.
One detail about that is that the recursion model would have to be
different, I think. In the existing code for DEFERRED we simply walk
down the hierarchy using the 'conparentid' field to find children. That
won't work for INHERIT / NO INHERIT -- for this we need to use normal
find_inheritance_children-based recursion.
One thing to keep in mind is what happens with
ALTER TABLE ONLY parent ALTER CONSTRAINT zzz;
ensuring that it operates without recursing for legacy inheritance, and
throwing an error for partitioned tables.
Thanks!
[1]: /messages/by-id/CAAJ_b96gEVfK5MVe5YRVwBuobMFr_CKGvz683zFLNeF8gAN5_Q@mail.gmail.com
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Digital and video cameras have this adjustment and film cameras don't for the
same reason dogs and cats lick themselves: because they can." (Ken Rockwell)
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
Thanks, Alvaro.
I have revised the patch as per your last update.
Please find attached v5 for further review.
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/>
On Wed, Feb 19, 2025 at 9:16 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
Show quoted text
On 2025-Feb-10, Suraj Kharage wrote:
Thanks, Alvaro, for the review.
I have addressed your comments per the above suggestions in the attached
v4
patch.
Okay, thanks. It looks good to me, but I realized a few days ago that
this patch affects the same code as the patch from Amul Sul to change
enforcedness of constraints[1], and it would be good to structure all
these in a sensible manner to avoid creating a mess of routines that
work against each other.So I have pushed patch 0001 from Amul, which restructures the way ALTER
TABLE .. ALTER CONSTRAINT works. This should make it possible to use
the same infrastructure for his NOT ENFORCED constraint change as well
as NO INHERIT. The way I see this working for your patch is that you'd
remove the new AT_AlterConstraintInherit code I had suggested
previously, and instead add a new flag to the ATAlterConstraint struct
to carry the information of the state change we want; then the state
change would actually be implemented inside ATExecAlterConstraintInternal.
I think you'll also need a new member in ATAlterConstraint to carry the
column name that's being modified.One detail about that is that the recursion model would have to be
different, I think. In the existing code for DEFERRED we simply walk
down the hierarchy using the 'conparentid' field to find children. That
won't work for INHERIT / NO INHERIT -- for this we need to use normal
find_inheritance_children-based recursion.One thing to keep in mind is what happens with
ALTER TABLE ONLY parent ALTER CONSTRAINT zzz;
ensuring that it operates without recursing for legacy inheritance, and
throwing an error for partitioned tables.Thanks!
[1]
/messages/by-id/CAAJ_b96gEVfK5MVe5YRVwBuobMFr_CKGvz683zFLNeF8gAN5_Q@mail.gmail.com--
Álvaro Herrera PostgreSQL Developer —
https://www.EnterpriseDB.com/
"Digital and video cameras have this adjustment and film cameras don't for
the
same reason dogs and cats lick themselves: because they can." (Ken
Rockwell)
Attachments:
v5-alter_not_null_constraint_to_inherit_no_inherit.patchapplication/octet-stream; name=v5-alter_not_null_constraint_to_inherit_no_inherit.patchDownload
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8e56b8e..fd02c3c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [INHERIT | NO INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -556,11 +557,31 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key constraints may be altered in
+ this fashion, but see below.
</para>
</listitem>
</varlistentry>
+ <varlistentry id="sql-altertable-desc-alter-constraint-inherit">
+ <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term>
+ <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term>
+ <listitem>
+ <para>
+ This form modifies a inheritable constraint so that it becomes not
+ inheritable, or vice-versa. Only not-null constraints may be altered
+ in this fashion at present.
+ In addition to changing the inheritability status of the constraint,
+ in the case where a non-inheritable constraint is being marked
+ inheritable, if the table has children, an equivalent constraint
+ is added to them. If marking an inheritable constraint as
+ non-inheritable on a table with children, then the corresponding
+ constraint on children will be marked as no longer inherited,
+ but not removed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-altertable-desc-validate-constraint">
<term><literal>VALIDATE CONSTRAINT</literal></term>
<listitem>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 9d8754b..b814e00 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -389,14 +389,18 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved,
LOCKMODE lockmode);
-static ObjectAddress ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon,
+static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
+ ATAlterConstraint *cmdcon,
bool recurse, LOCKMODE lockmode);
static bool ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
Relation tgrel, Relation rel, HeapTuple contuple,
- bool recurse, List **otherrelids, LOCKMODE lockmode);
+ bool recurse, List **otherrelids, LOCKMODE lockmode,
+ List **wqueue);
static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
bool deferrable, bool initdeferred,
List **otherrelids);
+static ObjectAddress ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode);
static ObjectAddress ATExecValidateConstraint(List **wqueue,
Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
@@ -5449,8 +5453,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
lockmode);
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
- address = ATExecAlterConstraint(rel, castNode(ATAlterConstraint,
- cmd->def),
+ address = ATExecAlterConstraint(wqueue, rel,
+ castNode(ATAlterConstraint, cmd->def),
cmd->recurse, lockmode);
break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
@@ -11796,14 +11800,14 @@ GetForeignKeyCheckTriggers(Relation trigrel,
*
* Update the attributes of a constraint.
*
- * Currently only works for Foreign Key constraints.
+ * Currently only works for Foreign Key and not null constraints.
*
* If the constraint is modified, returns its address; otherwise, return
* InvalidObjectAddress.
*/
static ObjectAddress
-ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
- LOCKMODE lockmode)
+ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
+ bool recurse, LOCKMODE lockmode)
{
Relation conrel;
Relation tgrel;
@@ -11854,11 +11858,18 @@ ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
cmdcon->conname, RelationGetRelationName(rel))));
currcon = (Form_pg_constraint) GETSTRUCT(contuple);
- if (currcon->contype != CONSTRAINT_FOREIGN)
+ if (cmdcon->alterDeferrability && currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ else if (cmdcon->alterinheritability &&
+ currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET %s only supports not null constraint",
+ RelationGetRelationName(rel), cmdcon->conname,
+ cmdcon->noinherit ? "NO INHERIT" : "INHERIT")));
/*
* If it's not the topmost constraint, raise an error.
@@ -11910,7 +11921,7 @@ ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
* Do the actual catalog work, and recurse if necessary.
*/
if (ATExecAlterConstraintInternal(cmdcon, conrel, tgrel, rel, contuple,
- recurse, &otherrelids, lockmode))
+ recurse, &otherrelids, lockmode, wqueue))
ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
/*
@@ -11943,7 +11954,8 @@ ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
static bool
ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
Relation tgrel, Relation rel, HeapTuple contuple,
- bool recurse, List **otherrelids, LOCKMODE lockmode)
+ bool recurse, List **otherrelids, LOCKMODE lockmode,
+ List **wqueue)
{
Form_pg_constraint currcon;
Oid refrelid = InvalidOid;
@@ -12024,13 +12036,77 @@ ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
childrel = table_open(childcon->conrelid, lockmode);
ATExecAlterConstraintInternal(cmdcon, conrel, tgrel, childrel, childtup,
- recurse, otherrelids, lockmode);
+ recurse, otherrelids, lockmode, wqueue);
table_close(childrel, NoLock);
}
systable_endscan(pscan);
}
+ /* Update the catalog for inheritability */
+ if (cmdcon->alterinheritability)
+ {
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+
+ /* Return false if constraint doesn't need updation. */
+ if ((cmdcon->noinherit && currcon->connoinherit) ||
+ (!cmdcon->noinherit && !currcon->connoinherit))
+ return false;
+
+ copyTuple = heap_copytuple(contuple);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+
+ if (cmdcon->noinherit)
+ {
+ /* Update the constraint tuple and mark connoinherit as true. */
+ copy_con->connoinherit = true;
+ }
+ else
+ {
+ /* Update the constraint tuple and mark connoinherit as false. */
+ copy_con->connoinherit = false;
+ }
+
+ CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
+ CommandCounterIncrement();
+ heap_freetuple(copyTuple);
+
+ /* Fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Recurse to propagate the constraint to children that don't have one.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+ ObjectAddress addr;
+
+ if (!cmdcon->noinherit)
+ {
+ addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, true, true, lockmode);
+ if (OidIsValid(addr.objectId))
+ CommandCounterIncrement();
+ }
+ else if (cmdcon->noinherit)
+ ATExecSetNotNullNoInherit(childrel, NameStr(currcon->conname),
+ colName, lockmode);
+
+ table_close(childrel, NoLock);
+ }
+
+ return false;
+ }
+
return changed;
}
@@ -12100,6 +12176,103 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
}
/*
+ * Find out the not null constraint from provided relation and decrement the
+ * coninhcount count if constraint exists since the parent constraint marked as
+ * no inherit.
+ *
+ * We must recurse to child tables during execution.
+ */
+static ObjectAddress
+ATExecSetNotNullNoInherit(Relation rel, char *conName,
+ char *colName, LOCKMODE lockmode)
+{
+
+ HeapTuple tuple;
+ AttrNumber attnum;
+ ObjectAddress address;
+ List *children;
+
+ /* Guard against stack overflow due to overly deep inheritance tree. */
+ check_stack_depth();
+
+ ATSimplePermissions(AT_AddConstraint, rel,
+ ATT_PARTITIONED_TABLE | ATT_TABLE | ATT_FOREIGN_TABLE);
+
+ attnum = get_attnum(RelationGetRelid(rel), colName);
+ if (attnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ /* Prevent them from altering a system attribute */
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ /* See if there's already a constraint */
+ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
+ if (HeapTupleIsValid(tuple))
+ {
+ Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ bool changed = false;
+
+ /* Decrement the constraint inheritance count */
+ if (conForm->coninhcount > 0)
+ {
+ conForm->coninhcount--;
+ changed = true;
+ }
+ /* Mark the constraint as local defined once coninhcount = 0 */
+ if (conForm->coninhcount == 0)
+ {
+ conForm->conislocal = true;
+ changed = true;
+ }
+
+ if (changed)
+ {
+ Relation constr_rel;
+
+ constr_rel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ CatalogTupleUpdate(constr_rel, &tuple->t_self, tuple);
+ ObjectAddressSet(address, ConstraintRelationId, conForm->oid);
+ table_close(constr_rel, RowExclusiveLock);
+
+ /* Make update visible */
+ CommandCounterIncrement();
+ }
+ }
+ else
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
+ colName, RelationGetRelid(rel));
+
+ if (tuple)
+ heap_freetuple(tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ /*
+ * Recurse to child tables.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);
+
+ foreach_oid(childoid, children)
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ ATExecSetNotNullNoInherit(childrel, conName, colName, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ return address;
+}
+
+/*
* ALTER TABLE VALIDATE CONSTRAINT
*
* XXX The reason we handle recursion here rather than at Phase 1 is because
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d99c93..1509cf6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2669,6 +2669,34 @@ alter_table_cmd:
NULL, NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT SET INHERIT */
+ | ALTER CONSTRAINT name SET INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ ATAlterConstraint *c = makeNode(ATAlterConstraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->conname = $3;
+ c->alterinheritability = true;
+ c->noinherit = false;
+
+ $$ = (Node *) n;
+ }
+ /* ALTER TABLE <name> ALTER CONSTRAINT SET NO INHERIT */
+ | ALTER CONSTRAINT name SET NO INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ ATAlterConstraint *c = makeNode(ATAlterConstraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->conname = $3;
+ c->alterinheritability = true;
+ c->noinherit = true;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0b208f5..1147b57 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2493,6 +2493,8 @@ typedef struct ATAlterConstraint
bool alterDeferrability; /* changing deferrability properties? */
bool deferrable; /* DEFERRABLE? */
bool initdeferred; /* INITIALLY DEFERRED? */
+ bool alterinheritability; /* changing inheritability properties */
+ bool noinherit;
} ATAlterConstraint;
/* Ad-hoc node for AT_ReplicaIdentity */
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 420b6ae..2ee2ef4 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2744,6 +2744,587 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 0 | t
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 3 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ part2
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+drop table part2 cascade;
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+ERROR: ALTER TABLE "part1" ALTER CONSTRAINT "part1_f1_check" SET INHERIT only supports not null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+ERROR: constraint "foo" of relation "part1" does not exist
+drop table part1;
+--
-- 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 30fba16..d0dcfc6 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1091,6 +1091,167 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+drop table part2 cascade;
+
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 2025-Feb-21, Suraj Kharage wrote:
Thanks, Alvaro.
I have revised the patch as per your last update.
Please find attached v5 for further review.
Hello
I noticed two issues. One is that we are OK to modify a constraint
that's defined in our parent, which breaks everything. We can only
allow a top-level constraint to be modified. I added a check in
ATExecAlterConstraint() for this. Please add a test case for this.
The other one is that when we set a constraint to NO INHERIT on a table
with children and grandchildren, we must only modify the directly
inheriting constraints as not having a parent -- we must not recurse to
also do that in the grandchildren! Otherwise they become disconnected,
which makes no sense. So we just want to locate the constraint for each
child, modify by subtracting 1 from coninhcount and set islocal, and
we're done. The whole ATExecSetNotNullNoInherit() function is based on
the wrong premise that this requires to recurse. I chose to remove it
to keep things simple.
Stylistically, in tablecmds.c we always have the 'List **wqueue'
argument as the first one in functions that take it. So when adding it
to a function that doesn't have it, don't put it last.
This error message:
- errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET %s only supports not null constraint",
- RelationGetRelationName(rel), cmdcon->conname,
- cmdcon->noinherit ? "NO INHERIT" : "INHERIT")));
seems a bit excessive. Looking at other examples, it doesn't look like
we need to cite the complete message in so much detail (especially if,
say, the user specified a schema-qualified table name in the command
which won't show up in the error message, this would look just weird).
I simplified it.
Please verify that the tests are still working correctly and resubmit.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"E pur si muove" (Galileo Galilei)
Attachments:
0001-Alvaro-s-review.patch.txttext/plain; charset=utf-8Download
From 3113c30f1e09f084f9f33b4006bed145c52c8573 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@alvh.no-ip.org>
Date: Fri, 28 Feb 2025 22:45:59 +0100
Subject: [PATCH] Alvaro's review
---
doc/src/sgml/ref/alter_table.sgml | 2 +-
src/backend/commands/tablecmds.c | 199 +++++++++---------------------
src/backend/parser/gram.y | 4 +-
src/include/nodes/parsenodes.h | 2 +-
4 files changed, 60 insertions(+), 147 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index fd02c3ca370..a397cdc0792 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -574,7 +574,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
In addition to changing the inheritability status of the constraint,
in the case where a non-inheritable constraint is being marked
inheritable, if the table has children, an equivalent constraint
- is added to them. If marking an inheritable constraint as
+ will be added to them. If marking an inheritable constraint as
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3b78b980d47..0e3a9d47720 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -392,15 +392,12 @@ static void AlterSeqNamespaces(Relation classRel, Relation rel,
static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
ATAlterConstraint *cmdcon,
bool recurse, LOCKMODE lockmode);
-static bool ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
+static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel,
Relation tgrel, Relation rel, HeapTuple contuple,
- bool recurse, List **otherrelids, LOCKMODE lockmode,
- List **wqueue);
+ bool recurse, List **otherrelids, LOCKMODE lockmode);
static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
bool deferrable, bool initdeferred,
List **otherrelids);
-static ObjectAddress ATExecSetNotNullNoInherit(Relation rel, char *conName,
- char *colName, LOCKMODE lockmode);
static ObjectAddress ATExecValidateConstraint(List **wqueue,
Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
@@ -11863,13 +11860,21 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
- else if (cmdcon->alterinheritability &&
- currcon->contype != CONSTRAINT_NOTNULL)
+ if (cmdcon->alterInheritability &&
+ currcon->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET %s only supports not null constraint",
- RelationGetRelationName(rel), cmdcon->conname,
- cmdcon->noinherit ? "NO INHERIT" : "INHERIT")));
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a not-null constraint",
+ RelationGetRelationName(rel), cmdcon->conname));
+
+ /* Refuse to modify inheritability of inherited constraints */
+ if (cmdcon->alterInheritability &&
+ cmdcon->noinherit && currcon->coninhcount > 0)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot alter inherited constraint \"%s\" on relation \"%s\"",
+ NameStr(currcon->conname),
+ RelationGetRelationName(rel)));
/*
* If it's not the topmost constraint, raise an error.
@@ -11920,8 +11925,8 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
/*
* Do the actual catalog work, and recurse if necessary.
*/
- if (ATExecAlterConstraintInternal(cmdcon, conrel, tgrel, rel, contuple,
- recurse, &otherrelids, lockmode, wqueue))
+ if (ATExecAlterConstraintInternal(wqueue, cmdcon, conrel, tgrel, rel,
+ contuple, recurse, &otherrelids, lockmode))
ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
/*
@@ -11952,10 +11957,10 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
* but existing releases don't do that.)
*/
static bool
-ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
- Relation tgrel, Relation rel, HeapTuple contuple,
- bool recurse, List **otherrelids, LOCKMODE lockmode,
- List **wqueue)
+ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel, Relation rel,
+ HeapTuple contuple, bool recurse,
+ List **otherrelids, LOCKMODE lockmode)
{
Form_pg_constraint currcon;
Oid refrelid = InvalidOid;
@@ -12035,16 +12040,20 @@ ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
Relation childrel;
childrel = table_open(childcon->conrelid, lockmode);
- ATExecAlterConstraintInternal(cmdcon, conrel, tgrel, childrel, childtup,
- recurse, otherrelids, lockmode, wqueue);
+ ATExecAlterConstraintInternal(wqueue, cmdcon, conrel, tgrel, childrel,
+ childtup, recurse, otherrelids, lockmode);
table_close(childrel, NoLock);
}
systable_endscan(pscan);
}
- /* Update the catalog for inheritability */
- if (cmdcon->alterinheritability)
+ /*
+ * Update the catalog for inheritability. No work if the constraint is
+ * already in the requested state.
+ */
+ if (cmdcon->alterInheritability &&
+ (cmdcon->noinherit != currcon->connoinherit))
{
AttrNumber colNum;
char *colName;
@@ -12052,59 +12061,60 @@ ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
HeapTuple copyTuple;
Form_pg_constraint copy_con;
- /* Return false if constraint doesn't need updation. */
- if ((cmdcon->noinherit && currcon->connoinherit) ||
- (!cmdcon->noinherit && !currcon->connoinherit))
- return false;
+ /* The current implementation only works for NOT NULL constraints */
+ Assert(currcon->contype == CONSTRAINT_NOTNULL);
copyTuple = heap_copytuple(contuple);
copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
-
- if (cmdcon->noinherit)
- {
- /* Update the constraint tuple and mark connoinherit as true. */
- copy_con->connoinherit = true;
- }
- else
- {
- /* Update the constraint tuple and mark connoinherit as false. */
- copy_con->connoinherit = false;
- }
+ copy_con->connoinherit = cmdcon->noinherit;
CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
CommandCounterIncrement();
heap_freetuple(copyTuple);
+ changed = true;
/* Fetch the column number and name */
colNum = extractNotNullColumn(contuple);
colName = get_attname(currcon->conrelid, colNum, false);
/*
- * Recurse to propagate the constraint to children that don't have one.
+ * Propagate the change to children. For SET NO INHERIT, we don't
+ * recursively affect children, just the immediate level.
*/
children = find_inheritance_children(RelationGetRelid(rel),
lockmode);
-
foreach_oid(childoid, children)
{
- Relation childrel = table_open(childoid, NoLock);
ObjectAddress addr;
- if (!cmdcon->noinherit)
+ if (cmdcon->noinherit)
{
+ HeapTuple childtup;
+ Form_pg_constraint childcon;
+
+ childtup = findNotNullConstraint(childoid, colName);
+ if (!childtup)
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
+ colName, childoid);
+ childcon = (Form_pg_constraint) GETSTRUCT(childtup);
+ Assert(childcon->coninhcount > 0);
+ childcon->coninhcount--;
+ childcon->conislocal = true;
+ CatalogTupleUpdate(conrel, &childtup->t_self, childtup);
+ heap_freetuple(childtup);
+ }
+ else
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
colName, true, true, lockmode);
if (OidIsValid(addr.objectId))
CommandCounterIncrement();
+ table_close(childrel, NoLock);
}
- else if (cmdcon->noinherit)
- ATExecSetNotNullNoInherit(childrel, NameStr(currcon->conname),
- colName, lockmode);
- table_close(childrel, NoLock);
}
-
- return false;
}
return changed;
@@ -12175,103 +12185,6 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
systable_endscan(tgscan);
}
-/*
- * Find out the not null constraint from provided relation and decrement the
- * coninhcount count if constraint exists since the parent constraint marked as
- * no inherit.
- *
- * We must recurse to child tables during execution.
- */
-static ObjectAddress
-ATExecSetNotNullNoInherit(Relation rel, char *conName,
- char *colName, LOCKMODE lockmode)
-{
-
- HeapTuple tuple;
- AttrNumber attnum;
- ObjectAddress address;
- List *children;
-
- /* Guard against stack overflow due to overly deep inheritance tree. */
- check_stack_depth();
-
- ATSimplePermissions(AT_AddConstraint, rel,
- ATT_PARTITIONED_TABLE | ATT_TABLE | ATT_FOREIGN_TABLE);
-
- attnum = get_attnum(RelationGetRelid(rel), colName);
- if (attnum == InvalidAttrNumber)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("column \"%s\" of relation \"%s\" does not exist",
- colName, RelationGetRelationName(rel))));
-
- /* Prevent them from altering a system attribute */
- if (attnum <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter system column \"%s\"",
- colName)));
-
- /* See if there's already a constraint */
- tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
- if (HeapTupleIsValid(tuple))
- {
- Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
- bool changed = false;
-
- /* Decrement the constraint inheritance count */
- if (conForm->coninhcount > 0)
- {
- conForm->coninhcount--;
- changed = true;
- }
- /* Mark the constraint as local defined once coninhcount = 0 */
- if (conForm->coninhcount == 0)
- {
- conForm->conislocal = true;
- changed = true;
- }
-
- if (changed)
- {
- Relation constr_rel;
-
- constr_rel = table_open(ConstraintRelationId, RowExclusiveLock);
-
- CatalogTupleUpdate(constr_rel, &tuple->t_self, tuple);
- ObjectAddressSet(address, ConstraintRelationId, conForm->oid);
- table_close(constr_rel, RowExclusiveLock);
-
- /* Make update visible */
- CommandCounterIncrement();
- }
- }
- else
- elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
- colName, RelationGetRelid(rel));
-
- if (tuple)
- heap_freetuple(tuple);
-
- InvokeObjectPostAlterHook(RelationRelationId,
- RelationGetRelid(rel), attnum);
-
- /*
- * Recurse to child tables.
- */
- children = find_inheritance_children(RelationGetRelid(rel), lockmode);
-
- foreach_oid(childoid, children)
- {
- Relation childrel = table_open(childoid, NoLock);
-
- ATExecSetNotNullNoInherit(childrel, conName, colName, lockmode);
- table_close(childrel, NoLock);
- }
-
- return address;
-}
-
/*
* ALTER TABLE VALIDATE CONSTRAINT
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1509cf61552..b99601e3788 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2678,7 +2678,7 @@ alter_table_cmd:
n->subtype = AT_AlterConstraint;
n->def = (Node *) c;
c->conname = $3;
- c->alterinheritability = true;
+ c->alterInheritability = true;
c->noinherit = false;
$$ = (Node *) n;
@@ -2692,7 +2692,7 @@ alter_table_cmd:
n->subtype = AT_AlterConstraint;
n->def = (Node *) c;
c->conname = $3;
- c->alterinheritability = true;
+ c->alterInheritability = true;
c->noinherit = true;
$$ = (Node *) n;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1147b573a37..23c9e3c5abf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2493,7 +2493,7 @@ typedef struct ATAlterConstraint
bool alterDeferrability; /* changing deferrability properties? */
bool deferrable; /* DEFERRABLE? */
bool initdeferred; /* INITIALLY DEFERRED? */
- bool alterinheritability; /* changing inheritability properties */
+ bool alterInheritability; /* changing inheritability properties */
bool noinherit;
} ATAlterConstraint;
--
2.39.5
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
Thanks Alvaro for the review and fixup patch.
I agree with your changes and merged that into the main patch along with a
couple of other changes.
Please find attached v6 for further review.
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/>
On Sat, Mar 1, 2025 at 4:15 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
Show quoted text
On 2025-Feb-21, Suraj Kharage wrote:
Thanks, Alvaro.
I have revised the patch as per your last update.
Please find attached v5 for further review.Hello
I noticed two issues. One is that we are OK to modify a constraint
that's defined in our parent, which breaks everything. We can only
allow a top-level constraint to be modified. I added a check in
ATExecAlterConstraint() for this. Please add a test case for this.The other one is that when we set a constraint to NO INHERIT on a table
with children and grandchildren, we must only modify the directly
inheriting constraints as not having a parent -- we must not recurse to
also do that in the grandchildren! Otherwise they become disconnected,
which makes no sense. So we just want to locate the constraint for each
child, modify by subtracting 1 from coninhcount and set islocal, and
we're done. The whole ATExecSetNotNullNoInherit() function is based on
the wrong premise that this requires to recurse. I chose to remove it
to keep things simple.Stylistically, in tablecmds.c we always have the 'List **wqueue'
argument as the first one in functions that take it. So when adding it
to a function that doesn't have it, don't put it last.This error message:
- errmsg("ALTER TABLE \"%s\" ALTER CONSTRAINT \"%s\" SET %s
only supports not null constraint",
- RelationGetRelationName(rel), cmdcon->conname,
- cmdcon->noinherit ? "NO INHERIT" : "INHERIT")));
seems a bit excessive. Looking at other examples, it doesn't look like
we need to cite the complete message in so much detail (especially if,
say, the user specified a schema-qualified table name in the command
which won't show up in the error message, this would look just weird).
I simplified it.Please verify that the tests are still working correctly and resubmit.
--
Álvaro Herrera PostgreSQL Developer —
https://www.EnterpriseDB.com/
"E pur si muove" (Galileo Galilei)
Attachments:
v6-0001-alter-not-null-constraint-from-inherit-to-no-inhe.patchapplication/octet-stream; name=v6-0001-alter-not-null-constraint-from-inherit-to-no-inhe.patchDownload
From e6a60e51c79bcca08291c9e9fc9a340dae95d93c Mon Sep 17 00:00:00 2001
From: Suraj Kharage <suraj.kharage@enterprisedb.com>
Date: Thu, 13 Mar 2025 05:38:41 +0530
Subject: [PATCH v6] alter not null constraint from inherit to no inherit and
vice versa.
---
doc/src/sgml/ref/alter_table.sgml | 23 +-
src/backend/commands/tablecmds.c | 115 ++++++-
src/backend/parser/gram.y | 28 ++
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/inherit.out | 585 ++++++++++++++++++++++++++++++++++
src/test/regress/sql/inherit.sql | 164 ++++++++++
6 files changed, 901 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8e56b8e..a397cdc 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -59,6 +59,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> SET [INHERIT | NO INHERIT]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -556,11 +557,31 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form alters the attributes of a constraint that was previously
- created. Currently only foreign key constraints may be altered.
+ created. Currently only foreign key constraints may be altered in
+ this fashion, but see below.
</para>
</listitem>
</varlistentry>
+ <varlistentry id="sql-altertable-desc-alter-constraint-inherit">
+ <term><literal>ALTER CONSTRAINT ... SET INHERIT</literal></term>
+ <term><literal>ALTER CONSTRAINT ... SET NO INHERIT</literal></term>
+ <listitem>
+ <para>
+ This form modifies a inheritable constraint so that it becomes not
+ inheritable, or vice-versa. Only not-null constraints may be altered
+ in this fashion at present.
+ In addition to changing the inheritability status of the constraint,
+ in the case where a non-inheritable constraint is being marked
+ inheritable, if the table has children, an equivalent constraint
+ will be added to them. If marking an inheritable constraint as
+ non-inheritable on a table with children, then the corresponding
+ constraint on children will be marked as no longer inherited,
+ but not removed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-altertable-desc-validate-constraint">
<term><literal>VALIDATE CONSTRAINT</literal></term>
<listitem>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ce7d115..c2502de 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -389,9 +389,10 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved,
LOCKMODE lockmode);
-static ObjectAddress ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon,
+static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
+ ATAlterConstraint *cmdcon,
bool recurse, LOCKMODE lockmode);
-static bool ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
+static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel,
Relation tgrel, Relation rel, HeapTuple contuple,
bool recurse, List **otherrelids, LOCKMODE lockmode);
static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
@@ -5449,8 +5450,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
lockmode);
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
- address = ATExecAlterConstraint(rel, castNode(ATAlterConstraint,
- cmd->def),
+ address = ATExecAlterConstraint(wqueue, rel,
+ castNode(ATAlterConstraint, cmd->def),
cmd->recurse, lockmode);
break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
@@ -11796,14 +11797,14 @@ GetForeignKeyCheckTriggers(Relation trigrel,
*
* Update the attributes of a constraint.
*
- * Currently only works for Foreign Key constraints.
+ * Currently only works for Foreign Key and not null constraints.
*
* If the constraint is modified, returns its address; otherwise, return
* InvalidObjectAddress.
*/
static ObjectAddress
-ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
- LOCKMODE lockmode)
+ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
+ bool recurse, LOCKMODE lockmode)
{
Relation conrel;
Relation tgrel;
@@ -11854,11 +11855,26 @@ ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
cmdcon->conname, RelationGetRelationName(rel))));
currcon = (Form_pg_constraint) GETSTRUCT(contuple);
- if (currcon->contype != CONSTRAINT_FOREIGN)
+ if (cmdcon->alterDeferrability && currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ if (cmdcon->alterInheritability &&
+ currcon->contype != CONSTRAINT_NOTNULL)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a not-null constraint",
+ cmdcon->conname, RelationGetRelationName(rel)));
+
+ /* Refuse to modify inheritability of inherited constraints */
+ if (cmdcon->alterInheritability &&
+ cmdcon->noinherit && currcon->coninhcount > 0)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot alter inherited constraint \"%s\" on relation \"%s\"",
+ NameStr(currcon->conname),
+ RelationGetRelationName(rel)));
/*
* If it's not the topmost constraint, raise an error.
@@ -11909,8 +11925,8 @@ ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
/*
* Do the actual catalog work, and recurse if necessary.
*/
- if (ATExecAlterConstraintInternal(cmdcon, conrel, tgrel, rel, contuple,
- recurse, &otherrelids, lockmode))
+ if (ATExecAlterConstraintInternal(wqueue, cmdcon, conrel, tgrel, rel,
+ contuple, recurse, &otherrelids, lockmode))
ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
/*
@@ -11941,9 +11957,10 @@ ATExecAlterConstraint(Relation rel, ATAlterConstraint *cmdcon, bool recurse,
* but existing releases don't do that.)
*/
static bool
-ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
- Relation tgrel, Relation rel, HeapTuple contuple,
- bool recurse, List **otherrelids, LOCKMODE lockmode)
+ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel, Relation rel,
+ HeapTuple contuple, bool recurse,
+ List **otherrelids, LOCKMODE lockmode)
{
Form_pg_constraint currcon;
Oid refrelid = InvalidOid;
@@ -12023,14 +12040,82 @@ ATExecAlterConstraintInternal(ATAlterConstraint *cmdcon, Relation conrel,
Relation childrel;
childrel = table_open(childcon->conrelid, lockmode);
- ATExecAlterConstraintInternal(cmdcon, conrel, tgrel, childrel, childtup,
- recurse, otherrelids, lockmode);
+ ATExecAlterConstraintInternal(wqueue, cmdcon, conrel, tgrel, childrel,
+ childtup, recurse, otherrelids, lockmode);
table_close(childrel, NoLock);
}
systable_endscan(pscan);
}
+ /*
+ * Update the catalog for inheritability. No work if the constraint is
+ * already in the requested state.
+ */
+ if (cmdcon->alterInheritability &&
+ (cmdcon->noinherit != currcon->connoinherit))
+ {
+ AttrNumber colNum;
+ char *colName;
+ List *children;
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+
+ /* The current implementation only works for NOT NULL constraints */
+ Assert(currcon->contype == CONSTRAINT_NOTNULL);
+
+ copyTuple = heap_copytuple(contuple);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ copy_con->connoinherit = cmdcon->noinherit;
+
+ CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
+ CommandCounterIncrement();
+ heap_freetuple(copyTuple);
+ changed = true;
+
+ /* Fetch the column number and name */
+ colNum = extractNotNullColumn(contuple);
+ colName = get_attname(currcon->conrelid, colNum, false);
+
+ /*
+ * Propagate the change to children. For SET NO INHERIT, we don't
+ * recursively affect children, just the immediate level.
+ */
+ children = find_inheritance_children(RelationGetRelid(rel),
+ lockmode);
+ foreach_oid(childoid, children)
+ {
+ ObjectAddress addr;
+
+ if (cmdcon->noinherit)
+ {
+ HeapTuple childtup;
+ Form_pg_constraint childcon;
+
+ childtup = findNotNullConstraint(childoid, colName);
+ if (!childtup)
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
+ colName, childoid);
+ childcon = (Form_pg_constraint) GETSTRUCT(childtup);
+ Assert(childcon->coninhcount > 0);
+ childcon->coninhcount--;
+ childcon->conislocal = true;
+ CatalogTupleUpdate(conrel, &childtup->t_self, childtup);
+ heap_freetuple(childtup);
+ }
+ else
+ {
+ Relation childrel = table_open(childoid, NoLock);
+
+ addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
+ colName, true, true, lockmode);
+ if (OidIsValid(addr.objectId))
+ CommandCounterIncrement();
+ table_close(childrel, NoLock);
+ }
+ }
+ }
+
return changed;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d99c93..b99601e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2669,6 +2669,34 @@ alter_table_cmd:
NULL, NULL, NULL, yyscanner);
$$ = (Node *) n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT SET INHERIT */
+ | ALTER CONSTRAINT name SET INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ ATAlterConstraint *c = makeNode(ATAlterConstraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->conname = $3;
+ c->alterInheritability = true;
+ c->noinherit = false;
+
+ $$ = (Node *) n;
+ }
+ /* ALTER TABLE <name> ALTER CONSTRAINT SET NO INHERIT */
+ | ALTER CONSTRAINT name SET NO INHERIT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ ATAlterConstraint *c = makeNode(ATAlterConstraint);
+
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->conname = $3;
+ c->alterInheritability = true;
+ c->noinherit = true;
+
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0b208f5..23c9e3c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2493,6 +2493,8 @@ typedef struct ATAlterConstraint
bool alterDeferrability; /* changing deferrability properties? */
bool deferrable; /* DEFERRABLE? */
bool initdeferred; /* INITIALLY DEFERRED? */
+ bool alterInheritability; /* changing inheritability properties */
+ bool noinherit;
} ATAlterConstraint;
/* Ad-hoc node for AT_ReplicaIdentity */
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 420b6ae..ed039ff 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2744,6 +2744,591 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_multiparent
drop cascades to table inh_multiparent2
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+/* Can't modify inheritability of inherited constraints */
+alter table ch1 alter constraint part1_f1_not_null set no inherit;
+ERROR: cannot alter inherited constraint "part1_f1_not_null" on relation "ch1"
+/* alter constraint set no inherit should work on top-level constraint */
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 1 | t
+ ch3 | part1_f1_not_null | n | 2 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+ERROR: column "f1" of relation "ch1" contains null values
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 2 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter f1 set not null;
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f1_not_null | n | 1 | t
+ ch2 | ch1_f1_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(3 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1,
+ ch1
+
+drop table part1 cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ ch1 | ch1_f3_not_null | n | 0 | t
+ ch2 | ch1_f3_not_null | n | 1 | f
+ ch3 | ch1_f3_not_null | n | 2 | f
+ part1 | part1_f1_not_null | n | 0 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | not null | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "ch1_f3_not_null" NOT NULL "f3" (inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch1
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (inherited)
+Inherits: ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 1 | f
+ ch3 | part1_f1_not_null | n | 1 | f
+(4 rows)
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+NOTICE: merging multiple inherited definitions of column "f1"
+create table ch3 () inherits (part1, ch1, ch2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 1 | f
+ ch2 | part1_f1_not_null | n | 3 | f
+ ch3 | part1_f1_not_null | n | 3 | f
+(4 rows)
+
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+ conrelid | conname | contype | coninhcount | conislocal
+----------+-------------------+---------+-------------+------------
+ part1 | part1_f1_not_null | n | 0 | t
+ ch1 | part1_f1_not_null | n | 0 | t
+ ch2 | part1_f1_not_null | n | 2 | t
+ ch3 | part1_f1_not_null | n | 2 | t
+(4 rows)
+
+\d+ ch1
+ Table "public.ch1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1"
+Inherits: part1
+Child tables: ch2,
+ ch3
+
+\d+ ch2
+ Table "public.ch2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1,
+ ch1,
+ part2
+Child tables: ch3
+
+\d+ ch3
+ Table "public.ch3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------------------+-----------+----------+---------+----------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+ f2 | text | | | | extended | |
+ f3 | integer | | | | plain | |
+ f4 | double precision | | | | plain | |
+Not-null constraints:
+ "part1_f1_not_null" NOT NULL "f1" (local, inherited)
+Inherits: part1,
+ ch1,
+ ch2
+
+drop table part1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ch1
+drop cascades to table ch2
+drop cascades to table ch3
+drop table part2 cascade;
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+ERROR: constraint "part1_f1_check" of relation "part1" is not a not-null constraint
+drop table part1;
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+ERROR: constraint "foo" of relation "part1" does not exist
+drop table part1;
+--
-- 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 30fba16..5378319 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -1091,6 +1091,170 @@ select conrelid::regclass, contype, conname,
drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
--
+-- Test - alter constraint inherit/no inherit for not null.
+--
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+/* Can't modify inheritability of inherited constraints */
+alter table ch1 alter constraint part1_f1_not_null set no inherit;
+/* alter constraint set no inherit should work on top-level constraint */
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test inherit constraint and make sure it validates.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+insert into ch1 values(NULL, 'sample', 1);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+delete from ch1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Test not null inherit constraint which already exists on child table.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter f1 set not null;
+\d+ ch1
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+
+drop table part1 cascade;
+
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int not null no inherit) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+alter table ch1 alter constraint ch1_f3_not_null set inherit;
+create table ch3 () inherits (part1, ch1, ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- Multilevel inheritance.
+create table part1 (f1 int not null no inherit);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+-- Test no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+
+-- If existing behavior is INHERIT.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (ch1);
+create table ch3 () inherits (ch2);
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+-- Set to no inherit.
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+
+drop table part1 cascade;
+
+-- Test no inherit when child has inherited constraint from multiple parents.
+create table part1 (f1 int not null);
+create table ch1 (f2 text, f3 int) inherits (part1);
+create table ch2 (f4 float) inherits (part1, ch1);
+create table ch3 () inherits (part1, ch1, ch2);
+create table part2(f1 int not null);
+alter table ch2 inherit part2;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+alter table part1 alter constraint part1_f1_not_null set no inherit;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'n' and
+ conrelid::regclass::text in ('part1', 'ch1', 'ch2', 'ch3')
+ order by 2, 1;
+\d+ ch1
+\d+ ch2
+\d+ ch3
+
+drop table part1 cascade;
+drop table part2 cascade;
+
+-- Negative scenarios for alter constraint .. set inherit.
+-- Other than not null constraints are not allowed to inherit.
+create table part1 (f1 int check(f1 > 5));
+alter table part1 alter constraint part1_f1_check set inherit;
+drop table part1;
+
+-- error out when provided not null constarint does not exists.
+create table part1(f1 int not null no inherit);
+alter table part1 alter constraint foo set inherit;
+drop table part1;
+
+--
-- Mixed ownership inheritance tree
--
create role regress_alice;
--
1.8.3.1
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 2025-Mar-03, Suraj Kharage wrote:
Thanks Alvaro for the review and fixup patch.
I agree with your changes and merged that into the main patch along with a
couple of other changes.Please find attached v6 for further review.
Thanks, I have pushed this. I made some changes to the tests, first by
renaming the tables to avoid too generic names, and second to try and
exercise everything about once.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 05.03.25 13:56, Alvaro Herrera wrote:
On 2025-Mar-03, Suraj Kharage wrote:
Thanks Alvaro for the review and fixup patch.
I agree with your changes and merged that into the main patch along with a
couple of other changes.Please find attached v6 for further review.
Thanks, I have pushed this. I made some changes to the tests, first by
renaming the tables to avoid too generic names, and second to try and
exercise everything about once.
A patch in the NOT ENFORCED constraints patch series proposes to
refactor some of the code added by this patch series ([0]/messages/by-id/CAAJ_b97aHsJgWhAuRQi1JdWsjzd_ygWEjqQVq_Ddo8dyCnnwkw@mail.gmail.com patch
v18-0001). I noticed that the code paths from this patch series do not
call InvokeObjectPostAlterHook() or CacheInvalidateRelcache() when a
constraint is altered. Was this intentional? If not, I can fix it as
part of that other patch, just wanted to check here.
[0]: /messages/by-id/CAAJ_b97aHsJgWhAuRQi1JdWsjzd_ygWEjqQVq_Ddo8dyCnnwkw@mail.gmail.com
/messages/by-id/CAAJ_b97aHsJgWhAuRQi1JdWsjzd_ygWEjqQVq_Ddo8dyCnnwkw@mail.gmail.com
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
Hello
On 2025-Mar-25, Peter Eisentraut wrote:
A patch in the NOT ENFORCED constraints patch series proposes to refactor
some of the code added by this patch series ([0] patch v18-0001). I noticed
that the code paths from this patch series do not call
InvokeObjectPostAlterHook() or CacheInvalidateRelcache() when a constraint
is altered. Was this intentional? If not, I can fix it as part of that
other patch, just wanted to check here.
It was not, I'm good with you fixing it, with thanks.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"The ability of users to misuse tools is, of course, legendary" (David Steele)
/messages/by-id/11b38a96-6ded-4668-b772-40f992132797@pgmasters.net
Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints
On 25.03.25 12:52, Alvaro Herrera wrote:
Hello
On 2025-Mar-25, Peter Eisentraut wrote:
A patch in the NOT ENFORCED constraints patch series proposes to refactor
some of the code added by this patch series ([0] patch v18-0001). I noticed
that the code paths from this patch series do not call
InvokeObjectPostAlterHook() or CacheInvalidateRelcache() when a constraint
is altered. Was this intentional? If not, I can fix it as part of that
other patch, just wanted to check here.It was not, I'm good with you fixing it, with thanks.
done