PostgreSQL Discussion - Update on tables when the row doesn't change

Author

Update on tables when the row doesn't change

Sebastian Böck

2005-05-24, 11:23 am

Hi all,

maybe it's a very silly question, but why does Postgres perform an
update on the table even if no data changes?

I recognized this recently doing a rewrite of my rules because they
took to long. I had many conditional rules I collapsed to one
unconditional rule, so that the views get constructed only once. If I
split these updates to the underlying tables, I get a lot of updates
which don't perform any "real" updates.

Can I circumvent this behaviour of Postgres only by defining lot of
rules / triggers on these underlying table are there some trickier ways?

Any help appreciated,

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Martijn van Oosterhout

2005-05-24, 11:23 am

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

Have a nice day,

On Tue, May 24, 2005 at 05:05:34PM +0200, Sebastian Böck wrote:
> Hi all,
>
> maybe it's a very silly question, but why does Postgres perform an
> update on the table even if no data changes?
>
> I recognized this recently doing a rewrite of my rules because they
> took to long. I had many conditional rules I collapsed to one
> unconditional rule, so that the views get constructed only once. If I
> split these updates to the underlying tables, I get a lot of updates
> which don't perform any "real" updates.
>
> Can I circumvent this behaviour of Postgres only by defining lot of
> rules / triggers on these underlying table are there some trickier ways?
>
> Any help appreciated,
>
> Sebastian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly

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

Sebastian Böck

2005-05-24, 11:23 am

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...
>
> Have a nice day,

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Sebastian Böck

2005-05-24, 8:23 pm

Jaime Casanova wrote:
> On 5/24/05, Sebastian Böck < sebastianboeck@freen
et.de> wrote:
>

I attach some sample SQL (commented) to demonstrate the described
scenario:
[color=darkred]
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Sebastian Böck

2005-05-24, 8:23 pm

Sorry, missed the SQL to test.

Sebastian

Tom Lane

2005-05-24, 8:23 pm

=?ISO-8859-1?Q?Sebastian_B=F6ck?= < sebastianboeck@freen
et.de> writes:
> Why does Postgres perform updates to tables, even if the row doesn't
> change at all?

Because testing for this would almost surely be a net loss for the vast
majority of applications. Checking to see if the new row value exactly
equals the old is hardly a zero-cost operation; if you pay that on every
update, that's a lot of overhead that you are hoping to make back by
sometimes avoiding the physical store of the new tuple. In most
applications I think the "sometimes" isn't going to be often enough
to justify doing it.

If you have a particular table in a particular app where it is worth it,
I'd recommend writing a BEFORE UPDATE trigger to make the comparisons
and suppress the update when NEW and OLD are equal.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Richard Huxton

2005-05-25, 3:23 am

Tom Lane wrote:
> =?ISO-8859-1?Q?Sebastian_B=F6ck?= < sebastianboeck@freen
et.de> writes:
>
>
>
> Because testing for this would almost surely be a net loss for the vast
> majority of applications. Checking to see if the new row value exactly
> equals the old is hardly a zero-cost operation; if you pay that on every
> update, that's a lot of overhead that you are hoping to make back by
> sometimes avoiding the physical store of the new tuple. In most
> applications I think the "sometimes" isn't going to be often enough
> to justify doing it.
>
> If you have a particular table in a particular app where it is worth it,
> I'd recommend writing a BEFORE UPDATE trigger to make the comparisons
> and suppress the update when NEW and OLD are equal.

In any case, what if I have a trigger that's supposed to increment a
counter or similar if issue a supposedly "unneeded" update.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Dawid Kuroczko

2005-05-25, 3:23 am

On 5/24/05, Sebastian Böck < sebastianboeck@freen
et.de> wrote:
> /* 3rd way of separating updates
> con: unnecessary updates on tables
> pro: view gets evaluated only 1 time
>
> Not adressing the problem of unnecessary updates, but the view
> gets only evaluated one time.
>
> */
>
> CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$
> DECLARE
> NEW ALIAS FOR $1;
> BEGIN
> RAISE NOTICE 'UPDATE';
> 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;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;

Control question, I didn't check it, but would it be enough to change from:
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
to:
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1?

.... I may be wrong. :)

Regards,
Dawid

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sebastian Böck

2005-05-25, 7:23 am

Dawid Kuroczko wrote:
> On 5/24/05, Sebastian Böck < sebastianboeck@freen
et.de> wrote:
>
>
>
> Control question, I didn't check it, but would it be enough to change from:
> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
> to:
> UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1?
>
> ... I may be wrong. :)

Yes, thats more elegant then my other (4th) solution.
Was late yesterday evening ;)

Thanks

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Martijn van Oosterhout

2005-05-25, 7:23 am

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.

Sebastian Böck

2005-05-25, 7:23 am

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:
[color=darkred]
> On Wed, May 25, 2005 at 10:40:38AM +0200, Sebastian Böck wrote:
>

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dawid Kuroczko

2005-05-25, 7:23 am

On 5/25/05, Sebastian Böck < sebastianboeck@freen
et.de> wrote:
>
> Yes, thats more elegant then my other (4th) solution.
> Was late yesterday evening ;)

Be wary of the NULL values though. :) Either don't use them, add
something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1
IS NULL)' or something more complicated. :)

Regards,
Dawid

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sebastian Böck

2005-05-25, 7:23 am

Dawid Kuroczko wrote:

>
>
> Be wary of the NULL values though. :) Either don't use them, add
> something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1
> IS NULL)' or something more complicated. :)

Thanks for the notice, but I have a special operator for this:

CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS
BOOLEAN AS $$
BEGIN
IF ($1 <> $2) OR ($1 IS NULL <> $2 IS NULL) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OPERATOR <<>> (
LEFTARG = ANYELEMENT,
RIGHTARG = ANYELEMENT,
PROCEDURE = different,
COMMUTATOR = <<>>,
NEGATOR = ====
);

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Ragnar Hafstað

2005-05-25, 9:23 am

On Wed, 2005-05-25 at 13:09 +0200, Sebastian Böck wrote:
> Dawid Kuroczko wrote:
>
>
> Thanks for the notice, but I have a special operator for this:
>
> CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS

and then there is the 'IS DISTINCT FROM' construct
http://www.postgresql.org/docs/8.0/...on.html#AEN6094

gnari

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Sebastian Böck

2005-05-25, 9:23 am

Ragnar Hafstað wrote:
> On Wed, 2005-05-25 at 13:09 +0200, Sebastian Böck wrote:
>
>
>
> and then there is the 'IS DISTINCT FROM' construct
> http://www.postgresql.org/docs/8.0/...on.html#AEN6094
>

Much easier :)

Thanks

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Tim Vadnais

2005-05-25, 11:23 am

Hi All,

Can someone please address this aspect of Sebastian's email? I, too, am
interested in the response.
[color=darkred]

Tim

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sebastian Böck
Sent: Tuesday, May 24, 2005 8:06 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Update on tables when the row doesn't change

Hi all,

Maybe it's a very silly question, but why does Postgres perform an update on
the table even if no data changes?

I recognized this recently doing a rewrite of my rules because they took to
long. I had many conditional rules I collapsed to one unconditional rule, so
that the views get constructed only once. If I split these updates to the
underlying tables, I get a lot of updates which don't perform any "real"
updates.

Can I circumvent this behaviour of Postgres only by defining lot of rules /
triggers on these underlying table are there some trickier ways?

Any help appreciated,

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Scott Marlowe

2005-05-25, 11:23 am

On Wed, 2005-05-25 at 10:41, Tim Vadnais wrote:

> Hi All,
>
> Can someone please address this aspect of Sebastian's email? I, too, am
> interested in the response.
>

Actually, I believe it was addressed by Tom, when he said that it would
be more costly to check every single update to see if there WAS a change
before applying it than to just apply the changes every time.

I concur. Can you imagine slowing down ALL updates by 5% or something
like that just to prevent the rare case where an update didn't actually
change a value?

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Martijn van Oosterhout

2005-05-25, 11:23 am

On Wed, May 25, 2005 at 08:41:23AM -0700, Tim Vadnais wrote:
> Hi All,
>
> Can someone please address this aspect of Sebastian's email? I, too, am
> interested in the response.
>

Tom did, AFAIK. Basically, it's a non-zero cost to check for something
that's useless in 99.99% of cases. Secondly, if you have a update
trigger on the table, it applies to all updates, even if they don't
change the actual data. If you didn't want to update the row, don't
issue it in the first place.

If you want to block the update, create your own trigger to detect it
and drop it. Most people don't need it.

That's completely seperate to his issue with reevaluating the view,
where I've seen no response to the question about what he thinks
PostgreSQL should be doing. You know, the query plan he get vs what he
actually wants.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/[color=darkred]
> 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.

Sponsored Links