Thread: update with no changes
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)
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 ?
regards,
Marcos
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 inturn prevents foreign key references from being removed etc). Just skipping that would cause a lot of new deadlocks andcorrectness 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