Re: delete and select with IN clause issues - Mailing list pgsql-sql
From | Jeff Frost |
---|---|
Subject | Re: delete and select with IN clause issues |
Date | |
Msg-id | Pine.LNX.4.64.0611022021150.6160@discord.home.frostconsultingllc.com Whole thread Raw |
In response to | Re: delete and select with IN clause issues (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: delete and select with IN clause issues
|
List | pgsql-sql |
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