Re: delete and select with IN clause issues

On Thu, 2 Nov 2006, Tom Lane wrote:

> Jeff Frost <jeff(at)frostconsultingllc(dot)com> 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?

test_tracking=# begin;
BEGIN
test_tracking=# set enable_seqscan TO false;
SET
test_tracking=# set enable_bitmapscan to false;
SET
test_tracking=# delete from visit where id not in (select distinct visit_id
from
test_tracking(# page_view);
DELETE 150660
test_tracking=# ROLLBACK ;

So, it seems everything worked fine with those two set to false.

with seqscan enabled, it fails:

test_tracking=# begin;
BEGIN
test_tracking=# set enable_bitmapscan to false;
SET
test_tracking=# show enable_seqscan ;
enable_seqscan
----------------
on
(1 row)

test_tracking=# delete from visit where id not in (select distinct visit_id
from page_view );
ERROR: update or delete on "visit" violates foreign key constraint
"fk34afd255fbacabec" on "page_view"
DETAIL: Key (id)=(38710245) is still referenced from table "page_view".

Looks like with just enable_seqscan disabled it works:

test_tracking=# begin;
BEGIN
test_tracking=# set enable_seqscan to false;
SET
test_tracking=# show enable_bitmapscan ;
enable_bitmapscan
-------------------
on
(1 row)

test_tracking=# delete from visit where id not in (select distinct visit_id
from page_view );
DELETE 150661
test_tracking=# ROLLBACK ;
ROLLBACK

However, this doesn't yield anything:

select id from visit where id not in (select distinct visit_id from page_view) and id = 38710245 ;
id
----
(0 rows)

with them set to true or false.

>
> Also, this is a long shot, but does visit by any chance have a cascading
> deletion self-reference?

Nope, I guess I didn't \d visit..thought I did...it's below. I'm going to
guess that there is index corruption and a reindex page_view will fix it. Do
you want me to gather any information in case this is a reproducible bug
before I issue the reindex?

Table "public.visit"
Column | Type | Modifiers
-------------+-----------------------------+------------------------
id | bigint | not null
visitor_id | bigint | not null
campaign_id | bigint |
session_id | character varying(32) | not null
uri | character varying(2000) |
referer | character varying(2000) |
user_agent | character varying(2000) |
remote_host | bigint | not null
outcome | character(1) | not null
stamp | timestamp without time zone |
email_key | character varying(16) |
bot | boolean | not null default false
status | character(1) | not null
Indexes:
"visit_pkey" PRIMARY KEY, btree (id)
"visit_un" UNIQUE, btree (session_id)
"visit_bot_idx" btree (bot)
"visit_remote_host_ix" btree (remote_host)
"visit_stamp_ix" btree (stamp)
"visit_visitor_ix" btree (visitor_id)
Foreign-key constraints:
"fk6b04d4bbfc2fa3d" FOREIGN KEY (remote_host) REFERENCES remote_host(id)
"fk6b04d4be5dc468" FOREIGN KEY (campaign_id) REFERENCES campaign(id)

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954