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