Thread: Re: update behavior
> On Jun 19, 2025, at 11:54 AM, Rui DeSousa <rui@crazybean.net> wrote: > > > >> 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, generatesa WAL entry. There is no shortcut in the processing that's "hey, there's not really a change here, we'll just leavestorage 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. NowI'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 islong. - It has the disadvantage of always running, even in contexts where it might not be needed.
On Thu, Jun 19, 2025 at 1:59 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jun 19, 2025, at 11:54 AM, Rui DeSousa <rui@crazybean.net> wrote:
>
>
>
>> 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. 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!