Re: Update on tables when the row doesn't change - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Update on tables when the row doesn't change
Date
Msg-id 20050525085158.GA15818@svana.org
Whole thread Raw
In response to Update on tables when the row doesn't change  (Sebastian Böck <sebastianboeck@freenet.de>)
Responses Re: Update on tables when the row doesn't change
List pgsql-general
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.

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.

Have a nice day,

On Wed, May 25, 2005 at 10:40:38AM +0200, Sebastian Böck wrote:
> Thank you for the explanation. That's what I wanted to do first, but
> then I discovered that the view gets not only evaluated for every rule
> on it, but also gets evaluated as often as there are statements in one
> rule.
>
> Example:
>
> 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;
>   );
>
> Why gets the view evaluated 4 times?
>
> Thanks
>
> Sebastian

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment

pgsql-general by date:

Previous
From: Sebastian Böck
Date:
Subject: Re: Update on tables when the row doesn't change
Next
From: Richard Huxton
Date:
Subject: Re: postmaster fails to start