Обсуждение: BUG #7662: INSERT rule doesn't work as expected

The following bug has been logged on the website:

Bug reference:      7662
Logged by:          Melese Tesfaye
Email address:      mtesfaye@gmail.com
PostgreSQL version: 9.2.1
Operating system:   FreeBSD 9.0-RELEASE
Description:        =


ON INSERT RULE doesn't always work as expected.
The reasoning for this particular rule was to add the new value to an
existing value if it is determined that the key already exists.

Here is the table definition:

CREATE TABLE test_r(id INT PRIMARY KEY,val INT NOT NULL);

Here is the rule definition:

CREATE OR REPLACE RULE "dup_add_test_r_rule" AS =

  ON INSERT TO test_r
  WHERE =

    EXISTS(SELECT 1 FROM test_r a WHERE a.id=3DNEW.id) =

    DO INSTEAD =

       (UPDATE test_r a SET val=3Da.val+NEW.val WHERE a.id=3DNEW.id);

Empty table:
       =

SELECT * FROM test_r;

+----+-----+
| id | val |
+----+-----+
+----+-----+
(0 rows)

Time: 0.775 ms

Now, insert the following.

INSERT INTO test_r VALUES(1,10);
INSERT 0 1
Time: 2.038 ms

Query the table after insert (expected val to be 10 since the rule would
have
been igonred as id 1 didn't exist prior to inserting.

SELECT * FROM test_r;
+----+-----+
| id | val |
+----+-----+
|  1 |  20 |
+----+-----+
(1 row)

mtesfaye@gmail.com writes:
> ON INSERT RULE doesn't always work as expected.
> The reasoning for this particular rule was to add the new value to an
> existing value if it is determined that the key already exists.

> CREATE OR REPLACE RULE "dup_add_test_r_rule" AS
>   ON INSERT TO test_r
>   WHERE
>     EXISTS(SELECT 1 FROM test_r a WHERE a.id=NEW.id)
>     DO INSTEAD
>        (UPDATE test_r a SET val=a.val+NEW.val WHERE a.id=NEW.id);

Per the manual:

    For ON INSERT rules, the original query (if not suppressed by INSTEAD)
    is done before any actions added by rules. This allows the actions to
    see the inserted row(s).

So the behavior in your example is

(1) The EXISTS test fails, so the INSERT is allowed to execute.

(2) Now the EXISTS test passes, so the UPDATE is allowed to execute.

This might not be what you wished would happen, but it's not a bug;
it's the way rules are defined to work.  You might have better luck
with a BEFORE INSERT trigger.

            regards, tom lane