Thread: UPDATE sql question
Hi, What exactly will happen if UPDATE sql statement instructs to update some columns with the same values as already in the database? Will Postgres update only different values or it will simply modify all columns listed in UPDATE sql? Thanks in advance for any suggestion. ********************************************* * Best Regards --- Andrei Verovski * * Personal Home Page * http://snow.prohosting.com/guru4mac * Mac, Linux, DTP, Development, IT WEB Site *********************************************
On Mon, 2003-07-28 at 03:24, Andrei Verovski wrote: > Hi, > > What exactly will happen if UPDATE sql statement instructs to update > some columns with the same values as already in the database? Will > Postgres update only different values or it will simply modify all > columns listed in UPDATE sql? You mean this: template1=# create table foo (f1 integer); CREATE TABLE template1=# insert into foo values (1); INSERT 16992 1 template1=# insert into foo values (2); INSERT 16993 1 template1=# insert into foo values (1); INSERT 16994 1 template1=# insert into foo values (3); INSERT 16995 1 template1=# select * from foo; 1 2 1 3 template1=# update foo set f1 = 1 where f1 = 1; UPDATE 2 template1=# select * from foo; 2 3 1 1 Looks like it does what you tell it to do... -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
Ron Johnson <ron.l.johnson@cox.net> writes:
> On Mon, 2003-07-28 at 03:24, Andrei Verovski wrote:
>> What exactly will happen if UPDATE sql statement instructs to update
>> some columns with the same values as already in the database? Will
>> Postgres update only different values or it will simply modify all
>> columns listed in UPDATE sql?
> Looks like it does what you tell it to do...
I think he was asking an implementation question, viz: does it skip the
physical update if no values in a row actually change? The answer is
no. I'd think that in most cases, the extra time spent checking to see
whether the updated columns didn't change would be a net loss.
regards, tom lane
On Fri, 2003-08-01 at 10:16, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: > > On Mon, 2003-07-28 at 03:24, Andrei Verovski wrote: > >> What exactly will happen if UPDATE sql statement instructs to update > >> some columns with the same values as already in the database? Will > >> Postgres update only different values or it will simply modify all > >> columns listed in UPDATE sql? > > > Looks like it does what you tell it to do... > > I think he was asking an implementation question, viz: does it skip the > physical update if no values in a row actually change? The answer is > no. I'd think that in most cases, the extra time spent checking to see > whether the updated columns didn't change would be a net loss. Would it always be a net loss, though? If *none* of the fields were updated, then you could burn some CPU (doing comparisons) to save a disk write. CPUs are so fast, nowadays. How many microseconds *would* be spent? Of course, one could always say, "Hey, application! Don't update unchanged values!!!!". -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
Ron Johnson <ron.l.johnson@cox.net> writes:
>> I'd think that in most cases, the extra time spent checking to see
>> whether the updated columns didn't change would be a net loss.
> Would it always be a net loss, though?
You're asking the wrong question. From my perspective, the question
is whether it'd be a net win averaged across all UPDATEs at all
installations everywhere. I can't believe that it would be.
> CPUs are so fast, nowadays. How many microseconds *would* be spent?
That's been a standard excuse for bad design for decades now :-(. Yeah,
the comparisons might be cheap (or not, on some datatypes) ... but the
potentially-avoided computation is reduced by a faster CPU as well.
If you have a particular application and table where no-op UPDATEs occur
often enough that it's really a win to suppress them, you can put in a
trigger to do it. Or better, fix the application to not issue the
UPDATE in the first place; that saves way more computation for the same
basic comparison overhead.
regards, tom lane
On Fri, 2003-08-01 at 11:15, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: > >> I'd think that in most cases, the extra time spent checking to see > >> whether the updated columns didn't change would be a net loss. > > > Would it always be a net loss, though? > > You're asking the wrong question. From my perspective, the question > is whether it'd be a net win averaged across all UPDATEs at all > installations everywhere. I can't believe that it would be. > > > CPUs are so fast, nowadays. How many microseconds *would* be spent? > > That's been a standard excuse for bad design for decades now :-(. Very true! How is it bad design to try and save an IO, though? > Yeah, > the comparisons might be cheap (or not, on some datatypes) ... but the > potentially-avoided computation is reduced by a faster CPU as well. But we don't know. MS, IBM or Oracle have the resources to do that kind of analysis. We don't. > If you have a particular application and table where no-op UPDATEs occur > often enough that it's really a win to suppress them, you can put in a > trigger to do it. Or better, fix the application to not issue the > UPDATE in the first place; that saves way more computation for the same > basic comparison overhead. Which is what I also said... <QUOTE> Of course, one could always say, "Hey, application! Don't update unchanged values!!!!". </QUOTE> -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+