On Thu, 2 Nov 2006, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
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".
This seems pretty darn weird. I am wondering about corrupt indexes ---
can you find the indicated key in either table if you set
enable_indexscan and enable_bitmapscan to 0?
test_tracking=# begin;
BEGIN
test_tracking=# set enable_seqscan TO false;
SET
test_tracking=# set enable_bitmapscan to false;
SET
test_tracking=# delete from visit where id not in (select distinct visit_id
from
test_tracking(# page_view);
DELETE 150660
test_tracking=# ROLLBACK ;
So, it seems everything worked fine with those two set to false.
with seqscan enabled, it fails:
test_tracking=# begin;
BEGIN
test_tracking=# set enable_bitmapscan to false;
SET
test_tracking=# show enable_seqscan ;
enable_seqscan
----------------
on
(1 row)
test_tracking=# delete from visit where id not in (select distinct visit_id
from page_view );
ERROR: update or delete on "visit" violates foreign key constraint
"fk34afd255fbacabec" on "page_view"
DETAIL: Key (id)=(38710245) is still referenced from table "page_view".
Looks like with just enable_seqscan disabled it works:
test_tracking=# begin;
BEGIN
test_tracking=# set enable_seqscan to false;
SET
test_tracking=# show enable_bitmapscan ;
enable_bitmapscan
-------------------
on
(1 row)
test_tracking=# delete from visit where id not in (select distinct visit_id
from page_view );
DELETE 150661
test_tracking=# ROLLBACK ;
ROLLBACK
However, this doesn't yield anything:
select id from visit where id not in (select distinct visit_id from page_view)
and id = 38710245 ;
id
----
(0 rows)
with them set to true or false.
Also, this is a long shot, but does visit by any chance have a cascading
deletion self-reference?
Nope, I guess I didn't \d visit..thought I did...it's below. I'm going to
guess that there is index corruption and a reindex page_view will fix it. Do
you want me to gather any information in case this is a reproducible bug
before I issue the reindex?
Table "public.visit"
Column | Type | Modifiers
-------------+-----------------------------+------------------------
id | bigint | not null
visitor_id | bigint | not null
campaign_id | bigint |
session_id | character varying(32) | not null
uri | character varying(2000) |
referer | character varying(2000) |
user_agent | character varying(2000) |
remote_host | bigint | not null
outcome | character(1) | not null
stamp | timestamp without time zone |
email_key | character varying(16) |
bot | boolean | not null default false
status | character(1) | not null
Indexes:
"visit_pkey" PRIMARY KEY, btree (id)
"visit_un" UNIQUE, btree (session_id)
"visit_bot_idx" btree (bot)
"visit_remote_host_ix" btree (remote_host)
"visit_stamp_ix" btree (stamp)
"visit_visitor_ix" btree (visitor_id)
Foreign-key constraints:
"fk6b04d4bbfc2fa3d" FOREIGN KEY (remote_host) REFERENCES remote_host(id)
"fk6b04d4be5dc468" FOREIGN KEY (campaign_id) REFERENCES campaign(id)
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
