Re: DO INSTEAD in rule

> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO
> > INSTEAD in the rule and the default value for j is 0. Am I missing
> > something?
> 
> > CREATE rule t1_ins AS ON INSERT TO t1
> >     WHERE (EXISTS (SELECT 1 FROM t1
> >             WHERE i = new.i))
> >     DO INSTEAD UPDATE t1 SET j = j + 1
> >     WHERE i = new.i;
> 
> Hm.  The problem is that the rule query runs after the INSERT and so it
> sees the inserted row as something to update.  The logic is essentially
> 
>     if (not (EXISTS ...)) then do the INSERT;
>     if (EXISTS ...) then do the UPDATE;
> 
> and the second command sees the inserted row as existing, so it updates
> it.

Oh I see. I think I can live with it. However I guess documentations
should be clearner about this...

> Consider using a trigger instead of a rule to do this.  Or, accept
> that the UPDATE will happen unconditionally, and start J off one less
> than it should be.
> 
> Note that either solution will have race conditions if multiple
> processes try to insert the same row at the same time.  There are
> discussions in the archives about how to avoid that, but I'm not
> sure anyone found a really satisfactory answer that didn't involve
> an unpleasant amount of locking.

Now I remember the discussion. Probably I should not use rules like my
examples for real world applications.
--
Tatsuo Ishii