Thread: DO INSTEAD in rule

Hi,

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?

DROP TABLE t1 CASCADE;
DROP TABLE
CREATE TABLE t1 (i INTEGER,j INTEGER DEFAULT 0
);
CREATE TABLE
CREATE rule t1_ins AS ON INSERT TO t1WHERE (EXISTS (SELECT 1 FROM t1        WHERE i = new.i))DO INSTEAD UPDATE t1 SET j
=j + 1WHERE i = new.i;
 
CREATE RULE
INSERT INTO t1 VALUES (1);
INSERT 1690668 1
SELECT * FROM t1;i | j 
---+---1 | 1
(1 row)
--
Tatsuo Ishii

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.

Without an if-then-else kind of control structure for the executor,
I'm not sure we can do better.  (Even with one, I'm not sure how to
handle cases where the INSERT inserts multiple rows.)

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.
        regards, tom lane

> 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