v6-0001-alter-not-null-constraint-from-inherit-to-no-inhe.patch
application/octet-stream
Filename: v6-0001-alter-not-null-constraint-from-inherit-to-no-inhe.patch
Type: application/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch v6-0001
Subject: alter not null constraint from inherit to no inherit and vice versa.
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/alter_table.sgml | 22 | 1 |
| src/backend/commands/tablecmds.c | 100 | 15 |
| src/backend/parser/gram.y | 28 | 0 |
| src/include/nodes/parsenodes.h | 2 | 0 |
| src/test/regress/expected/inherit.out | 585 | 0 |
| src/test/regress/sql/inherit.sql | 164 | 0 |
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