update with no changes

On Fri, Nov 19, 2021 at 9:38 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

If nothing was changed, why create a new record, append data to wal, set
old record as deleted, etc, etc ?

Because it takes resources to determine that nothing changed. If you want
to opt-in into that there is even an extension trigger that makes doing so
fairly simple. But it's off by default because the typical case is that
people don't frequently perform no-op updates so why eat the expense.

David J.

Because it takes resources to determine that nothing changed. If you want
to opt-in into that there is even an extension trigger that makes doing so
fairly simple. But it's off by default because the typical case is that
people don't frequently perform no-op updates so why eat the expense.

But it takes resources for other operations, right ?
I think this is not unusual. If an user double click on a grid, just sees a
record and clicks ok to save, probably that application calls an update
instead of seeing if some field were changed before that.

Hi,

On November 19, 2021 8:38:25 AM PST, Marcos Pegoraro <marcos@f10.com.br> wrote:

Why this happens ?

create table t(i int);
CREATE TABLE
insert into t values(1);
INSERT 0 1
select (ctid::text::point)[1]::int, * from t;
ctid | i
------+---
1 | 1
(1 row)
update t set i = i;
UPDATE 1
select (ctid::text::point)[1]::int, * from t;
ctid | i
------+---
2 | 1
(1 row)

If nothing was changed, why create a new record, append data to wal, set
old record as deleted, etc, etc ?

You can't just skip doing updates without causing problems. An update basically acquires an exclusive row lock (which in turn prevents foreign key references from being removed etc). Just skipping that would cause a lot of new deadlocks and correctness issues.

There's also cases where people intentionally perform updates to move records around etc.

Regards,

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Marcos Pegoraro <marcos@f10.com.br> writes:

But it takes resources for other operations, right ?
I think this is not unusual. If an user double click on a grid, just sees a
record and clicks ok to save, probably that application calls an update
instead of seeing if some field were changed before that.

[ shrug... ] As David said, if you think that it's important to have
such a check in a particular application, use a trigger to check it.
There's one built-in, you don't even need an extension:

https://www.postgresql.org/docs/current/functions-trigger.html

We're not going to make that happen by default though, because it'd
be a net drag on better-written applications.

regards, tom lane

On Fri, Nov 19, 2021 at 10:03 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

Because it takes resources to determine that nothing changed. If you want

to opt-in into that there is even an extension trigger that makes doing so
fairly simple. But it's off by default because the typical case is that
people don't frequently perform no-op updates so why eat the expense.

But it takes resources for other operations, right ?
I think this is not unusual. If an user double click on a grid, just sees
a record and clicks ok to save, probably that application calls an update
instead of seeing if some field were changed before that.

This has been the documented behavior for decades. I suggest you research
prior discussions on the topic if you need more than what has been
provided. You'd need to bring up some novel points about why a change here
would be overall beneficial to get any interest, at least from me, in
discussing the topic further.

I get the idea of letting the server centralize logic like this - but
frankly if the application is choosing to send all that data across the
wire just to have the server throw it away the application is wasting
network I/O. If it does manage its resources carefully then the server
will never even see an update and its behavior here becomes moot.

David J.

I get the idea of letting the server centralize logic like this - but
frankly if the application is choosing to send all that data across the
wire just to have the server throw it away the application is wasting
network I/O. If it does manage its resources carefully then the server
will never even see an update and its behavior here becomes moot.

I understand your point, it´s responsability of application to do what it

has to do. But lots of times (maybe 98% of them) is not same people doing
server side and application side. So, Postgres guys will have to review all
code being done on apps ?

And ok, thanks for explaining me.

On Fri, Nov 19, 2021 at 10:57 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

So, Postgres guys will have to review all code being done on apps ?

I suppose if the application side cannot be trusted to code to a
specification without having the server side add validation and/or
compensation code to catch the bugs then, yes, one option is to have the
server side do extra work. There are other solutions, some of which are
not even technical in nature.

David J.

I suppose if the application side cannot be trusted to code to a
specification without having the server side add validation and/or
compensation code to catch the bugs then, yes, one option is to have the
server side do extra work. There are other solutions, some of which are
not even technical in nature.

Just to show you my problem, since I wrote my first email of this

discussion, I changed a little my auditing trigger to get total of records
being updated. Only last 3 or 4 hours we´re talking, we had 12% of them
with no changes. It is a lot.

Thanks again, we have a huge code review here.

regards,
Marcos

On Fri, Nov 19, 2021 at 10:20 AM Andres Freund <andres@anarazel.de> wrote:

You can't just skip doing updates without causing problems.

Given you can do exactly this by using a trigger this statement is either
false or I'm missing some piece of knowledge it relies upon.

David J.

On 11/19/21 12:57, Marcos Pegoraro wrote:

I get the idea of letting the server centralize logic like this -
but frankly if the application is choosing to send all that data
across the wire just to have the server throw it away the
application is wasting network I/O.  If it does manage its
resources carefully then the server will never even see an update
and its behavior here becomes moot.

I understand your point, it´s responsability of application to do what
it has to do. But lots of times (maybe 98% of them) is not same people
doing server side and application side. So, Postgres guys will have to
review all code being done on apps ?

And ok, thanks for explaining me.

suppress_redundant_updates_trigger was created precisely because it's
not always easy to create application code in such a way that it
generates no redundant updates. However, there is a cost to using it,
and the break even point can be surprisingly high. It should therefore
be used with caution, and after appropriate benchmarks.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

suppress_redundant_updates_trigger was created precisely because it's
not always easy to create application code in such a way that it
generates no redundant updates. However, there is a cost to using it,
and the break even point can be surprisingly high. It should therefore
be used with caution, and after appropriate benchmarks.

well, there is a cost of not using it too. If lots of things needs to be

done when a record is stored, and if it doesn´t needed to be stored, all
these things will not be done. So, what are pros of changing a record
which did not changed any value and what are cons of it ? So, I understood
the way it works and yes, my point of view is that this trigger is really
needed, for me, obviously.

On 11/20/21 10:03, Marcos Pegoraro wrote:

suppress_redundant_updates_trigger was created precisely because it's
not always easy to create application code in such a way that it
generates no redundant updates. However, there is a cost to using it,
and the break even point can be surprisingly high. It should therefore
be used with caution, and after appropriate benchmarks.

well, there is a cost of not using it too. If lots of things needs to
be done when a record is stored, and if it doesn´t needed to be
stored, all these things will not be done.  So, what are pros of
changing a record which did not changed any value and what are cons of
it ? So, I understood the way it works and yes, my point of view is
that this trigger is really needed, for me, obviously.

If you need it then use it. It's been built into postgres since release
8.4. Just be aware that if you use it there is a cost incurred for every
record updated whether or not the record is redundant. If only 12% of
your updates are redundant I suspect it will be a net loss for you, but
as I said above you should benchmark it.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com