delete and select with IN clause issues - Mailing list pgsql-sql
From | Jeff Frost |
---|---|
Subject | delete and select with IN clause issues |
Date | |
Msg-id | Pine.LNX.4.64.0611021541050.29554@discord.home.frostconsultingllc.com Whole thread Raw |
Responses |
Re: delete and select with IN clause issues
Re: delete and select with IN clause issues |
List | pgsql-sql |
I'm having problem with a cleanup script that runs nightly. The script calls the following query: 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". Ok, that seems fine, but when I do a select instead of delete, I do not find the referenced id in my list: select id from visit where id not in (select distinct visit_id from page_view) and id = 38635629 ; id ---- (0 rows) Also, if I don't specify the id = bit at the end, I still don't find it in the output when I search through with less, so why is it trying to delete that row? Rewriting the query like so, yields the same problem: delete from visit where NOT EXISTS ( select * from page_view WHERE visit.id = page_view.visit_id); ERROR: update or delete on "visit" violates foreign key constraint "fk34afd255fbacabec" on "page_view" DETAIL: Key (id)=(38638264) is still referenced from table "page_view". The plan looks like this: explain analyze delete from visit where id not in (select distinct visit_id from page_view); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- SeqScan on visit (cost=165027.49..189106.89 rows=211976 width=6) (actual time=4789.595..5330.367 rows=150677 loops=1) Filter: (NOT (hashed subplan)) SubPlan -> Unique (cost=0.00..165017.77 rows=3889 width=8) (actual time=2.717..4388.928rows=273285 loops=1) -> Index Scan using page_view_visit_idx on page_view (cost=0.00..159625.41rows=2156946 width=8) (actual time=2.713..2914.944 rows=2249576 loops=1) Trigger for constraint fk34afd255fbacabec:time=7174.540 calls=150677 Total runtime: 32772.345 ms (7 rows) \d page_view Table "public.page_view" Column | Type | Modifiers ----------------+-----------------------------+--------------------- id | bigint | not nullvisit_id | bigint | not null uri | character varying(255) | params | text | stamp | timestamp without time zone | cindex | integer | not null default -1 tindex | integer | not null default -1 method | character varying(7) | not null source_address | character varying(16) | server_name | character varying(255) | Indexes: "page_view_pkey" PRIMARY KEY, btree (id) "page_view_stamp_idx" btree (stamp) "page_view_uri_idx" btree(uri) "page_view_visit_idx" btree (visit_id) Foreign-key constraints: "fk34afd255fbacabec" FOREIGN KEY (visit_id) REFERENCES visit(id) What kind of silliness am I forgetting? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954