> Tatsuo Ishii <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
