X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A4AA053F91 for ; Thu, 14 Apr 2005 12:06:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99707-10 for ; Thu, 14 Apr 2005 15:06:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3121353F9A for ; Thu, 14 Apr 2005 12:06:06 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3EF5jGr000743; Thu, 14 Apr 2005 11:05:47 -0400 (EDT) To: Stephan Szabo Cc: Richard van den Berg , Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: Foreign key slows down copy/insert In-reply-to: <20050414074154.M38465@megazone.bigpanda.com> References: <425E6045.4060201@trust-factory.com> <425E792B.4020500@familyhealth.com.au> <425E7D96.6020201@trust-factory.com> <20050414074154.M38465@megazone.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 14 Apr 2005 07:45:33 -0700" Date: Thu, 14 Apr 2005 11:05:45 -0400 Message-ID: <742.1113491145@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/285 X-Sequence-Number: 11682 Stephan Szabo writes: > ... At some point, if we can work out how to do all the semantics > properly, it'd probably be possible to replace the insert type check with > a per-statement check which would be somewhere in between. That requires > access to the affected rows inside the trigger which I don't believe is > available currently. Not necessarily. It occurs to me that maybe what we need is "lossy storage" of the trigger events. If we could detect that the queue of pending checks for a particular FK is getting large, we could discard the whole queue and replace it with one entry that says "run the wholesale check again when we are ready to fire triggers". I'm not sure how to detect this efficiently, though --- the trigger manager doesn't presently know anything about FKs being different from any other kind of trigger. regards, tom lane