Re: delete and select with IN clause issues

On Thu, 2 Nov 2006, Tom Lane wrote:

> Jeff Frost <jeff@frostconsultingllc.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
nullvisitor_id  | bigint                      | not null campaign_id | bigint                      | session_id  |
charactervarying(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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954