Re: rule weirdness

On Aug 20, 2007, at 10:24, Jeff Davis wrote:

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

Oh, for sure.

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

Yeah, I'd noticed that. I figured that your point was that the `raise
error` bit would not even execute.

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

<snip />

> 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.

Yes, so, just make sure that the column you use in the WHERE clause
of latter statements is not changed by earlier statements. Not that
I'd ever change the PK, but you never know…

> 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.

Yes, that makes sense. They should work if you run them in reverse
order though, correct?

create or replace rule rr_upd_rule as on update to rr do instead (
UPDATE rr2 SET i=NEW.i, d2=NEW.d2 WHERE i=OLD.i
UPDATE rr1 SET i=NEW.i, d1=NEW.d1 WHERE i=OLD.i;
);

Best,

David