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 <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
