Subject: update behavior

From: Scott Ribe <[email-masked]>
Date: Thu, 19 Jun 2025 11:23:58 -0600

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".

Is this correct?

--
Scott Ribe
[email-masked]
https://www.linkedin.com/in/scottribe/

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 19 Jun 2025 10:31:59 -0700

On Thu, Jun 19, 2025 at 10:24 AM Scott Ribe <[email-masked]>
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. There 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.  You need a trigger to prevent the update.  There is one provided:
suppress_redundant_updates_trigger()

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

David J.

---

From: Scott Ribe <[email-masked]>
Date: Thu, 19 Jun 2025 11:34:19 -0600

> On Jun 19, 2025, at 11:31 AM, David G. Johnston <[email-masked]> wrote:
> 
> Correct.  You need a trigger to prevent the update.  There is one provided: suppress_redundant_updates_trigger()

Or, in my case that prompted this question, I need a WHERE clause for the ON CONFLICT UPDATE...

---

From: Tom Lane <[email-masked]>
Date: Thu, 19 Jun 2025 13:35:37 -0400

> Is this correct?

It is.  We do offer a trigger you can add to suppress zero-change
updates [1], but that's not the default behavior.  We judged that
checking for that would add more cycles than it removes, for most
applications most of the time.

			regards, tom lane

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

---

From: Scott Ribe <[email-masked]>
Date: Thu, 19 Jun 2025 11:39:54 -0600

> On Jun 19, 2025, at 11:34 AM, Scott Ribe <[email-masked]> wrote:
> 

Wait, should suppress_redundant_updates_trigger be used even in this case? Would it suppress the update before the constraint checks and invocation of the ON CONFLICT clause???

---

From: Scott Ribe <[email-masked]>
Date: Thu, 19 Jun 2025 11:48:30 -0600

> On Jun 19, 2025, at 11:39 AM, Scott Ribe <[email-masked]> wrote:
> 

Or no, duh, this starts with an INSERT where that won't be run, there is no UPDATE until after the constraint violation, but then at that point it would suppress the update?

---

From: Rui DeSousa <[email-masked]>
Date: Thu, 19 Jun 2025 13:51:39 -0400

> On Jun 19, 2025, at 1:23 PM, Scott Ribe <[email-masked]> wrote:
> 

Correct, but it can be avoided. 

No update occurs in this case:. 

update foo
  set data = ‘hello world’
where id = 33
   and data is distinct from ‘hello world’
;

---

From: Rui DeSousa <[email-masked]>
Date: Thu, 19 Jun 2025 13:57:20 -0400

update foo
  set data = ‘hello world’
where id = 33

---

From: Scott Ribe <[email-masked]>
Date: Thu, 19 Jun 2025 11:58:36 -0600

> On Jun 19, 2025, at 11:54 AM, Rui DeSousa <[email-masked]> wrote:
> 
> 
> 

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.

---

From: Ron Johnson <[email-masked]>
Date: Thu, 19 Jun 2025 14:06:08 -0400

On Thu, Jun 19, 2025 at 1:59 PM Scott Ribe <[email-masked]>
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. There
> is no shortcut in the processing that's "hey, there's not really a change
> 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.

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

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 19 Jun 2025 14:29:29 -0400

> Or no, duh, this starts with an INSERT where that won't be run, there is no UPDATE until after the constraint violation, but then at that point it would suppress the update?

I believe we fire ON UPDATE triggers when an INSERT ON CONFLICT
takes an UPDATE action, so it should do what you want.  The
overhead tradeoff is yours to make.

			regards, tom lane

---

From: Scott Ribe <[email-masked]>
Date: Mon, 23 Jun 2025 11:29:50 -0600

> On Jun 19, 2025, at 12:29 PM, Tom Lane <[email-masked]> wrote:
> 
> takes an UPDATE action, so it should do what you want.

Tested, and confirmed, this morning.