Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit61d81bd

Browse files
committed
Allow CHECK constraints to be declared ONLY
This makes them enforceable only on the parent table, not on childrentables. This is useful in various situations, per discussion involvingpeople bitten by the restrictive behavior introduced in 8.4.Message-Id:8762mp93iw.fsf@comcast.netCAFaPBrSMMpubkGf4zcRL_YL-AERUbYF_-ZNNYfb3CVwwEqc9TQ@mail.gmail.comAuthors: Nikhil Sontakke, Alex HunsakerReviewed by Robert Haas and myself
1 parent9220362 commit61d81bd

File tree

19 files changed

+211
-78
lines changed

19 files changed

+211
-78
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 10 additions & 0 deletions
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

Lines changed: 8 additions & 0 deletions
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

Lines changed: 19 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -92,10 +92,10 @@ static Oid AddNewRelationType(const char *typeName,
9292
Oidnew_array_type);
9393
staticvoidRelationRemoveInheritance(Oidrelid);
9494
staticvoidStoreRelCheck(Relationrel,char*ccname,Node*expr,
95-
boolis_validated,boolis_local,intinhcount);
95+
boolis_validated,boolis_local,intinhcount,boolis_only);
9696
staticvoidStoreConstraints(Relationrel,List*cooked_constraints);
9797
staticboolMergeWithExistingConstraint(Relationrel,char*ccname,Node*expr,
98-
boolallow_merge,boolis_local);
98+
boolallow_merge,boolis_local,boolis_only);
9999
staticvoidSetRelationNumChecks(Relationrel,intnumchecks);
100100
staticNode*cookConstraint(ParseState*pstate,
101101
Node*raw_constraint,
@@ -1859,7 +1859,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr)
18591859
*/
18601860
staticvoid
18611861
StoreRelCheck(Relationrel,char*ccname,Node*expr,
1862-
boolis_validated,boolis_local,intinhcount)
1862+
boolis_validated,boolis_local,intinhcount,boolis_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
caseCONSTR_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
boolallow_merge,
2029-
boolis_local)
2030+
boolis_local,
2031+
boolis_only)
20302032
{
20312033
List*cookedConstraints=NIL;
20322034
TupleDesctupleDesc;
@@ -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
staticbool
22522256
MergeWithExistingConstraint(Relationrel,char*ccname,Node*expr,
2253-
boolallow_merge,boolis_local)
2257+
boolallow_merge,boolis_local,
2258+
boolis_only)
22542259
{
22552260
boolfound;
22562261
RelationconDesc;
@@ -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

Lines changed: 2 additions & 1 deletion
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

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,8 @@ CreateConstraintEntry(const char *constraintName,
6666
constchar*conBin,
6767
constchar*conSrc,
6868
boolconIsLocal,
69-
intconInhCount)
69+
intconInhCount,
70+
boolconIsOnly)
7071
{
7172
RelationconDesc;
7273
OidconOid;
@@ -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

Lines changed: 35 additions & 15 deletions
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
Oidchildrelid=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
HeapTupletuple;
67566766
boolfound= false;
67576767
boolis_check_constraint= false;
6768+
boolis_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

Lines changed: 2 additions & 1 deletion
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

Lines changed: 2 additions & 1 deletion
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

Lines changed: 1 addition & 0 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp