Attached is a small patch and a script to reproduce the issue.
The problem is a cache introduced in commit 45ba4247 that improves foreign key lookups during bulk updates when the FK value does not change. When restoring a schema dump from a database with many (say 100,000) foreign keys, this cache is growing very big and every ALTER TABLE command is causing a InvalidateConstraintCacheCallBack(), which does a sequential hash table scan.
The patch uses a heuristic method of detecting when the hash table should be destroyed and recreated. InvalidateConstraintCacheCallBack() adds the current size of the hash table to a counter. When that sum reaches 1,000,000, the hash table is flushed. This improves the schema restore of a database with 100,000 foreign keys by factor 3.
According to my tests the patch does not interfere with the bulk updates, the original feature was supposed to improve.
Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 61edde9..d7023ce 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -183,6 +183,7 @@ typedef struct RI_CompareHashEntry * ---------- */ static HTAB *ri_constraint_cache = NULL; +static long ri_constraint_cache_seq_count = 0; static HTAB *ri_query_cache = NULL; static HTAB *ri_compare_cache = NULL; @@ -2945,6 +2946,27 @@ InvalidateConstraintCacheCallBack(Datum arg, int cacheid, uint32 hashvalue) Assert(ri_constraint_cache != NULL); + /* + * Prevent an O(N^2) problem when creating large amounts of foreign + * key constraints with ALTER TABLE, like it happens at the end of + * a pg_dump with hundred-thousands of tables having references. + */ + ri_constraint_cache_seq_count += hash_get_num_entries(ri_constraint_cache); + if (ri_constraint_cache_seq_count > 1000000) + { + HASHCTL ctl; + + hash_destroy(ri_constraint_cache); + memset(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(Oid); + ctl.entrysize = sizeof(RI_ConstraintInfo); + ri_constraint_cache = hash_create("RI constraint cache", + RI_INIT_CONSTRAINTHASHSIZE, + &ctl, HASH_ELEM | HASH_BLOBS); + ri_constraint_cache_seq_count = 0; + return; + } + hash_seq_init(&status, ri_constraint_cache); while ((hentry = (RI_ConstraintInfo *) hash_seq_search(&status)) != NULL) {
t1.sh
Description: application/shellscript
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers