Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 154EB9FA666 for ; Thu, 2 Nov 2006 23:50:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 91804-01 for ; Fri, 3 Nov 2006 03:50:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 195C19FA418 for ; Thu, 2 Nov 2006 23:50:11 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kA33o0AQ024513; Thu, 2 Nov 2006 22:50:02 -0500 (EST) To: Jeff Frost cc: pgsql-sql@postgresql.org Subject: Re: delete and select with IN clause issues In-reply-to: References: Comments: In-reply-to Jeff Frost message dated "Thu, 02 Nov 2006 15:58:29 -0800" Date: Thu, 02 Nov 2006 22:50:00 -0500 Message-ID: <24512.1162525800@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/28 X-Sequence-Number: 26680 Jeff Frost writes: > delete from visit where id not in (select distinct visit_id from page_view); > This yields the following error: > ERROR: update or delete on "visit" violates foreign key constraint > "fk34afd255fbacabec" on "page_view" > DETAIL: Key (id)=(38635629) is still referenced from table "page_view". This seems pretty darn weird. I am wondering about corrupt indexes --- can you find the indicated key in either table if you set enable_indexscan and enable_bitmapscan to 0? Also, this is a long shot, but does visit by any chance have a cascading deletion self-reference? regards, tom lane