Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.600.51.1.1\))
Subject: Re: update behavior
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <4CE055A7-2368-4FD2-A900-52BFE4732641@crazybean.net>
Date: Thu, 19 Jun 2025 11:58:36 -0600
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Message-Id: <54495544-60AB-4CC8-9F47-78B33F4D99C4@elevated-dev.com>
References: <75F792AF-58DF-4559-A2E8-C89060D1E94E@elevated-dev.com>
 <4CE055A7-2368-4FD2-A900-52BFE4732641@crazybean.net>
To: Rui DeSousa <rui@crazybean.net>
Archived-At: 
 <https://www.postgresql.org/message-id/54495544-60AB-4CC8-9F47-78B33F4D99C4%40elevated-dev.com>
Precedence: bulk

> On Jun 19, 2025, at 11:54=E2=80=AFAM, Rui DeSousa <rui@crazybean.net> =
wrote:
>=20
>=20
>=20
>> On Jun 19, 2025, at 1:23 PM, Scott Ribe <scott_ribe@elevated-dev.com> =
wrote:
>>=20
>> 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. =
There is no shortcut in the processing that's "hey, there's not really a =
change here, we'll just leave storage alone".
>>=20
>> Is this correct?
>>=20
>=20
> Correct, but it can be avoided.=20
>=20
> No update occurs in this case:.=20
>=20
> 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 =
trigger 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.