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

From: Sebastian Böck <sebastianboeck(at)freenet(dot)de>
To: Jaime Casanova <systemguards(at)gmail(dot)com>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, pgsql-general(at)postgresql(dot)org
Subject: Re: Update on tables when the row doesn't change
Date: 2005-05-24 20:44:31
Message-ID: 4293922F.9060901@freenet.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Jaime Casanova wrote:
> On 5/24/05, Sebastian Böck <sebastianboeck(at)freenet(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sebastian Böck 2005-05-24 20:45:49 Re: Update on tables when the row doesn't change
Previous Message Devrim GUNDUZ 2005-05-24 19:10:35 Re: Postgres 8.0.3 Fedora RPMS ?