Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id B009F2E0030 for ; Thu, 3 Apr 2008 14:31:41 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 60659-03 for ; Thu, 3 Apr 2008 14:31:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by developer.postgresql.org (Postfix) with ESMTP id D119C2E008F for ; Thu, 3 Apr 2008 14:31:31 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id C0CBD363EB; Thu, 3 Apr 2008 10:31:19 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id BF4B3362F6; Thu, 3 Apr 2008 10:31:19 -0700 (PDT) Date: Thu, 3 Apr 2008 10:31:19 -0700 (PDT) From: Stephan Szabo To: chester c young Cc: sql pgsql Subject: Re: apparent RI bug In-Reply-To: <598841.46263.qm@web54306.mail.re2.yahoo.com> Message-ID: <20080403102340.B14656@megazone.bigpanda.com> References: <598841.46263.qm@web54306.mail.re2.yahoo.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/22 X-Sequence-Number: 30561 On Wed, 2 Apr 2008, chester c young wrote: > it appears I have a broken RI in my db. > > call_individual.clh_id references call_household.clh_id > > \d call_individual > ... > Foreign-key constraints: > "call_individual_clh_id_fkey" FOREIGN KEY (clh_id) REFERENCES > call_household(clh_id) ON DELETE CASCADE > > however: > development=# select clh_id from call_individual cli where not exists( > select 1 from call_household clh where clh.clh_id=cli.clh_id ); > clh_id > -------- > 14691 > > should not matter, but call_individual has a pre-delete trigger that > simply raises an exception to prevent deletions: > raise exception 'calls may not be deleted'; Yeah, that looks pretty broken. Can you reproduce this from a clean start repeatedly or is this a one off? Do you ever turn off triggers, perhaps by modifying the pg_class row's reltriggers (I'd guess the answer is no, but it'd be good to make sure)?