Re: rule weirdness

On Sat, 2007-08-18 at 13:25 -0700, David E. Wheeler wrote:
> On Aug 17, 2007, at 16:03, Jeff Davis wrote:
>
> > 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.
>
> To me, if I properly understand the example, the moral of the story
> is not to change primary key column values (unless, perhaps, your FK
> constraints have ON UPDATE CASCADE).
>

The moral of the story is to be very careful when using two statements
in a rule ;)

As an aside, in my example, I just noticed I actually posted the wrong
version of the rule rr_upd_rule, it should read:

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;
UPDATE rr2 SET i=NEW.i, d2=NEW.d2 WHERE i=OLD.i
);

Which makes more sense in my example, but it doesn't matter much
considering the second statement doesn't do much (although is still
executed).

Back to your point: Even with ON UPDATE CASCADE, you can still problems:

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 unique, d2 int,
foreign key (i) references rr1(i) on update cascade);
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;
UPDATE rr2 SET i=NEW.i, d2=NEW.d2 WHERE i=OLD.i
);
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 i=41, d2=43 where i=11;
UPDATE 0
=> select * from rr;
i | d1 | d2
----+----+----
12 | 22 | 32
41 | 21 | 31
(2 rows)

So, the key was updated, but "d2" was not. This also shows the
additional problem that you can't control the PQcmdTuples() (the "0" in
the "UPDATE 0").

The problem is that second statement doesn't effectively do anything.

I think that what causes that is that, when the tables are modified such
that the outer query's predicate is no longer satisfied by the tuple in
question, the range table is empty.

Regards,
Jeff Davis