MIME-Version: 1.0
References: <75F792AF-58DF-4559-A2E8-C89060D1E94E@elevated-dev.com>
 <4CE055A7-2368-4FD2-A900-52BFE4732641@crazybean.net>
 <54495544-60AB-4CC8-9F47-78B33F4D99C4@elevated-dev.com>
In-Reply-To: <54495544-60AB-4CC8-9F47-78B33F4D99C4@elevated-dev.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 19 Jun 2025 14:06:08 -0400
Message-ID: 
 <CANzqJaCj95JOKUd_ihcba4OqHTWnqEfz67hgaRezq4a49EF-eA@mail.gmail.com>
Subject: Re: update behavior
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003d86900637f09a8a"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaCj95JOKUd_ihcba4OqHTWnqEfz67hgaRezq4a49EF-eA%40mail.gmail.com>
Precedence: bulk

--0000000000003d86900637f09a8a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Jun 19, 2025 at 1:59=E2=80=AFPM Scott Ribe <scott_ribe@elevated-dev=
.com>
wrote:

> > On Jun 19, 2025, at 11:54=E2=80=AFAM, Rui DeSousa <rui@crazybean.net> w=
rote:
> >
> >
> >
> >> On Jun 19, 2025, at 1:23 PM, Scott Ribe <scott_ribe@elevated-dev.com>
> wrote:
> >>
> >> I believe that if I UPDATE a row with the same values that it already
> has, this still dirties pages, writes the row, generates a WAL entry. The=
re
> is no shortcut in the processing that's "hey, there's not really a change
> here, we'll just leave storage alone".
> >>
> >> Is this correct?
> >>
> >
> > Correct, but it can be avoided.
> >
> > No update occurs in this case:.
> >
> > update foo
> >   set data =3D =E2=80=98hello world=E2=80=99
> > where id =3D 33
> >    and data is distinct from =E2=80=98hello world=E2=80=99
> > ;
>
> That was my thought when I posted the original question, when I didn't
> know about suppress_redundant_updates_trigger. Now I'm thinking the trigg=
er
> is an option.
>
> - The trigger has the advantage that one doesn't have to maintain the
> WHERE clause--especially if the list of columns is long.
> - It has the disadvantage of always running, even in contexts where it
> might not be needed.
>

How much would fillfactor=3D50 (so as to enable HOT updates) mitigate the
problem?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--0000000000003d86900637f09a8a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jun 19, 2025 at 1:59=E2=80=AFPM S=
cott Ribe &lt;<a href=3D"mailto:scott_ribe@elevated-dev.com">scott_ribe@ele=
vated-dev.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_con=
tainer"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex">&gt; On Jun 19, 2=
025, at 11:54=E2=80=AFAM, Rui DeSousa &lt;<a href=3D"mailto:rui@crazybean.n=
et" target=3D"_blank">rui@crazybean.net</a>&gt; wrote:<br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt;&gt; On Jun 19, 2025, at 1:23 PM, Scott Ribe &lt;<a href=3D"mailto:scot=
t_ribe@elevated-dev.com" target=3D"_blank">scott_ribe@elevated-dev.com</a>&=
gt; wrote:<br>
&gt;&gt; <br>
&gt;&gt; I believe that if I UPDATE a row with the same values that it alre=
ady has, this still dirties pages, writes the row, generates a WAL entry. T=
here is no shortcut in the processing that&#39;s &quot;hey, there&#39;s not=
 really a change here, we&#39;ll just leave storage alone&quot;.<br>
&gt;&gt; <br>
&gt;&gt; Is this correct?<br>
&gt;&gt; <br>
&gt; <br>
&gt; Correct, but it can be avoided. <br>
&gt; <br>
&gt; No update occurs in this case:. <br>
&gt; <br>
&gt; update foo<br>
&gt;=C2=A0 =C2=A0set data =3D =E2=80=98hello world=E2=80=99<br>
&gt; where id =3D 33<br>
&gt;=C2=A0 =C2=A0 and data is distinct from =E2=80=98hello world=E2=80=99<b=
r>
&gt; ;<br>
<br>
That was my thought when I posted the original question, when I didn&#39;t =
know about suppress_redundant_updates_trigger. Now I&#39;m thinking the tri=
gger is an option.<br>
<br>
- The trigger has the advantage that one doesn&#39;t have to maintain the W=
HERE clause--especially if the list of columns is long.<br>
- It has the disadvantage of always running, even in contexts where it migh=
t not be needed.<br></blockquote><div>=C2=A0</div><div>How much would fillf=
actor=3D50 (so as to enable HOT updates) mitigate the problem?</div></div><=
div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=
=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&g=
t;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><=
div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000003d86900637f09a8a--