Обсуждение: 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