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

Commit5ddc728

Browse files
committed
Fix an O(N^2) problem in foreign key references.
Commit45ba424 improved foreign key lookups during bulk updateswhen the FK value does not change. When restoring a schema dumpfrom a database with many (say 100,000) foreign keys, this cachewould grow very big and every ALTER TABLE command was causing anInvalidateConstraintCacheCallBack(), which uses a sequential hashtable scan. This could cause a severe performance regression inrestoring a schema dump (including during pg_upgrade).The patch uses a heuristic method of detecting when the hash tableshould be destroyed and recreated.InvalidateConstraintCacheCallBack() adds the current size of thehash table to a counter. When that sum reaches 1,000,000, the hashtable is flushed. This fixes the regression without noticeableharm to the bulk update use case.Jan WieckBackpatch to 9.3 where the performance regression was introduced.
1 parentaa65de0 commit5ddc728

File tree

1 file changed

+35
-3
lines changed

1 file changed

+35
-3
lines changed

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

Lines changed: 35 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -183,6 +183,7 @@ typedef struct RI_CompareHashEntry
183183
* ----------
184184
*/
185185
staticHTAB*ri_constraint_cache=NULL;
186+
staticlongri_constraint_cache_seq_count=0;
186187
staticHTAB*ri_query_cache=NULL;
187188
staticHTAB*ri_compare_cache=NULL;
188189

@@ -215,6 +216,7 @@ static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
215216
staticboolri_AttributesEqual(Oideq_opr,Oidtypeid,
216217
Datumoldvalue,Datumnewvalue);
217218

219+
staticvoidri_InitConstraintCache(void);
218220
staticvoidri_InitHashTables(void);
219221
staticvoidInvalidateConstraintCacheCallBack(Datumarg,intcacheid,uint32hashvalue);
220222
staticSPIPlanPtrri_FetchPreparedPlan(RI_QueryKey*key);
@@ -2945,6 +2947,20 @@ InvalidateConstraintCacheCallBack(Datum arg, int cacheid, uint32 hashvalue)
29452947

29462948
Assert(ri_constraint_cache!=NULL);
29472949

2950+
/*
2951+
* Prevent an O(N^2) problem when creating large amounts of foreign
2952+
* key constraints with ALTER TABLE, like it happens at the end of
2953+
* a pg_dump with hundred-thousands of tables having references.
2954+
*/
2955+
ri_constraint_cache_seq_count+=hash_get_num_entries(ri_constraint_cache);
2956+
if (ri_constraint_cache_seq_count>1000000)
2957+
{
2958+
hash_destroy(ri_constraint_cache);
2959+
ri_InitConstraintCache();
2960+
ri_constraint_cache_seq_count=0;
2961+
return;
2962+
}
2963+
29482964
hash_seq_init(&status,ri_constraint_cache);
29492965
while ((hentry= (RI_ConstraintInfo*)hash_seq_search(&status))!=NULL)
29502966
{
@@ -3364,13 +3380,15 @@ ri_NullCheck(HeapTuple tup,
33643380

33653381

33663382
/* ----------
3367-
*ri_InitHashTables -
3383+
*ri_InitConstraintCache
33683384
*
3369-
*Initialize our internal hash tables.
3385+
* Initialize ri_constraint_cache when new or being rebuilt.
3386+
*
3387+
* This needs to be done from two places, so split it out to prevent drift.
33703388
* ----------
33713389
*/
33723390
staticvoid
3373-
ri_InitHashTables(void)
3391+
ri_InitConstraintCache(void)
33743392
{
33753393
HASHCTLctl;
33763394

@@ -3380,6 +3398,20 @@ ri_InitHashTables(void)
33803398
ri_constraint_cache=hash_create("RI constraint cache",
33813399
RI_INIT_CONSTRAINTHASHSIZE,
33823400
&ctl,HASH_ELEM |HASH_BLOBS);
3401+
}
3402+
3403+
/* ----------
3404+
* ri_InitHashTables -
3405+
*
3406+
*Initialize our internal hash tables.
3407+
* ----------
3408+
*/
3409+
staticvoid
3410+
ri_InitHashTables(void)
3411+
{
3412+
HASHCTLctl;
3413+
3414+
ri_InitConstraintCache();
33833415

33843416
/* Arrange to flush cache on pg_constraint changes */
33853417
CacheRegisterSyscacheCallback(CONSTROID,

0 commit comments

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