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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
