Re: Update on tables when the row doesn't change

Martijn van Oosterhout wrote:
> Well, I havn't run the rule so I don't know exactly whether it is
> relevent, but simply put, RULEs are like *macro substitution*. In
> macros, if you specify an expression (like a view) as an argument, it
> is placed as a whole each place the argument is used.

Yes I understood it the same way.
That makes absolutly sense in situations where you have different
rules on the same target (view or table).

> UPDATEs for different tables cannot be merged into a single query so
> you get four queries and it's not clear how you could avoid any work.
>
> I guess each subquery may be optimised individually, though I don't
> know if it really helps. Perhaps you could show us the resulting query
> plans and how you think they could be improved.

There are a lot of geometric operations involved (all done via
postgis) that make the view slow. I don't want to change the view
itself now, only want to get rid of all the unnecessary evaluations of
the view.

So I tried to collapse the rules into on rule as shown in the example
below:

> On Wed, May 25, 2005 at 10:40:38AM +0200, Sebastian Böck wrote:
>
>>CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
>>  DO INSTEAD (
>>    UPDATE test SET test = NEW.test WHERE id = OLD.id;
>>    UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
>>    UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
>>    UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
>>  );

I hoped that the view gets only evaluated once, because it is only one
rule, but thats not true :(

Thanks for all so far, I'll come back when tuning the obove mentioned
queries.

Sebastian