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."

Sat, 27 May 2000 15:58:30 -0700
Alfred Perlstein <bright@wintelcom.net>
but i _really can't_ insert a duplicate key!
* Alfred Perlstein <bright@wintelcom.net> [000527 16:03] wrote:
> 
> 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?

Ok, this was my fault, it seems the rule system takes a snapshot of the
table at the start of a insert from select op and my rule wasn't catching
the rows that were inserted during the insert.  (basically confirmed my
suspicions)

I found the duplicate row in my original table and once it was
removed the the inserts seem to work perfectly.

It would be nice to have an exception handler that could be executed
when an insert fails because of various reason, something like:

create rule update_instread_of_insert as on exception to mytable 
  where exception = violates_unique
  do update ....

This would reduce the amount of searching because the insert rule only
happens when there is an exception instead of forcing an extra lookup
before each insert.

Anyhow, I can always wish. :)

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

Sun, 28 May 2000 21:49:29 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: but i _really can't_ insert a duplicate key!