but i _really can't_ insert a duplicate key!

I have a table:

CREATE TABLE "link_tst" (
"c_id" int4,
"s_date" timestamp,
"r_id" int4,
"r_tally" int8
);

create unique INDEX "link_tst_idx" on "link_tst"
USING btree (r_id, c_id, s_date);

create rule link_tst_rule
as on insert to link_tst
where exists (
select
c_id
from
link_tst
where
r_id = NEW.r_id
AND c_id = NEW.c_id
AND s_date = NEW.s_date
) do instead
update
link_tst
set
r_hits = r_hits + NEW.r_hits
where
r_id = NEW.r_id
AND c_id = NEW.c_id
AND s_date = NEW.s_date
;

now when i select from another table with identical fields
but not the UNIQUE qualifier on it's index (there may be duplicates)
I get this:

select now(); insert into link_tst select * from r_link; select now();
now
------------------------
2000-05-27 15:08:23-07
(1 row)

ERROR: Cannot insert a duplicate key into unique index link_tst_idx
now
------------------------
2000-05-27 15:10:14-07
(1 row)

How is that possible? My only guess is that the rule is only being applied
to the table _before_ the query, and if there actually are duplicate rows
to be inserted the rule isn't catching them because the exists clause is
only running on the snapshot of the table before the insert starts.

is there a workaround or is this a possible bug?

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."