Обсуждение: RULE does not like the NOT EXISTS condition

Hi!

I found an "interest" in RULE's operation:

CREATE TABLE ruletest (id int4);
CREATE TABLE rulelog (id int4, ts timestamp NOT NULL DEFAULT current_timestamp);

CREATE RULE ruletest_rule AS ON INSERT TO ruletest DO INSERT INTO rulelog (id) VALUES (NEW.id);

INSERT INTO ruletest (id) VALUES (1);
INSERT INTO ruletest (id) SELECT 2 WHERE NOT EXISTS (SELECT 1 FROM ruletest WHERE id=2);
INSERT INTO ruletest (id) SELECT 3;

SELECT * FROM ruletest;
SELECT * FROM rulelog;

------
Output:

CREATE TABLE
CREATE TABLE
CREATE RULE
INSERT 0 1
INSERT 0 1
INSERT 0 1

  id
----
   1
   2
   3
(3 rows)

  id |             ts
----+----------------------------
   1 | 2018-09-08 17:18:41.661292
   3 | 2018-09-08 17:18:41.683459
(2 rows)


That is, in the NOT EXISTS condition the rule is not executed.


Version:
PostgreSQL 9.5.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
PostgreSQL 8.3.23 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.7.4


Is this bug or feature? :)



                                 -Sygma

>>>>> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>>>>> "Szima" == Szima Gábor <sygma@tesla.hu> writes:

 Szima> Hi!
 Szima> I found an "interest" in RULE's operation:

 Andrew> All non-trivial* rules are wrong,

I should of course add: the solution is to use triggers instead, which
are much easier to understand and have very predictable behavior.

--
Andrew (irc:RhodiumToad)