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

Commitc3ffe34

Browse files
committed
Avoid creating duplicate cached plans for inherited FK constraints.
When a foreign key constraint is applied to a partitioned table, eachleaf partition inherits a similar FK constraint. We were processing allof those constraints independently, meaning that in large partitioningtrees we'd build up large collections of cached FK-checking query plans.However, in all cases but one, the generated queries are actuallyidentical for all members of the inheritance tree (because, in mostcases, the query only mentions the topmost table of the other side ofthe FK relationship). So we can share a single cached plan among allthe partitions, saving memory, not to mention time to build and maintainthe cached plans.Keisuke Kuroda and Amit LangoteDiscussion:https://postgr.es/m/cab4b85d-9292-967d-adf2-be0d803c3e23@nttcom.co.jp_1
1 parentb124363 commitc3ffe34

File tree

3 files changed

+97
-4
lines changed

3 files changed

+97
-4
lines changed

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

Lines changed: 63 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -101,7 +101,10 @@ typedef struct RI_ConstraintInfo
101101
{
102102
Oidconstraint_id;/* OID of pg_constraint entry (hash key) */
103103
boolvalid;/* successfully initialized? */
104-
uint32oidHashValue;/* hash value of pg_constraint OID */
104+
Oidconstraint_root_id;/* OID of topmost ancestor constraint;
105+
* same as constraint_id if not inherited */
106+
uint32oidHashValue;/* hash value of constraint_id */
107+
uint32rootHashValue;/* hash value of constraint_root_id */
105108
NameDataconname;/* name of the FK constraint */
106109
Oidpk_relid;/* referenced relation */
107110
Oidfk_relid;/* referencing relation */
@@ -207,6 +210,7 @@ static void ri_CheckTrigger(FunctionCallInfo fcinfo, const char *funcname,
207210
staticconstRI_ConstraintInfo*ri_FetchConstraintInfo(Trigger*trigger,
208211
Relationtrig_rel,boolrel_is_pk);
209212
staticconstRI_ConstraintInfo*ri_LoadConstraintInfo(OidconstraintOid);
213+
staticOidget_ri_constraint_root(OidconstrOid);
210214
staticSPIPlanPtrri_PlanCheck(constchar*querystr,intnargs,Oid*argtypes,
211215
RI_QueryKey*qkey,Relationfk_rel,Relationpk_rel);
212216
staticboolri_PerformCheck(constRI_ConstraintInfo*riinfo,
@@ -1892,7 +1896,7 @@ ri_GenerateQualCollation(StringInfo buf, Oid collation)
18921896
*Construct a hashtable key for a prepared SPI plan of an FK constraint.
18931897
*
18941898
*key: output argument, *key is filled in based on the other arguments
1895-
*riinfo: info from pg_constraint entry
1899+
*riinfo: infoderivedfrom pg_constraint entry
18961900
*constr_queryno: an internal number identifying the query type
18971901
*(see RI_PLAN_XXX constants at head of file)
18981902
* ----------
@@ -1902,10 +1906,27 @@ ri_BuildQueryKey(RI_QueryKey *key, const RI_ConstraintInfo *riinfo,
19021906
int32constr_queryno)
19031907
{
19041908
/*
1909+
* Inherited constraints with a common ancestor can share ri_query_cache
1910+
* entries for all query types except RI_PLAN_CHECK_LOOKUPPK_FROM_PK.
1911+
* Except in that case, the query processes the other table involved in
1912+
* the FK constraint (i.e., not the table on which the trigger has been
1913+
* fired), and so it will be the same for all members of the inheritance
1914+
* tree. So we may use the root constraint's OID in the hash key, rather
1915+
* than the constraint's own OID. This avoids creating duplicate SPI
1916+
* plans, saving lots of work and memory when there are many partitions
1917+
* with similar FK constraints.
1918+
*
1919+
* (Note that we must still have a separate RI_ConstraintInfo for each
1920+
* constraint, because partitions can have different column orders,
1921+
* resulting in different pk_attnums[] or fk_attnums[] array contents.)
1922+
*
19051923
* We assume struct RI_QueryKey contains no padding bytes, else we'd need
19061924
* to use memset to clear them.
19071925
*/
1908-
key->constr_id=riinfo->constraint_id;
1926+
if (constr_queryno!=RI_PLAN_CHECK_LOOKUPPK_FROM_PK)
1927+
key->constr_id=riinfo->constraint_root_id;
1928+
else
1929+
key->constr_id=riinfo->constraint_id;
19091930
key->constr_queryno=constr_queryno;
19101931
}
19111932

@@ -2051,8 +2072,15 @@ ri_LoadConstraintInfo(Oid constraintOid)
20512072

20522073
/* And extract data */
20532074
Assert(riinfo->constraint_id==constraintOid);
2075+
if (OidIsValid(conForm->conparentid))
2076+
riinfo->constraint_root_id=
2077+
get_ri_constraint_root(conForm->conparentid);
2078+
else
2079+
riinfo->constraint_root_id=constraintOid;
20542080
riinfo->oidHashValue=GetSysCacheHashValue1(CONSTROID,
20552081
ObjectIdGetDatum(constraintOid));
2082+
riinfo->rootHashValue=GetSysCacheHashValue1(CONSTROID,
2083+
ObjectIdGetDatum(riinfo->constraint_root_id));
20562084
memcpy(&riinfo->conname,&conForm->conname,sizeof(NameData));
20572085
riinfo->pk_relid=conForm->confrelid;
20582086
riinfo->fk_relid=conForm->conrelid;
@@ -2082,6 +2110,30 @@ ri_LoadConstraintInfo(Oid constraintOid)
20822110
returnriinfo;
20832111
}
20842112

2113+
/*
2114+
* get_ri_constraint_root
2115+
*Returns the OID of the constraint's root parent
2116+
*/
2117+
staticOid
2118+
get_ri_constraint_root(OidconstrOid)
2119+
{
2120+
for (;;)
2121+
{
2122+
HeapTupletuple;
2123+
OidconstrParentOid;
2124+
2125+
tuple=SearchSysCache1(CONSTROID,ObjectIdGetDatum(constrOid));
2126+
if (!HeapTupleIsValid(tuple))
2127+
elog(ERROR,"cache lookup failed for constraint %u",constrOid);
2128+
constrParentOid= ((Form_pg_constraint)GETSTRUCT(tuple))->conparentid;
2129+
ReleaseSysCache(tuple);
2130+
if (!OidIsValid(constrParentOid))
2131+
break;/* we reached the root constraint */
2132+
constrOid=constrParentOid;
2133+
}
2134+
returnconstrOid;
2135+
}
2136+
20852137
/*
20862138
* Callback for pg_constraint inval events
20872139
*
@@ -2117,7 +2169,14 @@ InvalidateConstraintCacheCallBack(Datum arg, int cacheid, uint32 hashvalue)
21172169
RI_ConstraintInfo*riinfo=dlist_container(RI_ConstraintInfo,
21182170
valid_link,iter.cur);
21192171

2120-
if (hashvalue==0||riinfo->oidHashValue==hashvalue)
2172+
/*
2173+
* We must invalidate not only entries directly matching the given
2174+
* hash value, but also child entries, in case the invalidation
2175+
* affects a root constraint.
2176+
*/
2177+
if (hashvalue==0||
2178+
riinfo->oidHashValue==hashvalue||
2179+
riinfo->rootHashValue==hashvalue)
21212180
{
21222181
riinfo->valid= false;
21232182
/* Remove invalidated entries from the list, too */

‎src/test/regress/expected/foreign_key.out

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2470,3 +2470,21 @@ DROP SCHEMA fkpart9 CASCADE;
24702470
NOTICE: drop cascades to 2 other objects
24712471
DETAIL: drop cascades to table fkpart9.pk
24722472
drop cascades to table fkpart9.fk
2473+
-- test that ri_Check_Pk_Match() scans the correct partition for a deferred
2474+
-- ON DELETE/UPDATE NO ACTION constraint
2475+
CREATE SCHEMA fkpart10
2476+
CREATE TABLE tbl1(f1 int PRIMARY KEY) PARTITION BY RANGE(f1)
2477+
CREATE TABLE tbl1_p1 PARTITION OF tbl1 FOR VALUES FROM (minvalue) TO (1)
2478+
CREATE TABLE tbl1_p2 PARTITION OF tbl1 FOR VALUES FROM (1) TO (maxvalue)
2479+
CREATE TABLE tbl2(f1 int REFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED);
2480+
INSERT INTO fkpart10.tbl1 VALUES (0), (1);
2481+
INSERT INTO fkpart10.tbl2 VALUES (0), (1);
2482+
BEGIN;
2483+
DELETE FROM fkpart10.tbl1 WHERE f1 = 0;
2484+
UPDATE fkpart10.tbl1 SET f1 = 2 WHERE f1 = 1;
2485+
INSERT INTO fkpart10.tbl1 VALUES (0), (1);
2486+
COMMIT;
2487+
DROP SCHEMA fkpart10 CASCADE;
2488+
NOTICE: drop cascades to 2 other objects
2489+
DETAIL: drop cascades to table fkpart10.tbl1
2490+
drop cascades to table fkpart10.tbl2

‎src/test/regress/sql/foreign_key.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1738,3 +1738,19 @@ DELETE FROM fkpart9.pk WHERE a=35;
17381738
SELECT*FROMfkpart9.pk;
17391739
SELECT*FROMfkpart9.fk;
17401740
DROPSCHEMA fkpart9 CASCADE;
1741+
1742+
-- test that ri_Check_Pk_Match() scans the correct partition for a deferred
1743+
-- ON DELETE/UPDATE NO ACTION constraint
1744+
CREATESCHEMAfkpart10
1745+
CREATETABLEtbl1(f1intPRIMARY KEY) PARTITION BY RANGE(f1)
1746+
CREATETABLEtbl1_p1 PARTITION OF tbl1 FORVALUESFROM (minvalue) TO (1)
1747+
CREATETABLEtbl1_p2 PARTITION OF tbl1 FORVALUESFROM (1) TO (maxvalue)
1748+
CREATETABLEtbl2(f1intREFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED);
1749+
INSERT INTOfkpart10.tbl1VALUES (0), (1);
1750+
INSERT INTOfkpart10.tbl2VALUES (0), (1);
1751+
BEGIN;
1752+
DELETEFROMfkpart10.tbl1WHERE f1=0;
1753+
UPDATEfkpart10.tbl1SET f1=2WHERE f1=1;
1754+
INSERT INTOfkpart10.tbl1VALUES (0), (1);
1755+
COMMIT;
1756+
DROPSCHEMA fkpart10 CASCADE;

0 commit comments

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