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 ] name ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] + ALTER CONSTRAINT constraint_name SET [INHERIT | NO INHERIT] VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] @@ -556,11 +557,31 @@ WITH ( MODULUS numeric_literal, REM 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. + + ALTER CONSTRAINT ... SET INHERIT + ALTER CONSTRAINT ... SET NO INHERIT + + + 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. + + + + VALIDATE CONSTRAINT 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 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 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 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;