Re: update with no changes

Hi,

On November 19, 2021 8:38:25 AM PST, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:
>Why this happens ?
>
>create table t(i int);
>CREATE TABLE
>insert into t values(1);
>INSERT 0 1
>select (ctid::text::point)[1]::int, * from t;
> ctid | i
>------+---
> 1 | 1
>(1 row)
>update t set i = i;
>UPDATE 1
>select (ctid::text::point)[1]::int, * from t;
> ctid | i
>------+---
> 2 | 1
>(1 row)
>
>If nothing was changed, why create a new record, append data to wal, set
>old record as deleted, etc, etc ?

You can't just skip doing updates without causing problems. An update basically acquires an exclusive row lock (which in turn prevents foreign key references from being removed etc). Just skipping that would cause a lot of new deadlocks and correctness issues.

There's also cases where people intentionally perform updates to move records around etc.

Regards,

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.