Re: RULE does not like the NOT EXISTS condition

>>>>> "Szima" == Szima Gábor <sygma(at)tesla(dot)hu> writes:

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

All non-trivial* rules are wrong, and this is a good illustration of
why.

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

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

So when you try and run the above INSERT, this is what gets run (costs
and timings omitted for clarity):

Insert on ruletest
-> Result (rows=1 loops=1)
One-Time Filter: (NOT $0)
InitPlan 1 (returns $0)
-> Seq Scan on ruletest ruletest_1 (rows=0 loops=1)
Filter: (id = 2)
Rows Removed by Filter: 1

Insert on rulelog
-> Subquery Scan on "*SELECT*" (rows=0 loops=1)
-> Result (rows=0 loops=1)
One-Time Filter: (NOT $0)
InitPlan 1 (returns $0)
-> Seq Scan on ruletest (rows=1 loops=1)
Filter: (id = 2)
Rows Removed by Filter: 1

Between the two plan trees the command counter gets incremented, which
means that the changes made by the first are visible to the second.

So the insert on ruletest happens because the not exists condition is
initially true. But the insert on rulelog doesn't happen because the not
exists condition is now false, so the select returns no row to insert.

This kind of multiple-evaluation hazard is one of the biggest reasons to
avoid rules. Even experts can rarely predict the effect of an arbitrary
rule on an arbitrary DML statement (without running EXPLAIN ANALYZE to
see what actually happens, as above).

* - a rule is non-trivial if it is DO ALSO (as in this case), or it
contains multiple queries in the action, or it contains a conditional
action. See http://blog.rhodiumtoad.org.uk/2010/06/21/the-rule-challenge/

--
Andrew (irc:RhodiumToad)