Re: delete and select with IN clause issues

On Tue, 7 Nov 2006, Jeff Frost wrote:

> Well, it's been working wonderfully since the REINDEX, so I don't know what 
> to say.  Any idea if having a too small max_fsm_pages could hose an index, 
> because I know that happened not too long before we started seeing this 
> problem.  The fsm settings were increased prior to the problem occurring, but 
> it's possible the index was already damaged?

Well, once again I speak too soon:

ERROR:  update or delete on "visit" violates foreign key constraint 
"fk34afd255fbacabec" on "page_view"
DETAIL:  Key (id)=(40276078) is still referenced from table "page_view".

and again, it's in both tables, so it shouldn't be trying to remove it from 
visit:

engage_tracking=# select id from visit where id = 40276078;    id
---------- 40276078
(1 row)

engage_tracking=# select visit_id from page_view where visit_id = 40276078; visit_id
---------- 40276078

Reminder: the delete query is:

delete from visit where id not in (select distinct visit_id from page_view);

and if I do this:

select id from visit where id not in (select distinct visit_id from page_view);

I get a result set without that id in it.

And now after looking at the data, I realize what's going on.  I believe I 
have a race condition in which the visit entry is created before the select 
begins, but the page_view entry has not yet been created.  I then try to 
delete the visit entry after the page_view row is created and then my fkey 
stops me.

I added a WHERE clause in my script that looks like this:

DELETE FROM visit WHERE stamp < now() - INTERVAL '30 days' AND id NOT IN (SELECT DISTINCT visit_id FROM page_view);

I think we can put this one to bed and sorry for wasting everyone's cycles.

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