Re: delete and select with IN clause issues

On Thu, 2 Nov 2006, Jeff Frost wrote:

> 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?
>
>

Almost forgot:

server_version
----------------
8.1.4
(1 row)

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