Re: DO INSTEAD in rule

> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)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