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

Jaime Casanova wrote:
> On 5/24/05, Sebastian Böck <sebastianboeck@freenet.de> wrote:
>
>>Martijn van Oosterhout wrote:
>>
>>>I'm sure I'm not the only one, but, what are you talking about? RULEs
>>>are not really obvious so it would help if you could post an example of
>>>what you mean...

I attach some sample SQL (commented) to demonstrate the described
scenario:

>>Hi, I'm not really talking about rules.
>>
>>I'm talking about updates on *real* tables, and how to avoid
>>unnecessary updates on these tables if the row doesn't change.
>>
>>The situation looks like this:
>>
>>I have a view which is a join of a lot of tables.
>>
>>I have lot of conditional ON UPDATE rules to that view that split one
>>update to the view into updates on the underlying table. The condition
>>of each rule is constructed in a way that the underlying table only
>>gets an update if the corresponding values change.
>>
>>If I collapse all these rules into one conditional rule and pass all
>>the updates to the underlying tables, I get a lot of unnecessary
>>updates to these real tables, if the values don't change.
>>
>>Thats what I want to avoid.
>>
>>Sorry for not beeing that clear.
>>
>>Sebastian
>>
>>
>
> And how are you preventing the rule execute the update if the field
> has no change? That is way Martijn told you about showing the rule.

Sorry I don't understand what you mean.
What's wrong with:

CREATE OR REPLACE RULE upd AS ON UPDATE TO view
WHERE NEW.field = OLD.field DO INSTEAD NOTHING;

> AFAIK, if you execute an update on a view that has a ON UPDATE rule
> all the SQL ACTIONS specified for the rule will be perfomed INSTEAD OF
> the original update

Ok thanks for the tip, but I alredy knew this ;)

But my original question remeins:

Why does Postgres perform updates to tables, even if the row doesn't
change at all?

Thanks

Sebastian