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
