rule weirdness

At last night's meeting we started discussing rules versus triggers. As
promised, here's the weird rule example I wanted to show:

drop table if exists rr1 cascade;
drop table if exists rr2 cascade;
create table rr1( i int primary key, d1 int);
create table rr2( i int primary key, d2 int);
create or replace view rr as select i, d1, d2 from rr1 natural join rr2;
create or replace rule rr_ins_rule as on insert to rr do instead (
insert into rr1(i, d1) values(NEW.i, NEW.d1);
insert into rr2(i, d2) values(NEW.i, NEW.d2)
);
create or replace rule rr_upd_rule as on update to rr do instead (
UPDATE rr1 SET i=NEW.i, d1=NEW.d1 WHERE i=OLD.i;
SELECT error('foo')
);
insert into rr(i,d1,d2) values(11,21,31);
insert into rr(i,d1,d2) values(12,22,32);

=> select * from rr;
i | d1 | d2
----+----+----
11 | 21 | 31
12 | 22 | 32
(2 rows)

=> update rr set d1=24 where i=11;
UPDATE 1
=> select * from rr;
i | d1 | d2
----+----+----
12 | 22 | 32
11 | 24 | 31
(2 rows)

=> update rr set i=41 where i=11;
UPDATE 0
=> select * from rr;
i | d1 | d2
----+----+----
12 | 22 | 32
(1 row)

-- oops! where did the other row go?

=> select * from rr1;
i | d1
----+----
12 | 22
41 | 24
(2 rows)

-- rr1 was updated properly. All records
-- with i=11 were updated so that i=41

=> select * from rr2;
i | d2
----+----
12 | 32
11 | 31
(2 rows)

-- rr2 was not updated at all!

The reason that rr2 was not updated was that the rule executes these two
statements:

UPDATE rr1 SET i=NEW.i, d1=NEW.d1 WHERE i=OLD.i;
UPDATE rr2 SET i=NEW.i, d2=NEW.d2 WHERE i=OLD.i;

However, the second statement won't do anything, because the tuples
you're updating no longer match the predicate in the outer query "update
rr set i=41 where i=11". So that means that it actually matters which
column you specify in the where clause of the outer update.

In fact, the second statement can be anything, it doesn't matter at all.
It will not do anything. A "SELECT my_func()" won't execute the function
(but will give an empty result set).

Jeff Davis