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

Commitcb3a7c2

Browse files
committed
ALTER TABLE: skip FK validation when it's safe to do so
We already skip rewriting the table in these cases, but we still force awhole table scan to validate the data. This can be skipped, and thuswe can make the whole ALTER TABLE operation just do some catalog touchesinstead of scanning the table, when these two conditions hold:(a) Old and new pg_constraint.conpfeqop match exactly. This is actuallystronger than needed; we could loosen things by way of operatorfamilies, but it'd require a lot more effort.(b) The functions, if any, implementing a cast from the foreign type tothe primary opcintype are the same. For this purpose, we can consider abinary coercion equivalent to an exact type match. When the opcintypeis polymorphic, require that the old and new foreign types matchexactly. (Since ri_triggers.c does use the executor, the stronger checkfor polymorphic types is no mere future-proofing. However, no core typeexercises its necessity.)Author: Noah MischCommitter's note: catalog version bumped due to change of the Constraintnode. I can't actually find any way to have such a node in a storedrule, but given that we have "out" support for them, better be safe.
1 parent9bf8603 commitcb3a7c2

File tree

7 files changed

+187
-6
lines changed

7 files changed

+187
-6
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 181 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -276,6 +276,8 @@ static Oid transformFkeyCheckAttrs(Relation pkrel,
276276
intnumattrs,int16*attnums,
277277
Oid*opclasses);
278278
staticvoidcheckFkeyPermissions(Relationrel,int16*attnums,intnatts);
279+
staticCoercionPathTypefindFkeyCast(OidtargetTypeId,OidsourceTypeId,
280+
Oid*funcid);
279281
staticvoidvalidateCheckConstraint(Relationrel,HeapTupleconstrtup);
280282
staticvoidvalidateForeignKeyConstraint(char*conname,
281283
Relationrel,Relationpkrel,
@@ -358,6 +360,7 @@ static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMOD
358360
staticvoidATPostAlterTypeParse(OidoldId,char*cmd,
359361
List**wqueue,LOCKMODElockmode,boolrewrite);
360362
staticvoidTryReuseIndex(OidoldId,IndexStmt*stmt);
363+
staticvoidTryReuseForeignKey(OidoldId,Constraint*con);
361364
staticvoidchange_owner_fix_column_acls(OidrelationOid,
362365
OidoldOwnerId,OidnewOwnerId);
363366
staticvoidchange_owner_recurse_to_sequences(OidrelationOid,
@@ -5620,6 +5623,8 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
56205623
numpks;
56215624
OidindexOid;
56225625
OidconstrOid;
5626+
boolold_check_ok;
5627+
ListCell*old_pfeqop_item=list_head(fkconstraint->old_conpfeqop);
56235628

56245629
/*
56255630
* Grab an exclusive lock on the pk table, so that someone doesn't delete
@@ -5736,6 +5741,13 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
57365741
(errcode(ERRCODE_INVALID_FOREIGN_KEY),
57375742
errmsg("number of referencing and referenced columns for foreign key disagree")));
57385743

5744+
/*
5745+
* On the strength of a previous constraint, we might avoid scanning
5746+
* tables to validate this one. See below.
5747+
*/
5748+
old_check_ok= (fkconstraint->old_conpfeqop!=NIL);
5749+
Assert(!old_check_ok||numfks==list_length(fkconstraint->old_conpfeqop));
5750+
57395751
for (i=0;i<numpks;i++)
57405752
{
57415753
Oidpktype=pktypoid[i];
@@ -5750,6 +5762,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
57505762
Oidppeqop;
57515763
Oidffeqop;
57525764
int16eqstrategy;
5765+
Oidpfeqop_right;
57535766

57545767
/* We need several fields out of the pg_opclass entry */
57555768
cla_ht=SearchSysCache1(CLAOID,ObjectIdGetDatum(opclasses[i]));
@@ -5792,10 +5805,17 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
57925805
pfeqop=get_opfamily_member(opfamily,opcintype,fktyped,
57935806
eqstrategy);
57945807
if (OidIsValid(pfeqop))
5808+
{
5809+
pfeqop_right=fktyped;
57955810
ffeqop=get_opfamily_member(opfamily,fktyped,fktyped,
57965811
eqstrategy);
5812+
}
57975813
else
5798-
ffeqop=InvalidOid;/* keep compiler quiet */
5814+
{
5815+
/* keep compiler quiet */
5816+
pfeqop_right=InvalidOid;
5817+
ffeqop=InvalidOid;
5818+
}
57995819

58005820
if (!(OidIsValid(pfeqop)&&OidIsValid(ffeqop)))
58015821
{
@@ -5817,7 +5837,10 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
58175837
target_typeids[1]=opcintype;
58185838
if (can_coerce_type(2,input_typeids,target_typeids,
58195839
COERCION_IMPLICIT))
5840+
{
58205841
pfeqop=ffeqop=ppeqop;
5842+
pfeqop_right=opcintype;
5843+
}
58215844
}
58225845

58235846
if (!(OidIsValid(pfeqop)&&OidIsValid(ffeqop)))
@@ -5833,6 +5856,77 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
58335856
format_type_be(fktype),
58345857
format_type_be(pktype))));
58355858

5859+
if (old_check_ok)
5860+
{
5861+
/*
5862+
* When a pfeqop changes, revalidate the constraint. We could
5863+
* permit intra-opfamily changes, but that adds subtle complexity
5864+
* without any concrete benefit for core types. We need not
5865+
* assess ppeqop or ffeqop, which RI_Initial_Check() does not use.
5866+
*/
5867+
old_check_ok= (pfeqop==lfirst_oid(old_pfeqop_item));
5868+
old_pfeqop_item=lnext(old_pfeqop_item);
5869+
}
5870+
if (old_check_ok)
5871+
{
5872+
Oidold_fktype;
5873+
Oidnew_fktype;
5874+
CoercionPathTypeold_pathtype;
5875+
CoercionPathTypenew_pathtype;
5876+
Oidold_castfunc;
5877+
Oidnew_castfunc;
5878+
5879+
/*
5880+
* Identify coercion pathways from each of the old and new FK-side
5881+
* column types to the right (foreign) operand type of the pfeqop.
5882+
* We may assume that pg_constraint.conkey is not changing.
5883+
*/
5884+
old_fktype=tab->oldDesc->attrs[fkattnum[i]-1]->atttypid;
5885+
new_fktype=fktype;
5886+
old_pathtype=findFkeyCast(pfeqop_right,old_fktype,
5887+
&old_castfunc);
5888+
new_pathtype=findFkeyCast(pfeqop_right,new_fktype,
5889+
&new_castfunc);
5890+
5891+
/*
5892+
* Upon a change to the cast from the FK column to its pfeqop
5893+
* operand, revalidate the constraint. For this evaluation, a
5894+
* binary coercion cast is equivalent to no cast at all. While
5895+
* type implementors should design implicit casts with an eye
5896+
* toward consistency of operations like equality, we cannot assume
5897+
* here that they have done so.
5898+
*
5899+
* A function with a polymorphic argument could change behavior
5900+
* arbitrarily in response to get_fn_expr_argtype(). Therefore,
5901+
* when the cast destination is polymorphic, we only avoid
5902+
* revalidation if the input type has not changed at all. Given
5903+
* just the core data types and operator classes, this requirement
5904+
* prevents no would-be optimizations.
5905+
*
5906+
* If the cast converts from a base type to a domain thereon, then
5907+
* that domain type must be the opcintype of the unique index.
5908+
* Necessarily, the primary key column must then be of the domain
5909+
* type. Since the constraint was previously valid, all values on
5910+
* the foreign side necessarily exist on the primary side and in
5911+
* turn conform to the domain. Consequently, we need not treat
5912+
* domains specially here.
5913+
*
5914+
* Since we require that all collations share the same notion of
5915+
* equality (which they do, because texteq reduces to bitwise
5916+
* equality), we don't compare collation here.
5917+
*
5918+
* We need not directly consider the PK type. It's necessarily
5919+
* binary coercible to the opcintype of the unique index column,
5920+
* and ri_triggers.c will only deal with PK datums in terms of that
5921+
* opcintype. Changing the opcintype also changes pfeqop.
5922+
*/
5923+
old_check_ok= (new_pathtype==old_pathtype&&
5924+
new_castfunc==old_castfunc&&
5925+
(!IsPolymorphicType(pfeqop_right)||
5926+
new_fktype==old_fktype));
5927+
5928+
}
5929+
58365930
pfeqoperators[i]=pfeqop;
58375931
ppeqoperators[i]=ppeqop;
58385932
ffeqoperators[i]=ffeqop;
@@ -5877,10 +5971,12 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
58775971

58785972
/*
58795973
* Tell Phase 3 to check that the constraint is satisfied by existing rows.
5880-
* We can skip this during table creation, or if requested explicitly by
5881-
* specifying NOT VALID in an ADD FOREIGN KEY command.
5974+
* We can skip this during table creation, when requested explicitly by
5975+
* specifying NOT VALID in an ADD FOREIGN KEY command, and when we're
5976+
* recreating a constraint following a SET DATA TYPE operation that did not
5977+
* impugn its validity.
58825978
*/
5883-
if (!fkconstraint->skip_validation)
5979+
if (!old_check_ok&& !fkconstraint->skip_validation)
58845980
{
58855981
NewConstraint*newcon;
58865982

@@ -6330,6 +6426,35 @@ transformFkeyCheckAttrs(Relation pkrel,
63306426
returnindexoid;
63316427
}
63326428

6429+
/*
6430+
* findFkeyCast -
6431+
*
6432+
*Wrapper around find_coercion_pathway() for ATAddForeignKeyConstraint().
6433+
*Caller has equal regard for binary coercibility and for an exact match.
6434+
*/
6435+
staticCoercionPathType
6436+
findFkeyCast(OidtargetTypeId,OidsourceTypeId,Oid*funcid)
6437+
{
6438+
CoercionPathTyperet;
6439+
6440+
if (targetTypeId==sourceTypeId)
6441+
{
6442+
ret=COERCION_PATH_RELABELTYPE;
6443+
*funcid=InvalidOid;
6444+
}
6445+
else
6446+
{
6447+
ret=find_coercion_pathway(targetTypeId,sourceTypeId,
6448+
COERCION_IMPLICIT,funcid);
6449+
if (ret==COERCION_PATH_NONE)
6450+
/* A previously-relied-upon cast is now gone. */
6451+
elog(ERROR,"could not find cast from %u to %u",
6452+
sourceTypeId,targetTypeId);
6453+
}
6454+
6455+
returnret;
6456+
}
6457+
63336458
/* Permissions checks for ADD FOREIGN KEY */
63346459
staticvoid
63356460
checkFkeyPermissions(Relationrel,int16*attnums,intnatts)
@@ -7717,6 +7842,7 @@ ATPostAlterTypeParse(Oid oldId, char *cmd,
77177842
foreach(lcmd,stmt->cmds)
77187843
{
77197844
AlterTableCmd*cmd= (AlterTableCmd*)lfirst(lcmd);
7845+
Constraint*con;
77207846

77217847
switch (cmd->subtype)
77227848
{
@@ -7730,6 +7856,12 @@ ATPostAlterTypeParse(Oid oldId, char *cmd,
77307856
lappend(tab->subcmds[AT_PASS_OLD_INDEX],cmd);
77317857
break;
77327858
caseAT_AddConstraint:
7859+
Assert(IsA(cmd->def,Constraint));
7860+
con= (Constraint*)cmd->def;
7861+
/* rewriting neither side of a FK */
7862+
if (con->contype==CONSTR_FOREIGN&&
7863+
!rewrite&& !tab->rewrite)
7864+
TryReuseForeignKey(oldId,con);
77337865
tab->subcmds[AT_PASS_OLD_CONSTR]=
77347866
lappend(tab->subcmds[AT_PASS_OLD_CONSTR],cmd);
77357867
break;
@@ -7751,7 +7883,7 @@ ATPostAlterTypeParse(Oid oldId, char *cmd,
77517883
/*
77527884
* Subroutine for ATPostAlterTypeParse(). Calls out to CheckIndexCompatible()
77537885
* for the real analysis, then mutates the IndexStmt based on that verdict.
7754-
*/
7886+
*/
77557887
staticvoid
77567888
TryReuseIndex(OidoldId,IndexStmt*stmt)
77577889
{
@@ -7768,6 +7900,50 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt)
77687900
}
77697901
}
77707902

7903+
/*
7904+
* Subroutine for ATPostAlterTypeParse().
7905+
*
7906+
* Stash the old P-F equality operator into the Constraint node, for possible
7907+
* use by ATAddForeignKeyConstraint() in determining whether revalidation of
7908+
* this constraint can be skipped.
7909+
*/
7910+
staticvoid
7911+
TryReuseForeignKey(OidoldId,Constraint*con)
7912+
{
7913+
HeapTupletup;
7914+
Datumadatum;
7915+
boolisNull;
7916+
ArrayType*arr;
7917+
Oid*rawarr;
7918+
intnumkeys;
7919+
inti;
7920+
7921+
Assert(con->contype==CONSTR_FOREIGN);
7922+
Assert(con->old_conpfeqop==NIL);/* already prepared this node */
7923+
7924+
tup=SearchSysCache1(CONSTROID,ObjectIdGetDatum(oldId));
7925+
if (!HeapTupleIsValid(tup))/* should not happen */
7926+
elog(ERROR,"cache lookup failed for constraint %u",oldId);
7927+
7928+
adatum=SysCacheGetAttr(CONSTROID,tup,
7929+
Anum_pg_constraint_conpfeqop,&isNull);
7930+
if (isNull)
7931+
elog(ERROR,"null conpfeqop for constraint %u",oldId);
7932+
arr=DatumGetArrayTypeP(adatum);/* ensure not toasted */
7933+
numkeys=ARR_DIMS(arr)[0];
7934+
/* test follows the one in ri_FetchConstraintInfo() */
7935+
if (ARR_NDIM(arr)!=1||
7936+
ARR_HASNULL(arr)||
7937+
ARR_ELEMTYPE(arr)!=OIDOID)
7938+
elog(ERROR,"conpfeqop is not a 1-D Oid array");
7939+
rawarr= (Oid*)ARR_DATA_PTR(arr);
7940+
7941+
/* stash a List of the operator Oids in our Constraint node */
7942+
for (i=0;i<numkeys;i++)
7943+
con->old_conpfeqop=lcons_oid(rawarr[i],con->old_conpfeqop);
7944+
7945+
ReleaseSysCache(tup);
7946+
}
77717947

77727948
/*
77737949
* ALTER TABLE OWNER

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2364,6 +2364,7 @@ _copyConstraint(const Constraint *from)
23642364
COPY_SCALAR_FIELD(fk_matchtype);
23652365
COPY_SCALAR_FIELD(fk_upd_action);
23662366
COPY_SCALAR_FIELD(fk_del_action);
2367+
COPY_NODE_FIELD(old_conpfeqop);
23672368
COPY_SCALAR_FIELD(skip_validation);
23682369
COPY_SCALAR_FIELD(initially_valid);
23692370

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2199,6 +2199,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
21992199
COMPARE_SCALAR_FIELD(fk_matchtype);
22002200
COMPARE_SCALAR_FIELD(fk_upd_action);
22012201
COMPARE_SCALAR_FIELD(fk_del_action);
2202+
COMPARE_NODE_FIELD(old_conpfeqop);
22022203
COMPARE_SCALAR_FIELD(skip_validation);
22032204
COMPARE_SCALAR_FIELD(initially_valid);
22042205

‎src/backend/nodes/outfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2626,6 +2626,7 @@ _outConstraint(StringInfo str, const Constraint *node)
26262626
WRITE_CHAR_FIELD(fk_matchtype);
26272627
WRITE_CHAR_FIELD(fk_upd_action);
26282628
WRITE_CHAR_FIELD(fk_del_action);
2629+
WRITE_NODE_FIELD(old_conpfeqop);
26292630
WRITE_BOOL_FIELD(skip_validation);
26302631
WRITE_BOOL_FIELD(initially_valid);
26312632
break;

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3224,6 +3224,7 @@ ri_FetchConstraintInfo(RI_ConstraintInfo *riinfo,
32243224
elog(ERROR,"null conpfeqop for constraint %u",constraintOid);
32253225
arr=DatumGetArrayTypeP(adatum);/* ensure not toasted */
32263226
numkeys=ARR_DIMS(arr)[0];
3227+
/* see TryReuseForeignKey if you change the test below */
32273228
if (ARR_NDIM(arr)!=1||
32283229
numkeys!=riinfo->nkeys||
32293230
numkeys>RI_MAX_NUMKEYS||

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201202191
56+
#defineCATALOG_VERSION_NO201202271
5757

5858
#endif

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1552,6 +1552,7 @@ typedef struct Constraint
15521552
charfk_matchtype;/* FULL, PARTIAL, UNSPECIFIED */
15531553
charfk_upd_action;/* ON UPDATE action */
15541554
charfk_del_action;/* ON DELETE action */
1555+
List*old_conpfeqop;/* pg_constraint.conpfeqop of my former self */
15551556

15561557
/* Fields used for constraints that allow a NOT VALID specification */
15571558
boolskip_validation;/* skip validation of existing rows? */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp