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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954