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
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan 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.928 rows=273285 loops=1)
-> Index Scan using page_view_visit_idx on page_view
(cost=0.00..159625.41 rows=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 null
visit_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 <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
