public inbox for [email protected]help / color / mirror / Atom feed
update behavior 12+ messages / 6 participants [nested] [flat]
* update behavior @ 2025-06-19 17:23 Scott Ribe <[email protected]> 0 siblings, 5 replies; 12+ messages in thread From: Scott Ribe @ 2025-06-19 17:23 UTC (permalink / raw) To: Pgsql-admin <[email protected]> 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 protected] https://www.linkedin.com/in/scottribe/ ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:31 David G. Johnston <[email protected]> parent: Scott Ribe <[email protected]> 4 siblings, 1 reply; 12+ messages in thread From: David G. Johnston @ 2025-06-19 17:31 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: Pgsql-admin <[email protected]> On Thu, Jun 19, 2025 at 10:24 AM Scott Ribe <[email protected]> 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. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:34 Scott Ribe <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Scott Ribe @ 2025-06-19 17:34 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Pgsql-admin <[email protected]> > On Jun 19, 2025, at 11:31 AM, David G. Johnston <[email protected]> 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... ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:35 Tom Lane <[email protected]> parent: Scott Ribe <[email protected]> 4 siblings, 0 replies; 12+ messages in thread From: Tom Lane @ 2025-06-19 17:35 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: Pgsql-admin <[email protected]> Scott Ribe <[email protected]> writes: > 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? 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 ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:39 Scott Ribe <[email protected]> parent: Scott Ribe <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Scott Ribe @ 2025-06-19 17:39 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Pgsql-admin <[email protected]> > On Jun 19, 2025, at 11:34 AM, Scott Ribe <[email protected]> wrote: > >> On Jun 19, 2025, at 11:31 AM, David G. Johnston <[email protected]> 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... 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??? ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:48 Scott Ribe <[email protected]> parent: Scott Ribe <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Scott Ribe @ 2025-06-19 17:48 UTC (permalink / raw) To: David G. Johnston <[email protected]>; Tom Lane <[email protected]>; +Cc: Pgsql-admin <[email protected]> > On Jun 19, 2025, at 11:39 AM, Scott Ribe <[email protected]> wrote: > >> On Jun 19, 2025, at 11:34 AM, Scott Ribe <[email protected]> wrote: >> >>> On Jun 19, 2025, at 11:31 AM, David G. Johnston <[email protected]> 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... > > 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??? 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? ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:51 Rui DeSousa <[email protected]> parent: Scott Ribe <[email protected]> 4 siblings, 0 replies; 12+ messages in thread From: Rui DeSousa @ 2025-06-19 17:51 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: Pgsql-admin <[email protected]> > On Jun 19, 2025, at 1:23 PM, Scott Ribe <[email protected]> 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, 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’ ; ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:57 Rui DeSousa <[email protected]> parent: Scott Ribe <[email protected]> 4 siblings, 0 replies; 12+ messages in thread From: Rui DeSousa @ 2025-06-19 17:57 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: Pgsql-admin <[email protected]> > On Jun 19, 2025, at 1:23 PM, Scott Ribe <[email protected]> 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, 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’ ; ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 17:58 Scott Ribe <[email protected]> parent: Scott Ribe <[email protected]> 4 siblings, 1 reply; 12+ messages in thread From: Scott Ribe @ 2025-06-19 17:58 UTC (permalink / raw) To: Rui DeSousa <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Tom Lane <[email protected]> > On Jun 19, 2025, at 11:54 AM, Rui DeSousa <[email protected]> wrote: > > > >> On Jun 19, 2025, at 1:23 PM, Scott Ribe <[email protected]> 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, 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’ > ; 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. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 18:06 Ron Johnson <[email protected]> parent: Scott Ribe <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Ron Johnson @ 2025-06-19 18:06 UTC (permalink / raw) To: Pgsql-admin <[email protected]> On Thu, Jun 19, 2025 at 1:59 PM Scott Ribe <[email protected]> wrote: > > On Jun 19, 2025, at 11:54 AM, Rui DeSousa <[email protected]> wrote: > > > > > > > >> On Jun 19, 2025, at 1:23 PM, Scott Ribe <[email protected]> > 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, 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’ > > ; > > 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. > 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! ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-19 18:29 Tom Lane <[email protected]> parent: Scott Ribe <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tom Lane @ 2025-06-19 18:29 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pgsql-admin <[email protected]> Scott Ribe <[email protected]> writes: > On Jun 19, 2025, at 11:39 AM, Scott Ribe <[email protected]> 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??? > 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 ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: update behavior @ 2025-06-23 17:29 Scott Ribe <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Scott Ribe @ 2025-06-23 17:29 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pgsql-admin <[email protected]> > On Jun 19, 2025, at 12:29 PM, Tom Lane <[email protected]> wrote: > > I believe we fire ON UPDATE triggers when an INSERT ON CONFLICT > takes an UPDATE action, so it should do what you want. Tested, and confirmed, this morning. ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-06-23 17:29 UTC | newest] Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-19 17:23 update behavior Scott Ribe <[email protected]> 2025-06-19 17:31 ` David G. Johnston <[email protected]> 2025-06-19 17:34 ` Scott Ribe <[email protected]> 2025-06-19 17:39 ` Scott Ribe <[email protected]> 2025-06-19 17:48 ` Scott Ribe <[email protected]> 2025-06-19 18:29 ` Tom Lane <[email protected]> 2025-06-23 17:29 ` Scott Ribe <[email protected]> 2025-06-19 17:35 ` Tom Lane <[email protected]> 2025-06-19 17:51 ` Rui DeSousa <[email protected]> 2025-06-19 17:57 ` Rui DeSousa <[email protected]> 2025-06-19 17:58 ` Scott Ribe <[email protected]> 2025-06-19 18:06 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox