Skip to content

Commit 61d81bd

Browse files
alvherrealvherre
committed
Allow CHECK constraints to be declared ONLY
This makes them enforceable only on the parent table, not on children tables. This is useful in various situations, per discussion involving people bitten by the restrictive behavior introduced in 8.4. Message-Id: 8762mp93iw.fsf@comcast.net CAFaPBrSMMpubkGf4zcRL_YL-AERUbYF_-ZNNYfb3CVwwEqc9TQ@mail.gmail.com Authors: Nikhil Sontakke, Alex Hunsaker Reviewed by Robert Haas and myself
1 parent 9220362 commit 61d81bd

File tree

19 files changed

+211
-78
lines changed

19 files changed

+211
-78
lines changed

‎doc/src/sgml/catalogs.sgml

+10
Original file line numberDiff line numberDiff line change
@@ -2036,6 +2036,16 @@
20362036
</entry>
20372037
</row>
20382038

2039+
<row>
2040+
<entry><structfield>conisonly</structfield></entry>
2041+
<entry><type>bool</type></entry>
2042+
<entry></entry>
2043+
<entry>
2044+
This constraint is defined locally for the relation. It is a
2045+
non-inheritable constraint.
2046+
</entry>
2047+
</row>
2048+
20392049
<row>
20402050
<entry><structfield>conkey</structfield></entry>
20412051
<entry><type>int2[]</type></entry>

‎doc/src/sgml/ref/alter_table.sgml

+8
Original file line numberDiff line numberDiff line change
@@ -983,6 +983,14 @@ ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
983983
</programlisting>
984984
</para>
985985

986+
<para>
987+
To add a check constraint only to a table and not to its children:
988+
<programlisting>
989+
ALTER TABLE ONLY distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
990+
</programlisting>
991+
(The check constraint will not be inherited by future children, either.)
992+
</para>
993+
986994
<para>
987995
To remove a check constraint from a table and all its children:
988996
<programlisting>

‎src/backend/catalog/heap.c

+19-9
Original file line numberDiff line numberDiff line change
@@ -92,10 +92,10 @@ static Oid AddNewRelationType(const char *typeName,
9292
Oid new_array_type);
9393
static void RelationRemoveInheritance(Oid relid);
9494
static void StoreRelCheck(Relation rel, char *ccname, Node *expr,
95-
bool is_validated, bool is_local, int inhcount);
95+
bool is_validated, bool is_local, int inhcount, bool is_only);
9696
static void StoreConstraints(Relation rel, List *cooked_constraints);
9797
static bool MergeWithExistingConstraint(Relation rel, char *ccname, Node *expr,
98-
bool allow_merge, bool is_local);
98+
bool allow_merge, bool is_local, bool is_only);
9999
static void SetRelationNumChecks(Relation rel, int numchecks);
100100
static Node *cookConstraint(ParseState *pstate,
101101
Node *raw_constraint,
@@ -1859,7 +1859,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr)
18591859
*/
18601860
static void
18611861
StoreRelCheck(Relation rel, char *ccname, Node *expr,
1862-
bool is_validated, bool is_local, int inhcount)
1862+
bool is_validated, bool is_local, int inhcount, bool is_only)
18631863
{
18641864
char *ccbin;
18651865
char *ccsrc;
@@ -1942,7 +1942,8 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
19421942
ccbin, /* Binary form of check constraint */
19431943
ccsrc, /* Source form of check constraint */
19441944
is_local, /* conislocal */
1945-
inhcount); /* coninhcount */
1945+
inhcount, /* coninhcount */
1946+
is_only); /* conisonly */
19461947

19471948
pfree(ccbin);
19481949
pfree(ccsrc);
@@ -1983,7 +1984,7 @@ StoreConstraints(Relation rel, List *cooked_constraints)
19831984
break;
19841985
case CONSTR_CHECK:
19851986
StoreRelCheck(rel, con->name, con->expr, !con->skip_validation,
1986-
con->is_local, con->inhcount);
1987+
con->is_local, con->inhcount, con->is_only);
19871988
numchecks++;
19881989
break;
19891990
default:
@@ -2026,7 +2027,8 @@ AddRelationNewConstraints(Relation rel,
20262027
List *newColDefaults,
20272028
List *newConstraints,
20282029
bool allow_merge,
2029-
bool is_local)
2030+
bool is_local,
2031+
bool is_only)
20302032
{
20312033
List *cookedConstraints = NIL;
20322034
TupleDesc tupleDesc;
@@ -2099,6 +2101,7 @@ AddRelationNewConstraints(Relation rel,
20992101
cooked->skip_validation = false;
21002102
cooked->is_local = is_local;
21012103
cooked->inhcount = is_local ? 0 : 1;
2104+
cooked->is_only = is_only;
21022105
cookedConstraints = lappend(cookedConstraints, cooked);
21032106
}
21042107

@@ -2166,7 +2169,7 @@ AddRelationNewConstraints(Relation rel,
21662169
* what ATAddCheckConstraint wants.)
21672170
*/
21682171
if (MergeWithExistingConstraint(rel, ccname, expr,
2169-
allow_merge, is_local))
2172+
allow_merge, is_local, is_only))
21702173
continue;
21712174
}
21722175
else
@@ -2213,7 +2216,7 @@ AddRelationNewConstraints(Relation rel,
22132216
* OK, store it.
22142217
*/
22152218
StoreRelCheck(rel, ccname, expr, !cdef->skip_validation, is_local,
2216-
is_local ? 0 : 1);
2219+
is_local ? 0 : 1, is_only);
22172220

22182221
numchecks++;
22192222

@@ -2225,6 +2228,7 @@ AddRelationNewConstraints(Relation rel,
22252228
cooked->skip_validation = cdef->skip_validation;
22262229
cooked->is_local = is_local;
22272230
cooked->inhcount = is_local ? 0 : 1;
2231+
cooked->is_only = is_only;
22282232
cookedConstraints = lappend(cookedConstraints, cooked);
22292233
}
22302234

@@ -2250,7 +2254,8 @@ AddRelationNewConstraints(Relation rel,
22502254
*/
22512255
static bool
22522256
MergeWithExistingConstraint(Relation rel, char *ccname, Node *expr,
2253-
bool allow_merge, bool is_local)
2257+
bool allow_merge, bool is_local,
2258+
bool is_only)
22542259
{
22552260
bool found;
22562261
Relation conDesc;
@@ -2312,6 +2317,11 @@ MergeWithExistingConstraint(Relation rel, char *ccname, Node *expr,
23122317
con->conislocal = true;
23132318
else
23142319
con->coninhcount++;
2320+
if (is_only)
2321+
{
2322+
Assert(is_local);
2323+
con->conisonly = true;
2324+
}
23152325
simple_heap_update(conDesc, &tup->t_self, tup);
23162326
CatalogUpdateIndexes(conDesc, tup);
23172327
break;

‎src/backend/catalog/index.c

+2-1
Original file line numberDiff line numberDiff line change
@@ -1155,7 +1155,8 @@ index_constraint_create(Relation heapRelation,
11551155
NULL,
11561156
NULL,
11571157
true, /* islocal */
1158-
0); /* inhcount */
1158+
0, /* inhcount */
1159+
false); /* isonly */
11591160

11601161
/*
11611162
* Register the index as internally dependent on the constraint.

‎src/backend/catalog/pg_constraint.c

+3-1
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,8 @@ CreateConstraintEntry(const char *constraintName,
6666
const char *conBin,
6767
const char *conSrc,
6868
bool conIsLocal,
69-
int conInhCount)
69+
int conInhCount,
70+
bool conIsOnly)
7071
{
7172
Relation conDesc;
7273
Oid conOid;
@@ -169,6 +170,7 @@ CreateConstraintEntry(const char *constraintName,
169170
values[Anum_pg_constraint_confmatchtype - 1] = CharGetDatum(foreignMatchType);
170171
values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal);
171172
values[Anum_pg_constraint_coninhcount - 1] = Int32GetDatum(conInhCount);
173+
values[Anum_pg_constraint_conisonly - 1] = BoolGetDatum(conIsOnly);
172174

173175
if (conkeyArray)
174176
values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray);

‎src/backend/commands/tablecmds.c

+35-15
Original file line numberDiff line numberDiff line change
@@ -579,6 +579,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
579579
cooked->skip_validation = false;
580580
cooked->is_local = true; /* not used for defaults */
581581
cooked->inhcount = 0; /* ditto */
582+
cooked->is_only = false;
582583
cookedDefaults = lappend(cookedDefaults, cooked);
583584
descriptor->attrs[attnum - 1]->atthasdef = true;
584585
}
@@ -638,7 +639,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
638639
*/
639640
if (rawDefaults || stmt->constraints)
640641
AddRelationNewConstraints(rel, rawDefaults, stmt->constraints,
641-
true, true);
642+
true, true, false);
642643

643644
/*
644645
* Clean up. We keep lock on new relation (although it shouldn't be
@@ -1599,6 +1600,10 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
15991600
char *name = check[i].ccname;
16001601
Node *expr;
16011602

1603+
/* ignore if the constraint is non-inheritable */
1604+
if (check[i].cconly)
1605+
continue;
1606+
16021607
/* adjust varattnos of ccbin here */
16031608
expr = stringToNode(check[i].ccbin);
16041609
change_varattnos_of_a_node(expr, newattno);
@@ -1617,6 +1622,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
16171622
cooked->skip_validation = false;
16181623
cooked->is_local = false;
16191624
cooked->inhcount = 1;
1625+
cooked->is_only = false;
16201626
constraints = lappend(constraints, cooked);
16211627
}
16221628
}
@@ -4501,7 +4507,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
45014507
* This function is intended for CREATE TABLE, so it processes a
45024508
* _list_ of defaults, but we just do one.
45034509
*/
4504-
AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true);
4510+
AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true, false);
45054511

45064512
/* Make the additional catalog changes visible */
45074513
CommandCounterIncrement();
@@ -4898,7 +4904,7 @@ ATExecColumnDefault(Relation rel, const char *colName,
48984904
* This function is intended for CREATE TABLE, so it processes a
48994905
* _list_ of defaults, but we just do one.
49004906
*/
4901-
AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true);
4907+
AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true, false);
49024908
}
49034909
}
49044910

@@ -5562,10 +5568,16 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
55625568
* omitted from the returned list, which is what we want: we do not need
55635569
* to do any validation work. That can only happen at child tables,
55645570
* though, since we disallow merging at the top level.
5571+
*
5572+
* Note: we set is_only based on the recurse flag which is false when
5573+
* interpretInhOption() of our statement returns false all the way up
5574+
* in AlterTable and gets passed all the way down to here.
55655575
*/
55665576
newcons = AddRelationNewConstraints(rel, NIL,
55675577
list_make1(copyObject(constr)),
5568-
recursing, !recursing);
5578+
recursing, /* allow_merge */
5579+
!recursing, /* is_local */
5580+
!recurse && !recursing); /* is_only */
55695581

55705582
/* Add each to-be-validated constraint to Phase 3's queue */
55715583
foreach(lcon, newcons)
@@ -5605,22 +5617,19 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
56055617
if (newcons == NIL)
56065618
return;
56075619

5620+
/*
5621+
* Adding an ONLY constraint? No need to find our children
5622+
*/
5623+
if (!recurse && !recursing)
5624+
return;
5625+
56085626
/*
56095627
* Propagate to children as appropriate. Unlike most other ALTER
56105628
* routines, we have to do this one level of recursion at a time; we can't
56115629
* use find_all_inheritors to do it in one pass.
56125630
*/
56135631
children = find_inheritance_children(RelationGetRelid(rel), lockmode);
56145632

5615-
/*
5616-
* If we are told not to recurse, there had better not be any child
5617-
* tables; else the addition would put them out of step.
5618-
*/
5619-
if (children && !recurse)
5620-
ereport(ERROR,
5621-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
5622-
errmsg("constraint must be added to child tables too")));
5623-
56245633
foreach(child, children)
56255634
{
56265635
Oid childrelid = lfirst_oid(child);
@@ -5914,7 +5923,8 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
59145923
NULL,
59155924
NULL,
59165925
true, /* islocal */
5917-
0); /* inhcount */
5926+
0, /* inhcount */
5927+
false); /* isonly */
59185928

59195929
/*
59205930
* Create the triggers that will enforce the constraint.
@@ -6755,6 +6765,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
67556765
HeapTuple tuple;
67566766
bool found = false;
67576767
bool is_check_constraint = false;
6768+
bool is_only_constraint = false;
67586769

67596770
/* At top level, permission check was done in ATPrepCmd, else do it */
67606771
if (recursing)
@@ -6791,6 +6802,12 @@ ATExecDropConstraint(Relation rel, const char *constrName,
67916802
/* Right now only CHECK constraints can be inherited */
67926803
if (con->contype == CONSTRAINT_CHECK)
67936804
is_check_constraint = true;
6805+
6806+
if (con->conisonly)
6807+
{
6808+
Assert(is_check_constraint);
6809+
is_only_constraint = true;
6810+
}
67946811

67956812
/*
67966813
* Perform the actual constraint deletion
@@ -6802,6 +6819,9 @@ ATExecDropConstraint(Relation rel, const char *constrName,
68026819
performDeletion(&conobj, behavior);
68036820

68046821
found = true;
6822+
6823+
/* constraint found and dropped -- no need to keep looping */
6824+
break;
68056825
}
68066826

68076827
systable_endscan(scan);
@@ -6830,7 +6850,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
68306850
* routines, we have to do this one level of recursion at a time; we can't
68316851
* use find_all_inheritors to do it in one pass.
68326852
*/
6833-
if (is_check_constraint)
6853+
if (is_check_constraint && !is_only_constraint)
68346854
children = find_inheritance_children(RelationGetRelid(rel), lockmode);
68356855
else
68366856
children = NIL;

‎src/backend/commands/trigger.c

+2-1
Original file line numberDiff line numberDiff line change
@@ -449,7 +449,8 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
449449
NULL,
450450
NULL,
451451
true, /* islocal */
452-
0); /* inhcount */
452+
0, /* inhcount */
453+
false); /* isonly */
453454
}
454455

455456
/*

‎src/backend/commands/typecmds.c

+2-1
Original file line numberDiff line numberDiff line change
@@ -2934,7 +2934,8 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
29342934
ccbin, /* Binary form of check constraint */
29352935
ccsrc, /* Source form of check constraint */
29362936
true, /* is local */
2937-
0); /* inhcount */
2937+
0, /* inhcount */
2938+
false); /* is only */
29382939

29392940
/*
29402941
* Return the compiled constraint expression so the calling routine can

‎src/backend/utils/cache/relcache.c

+1
Original file line numberDiff line numberDiff line change
@@ -3261,6 +3261,7 @@ CheckConstraintFetch(Relation relation)
32613261
RelationGetRelationName(relation));
32623262

32633263
check[found].ccvalid = conform->convalidated;
3264+
check[found].cconly = conform->conisonly;
32643265
check[found].ccname = MemoryContextStrdup(CacheMemoryContext,
32653266
NameStr(conform->conname));
32663267

0 commit comments

Comments
 (0)