Re: Proposal for UPDATE: do not insert new tuple on heap if update does not change data

Hi,

To eliminate creation of new tuple version in this case it is necessary
to check that update actually doesn't change the record.
It is not a cheapest test and it seems to be not so good idea to perform
it always.
But if you fill that in your case there are many "identical" updates,
you can always explicitly rewrite query by adding extra check:

UPDATE foo SET val = 'second' where pk = 2 and val <> 'second';

On 20.01.2016 12:55, Gasper Zejn wrote:
> Hi,
>
> I was wondering if PostgreSQL adds new tuple if data is not changed
> when using UPDATE. It turns out it does add them and I think it might
> be beneficial not to add a new tuple in this case, since it causes a
> great deal of maintenance: updating indexes, vacuuming table and
> index, also heap fragmentation.
>
> How to check:
>
> CREATE TABLE foo (pk serial primary key, val text);
> -- Starting point: two rows.
> INSERT INTO foo VALUES (1, 'first');
> INSERT INTO foo VALUES (2, 'second');
> CHECKPOINT;
>
> -- Updating row with same value.
> UPDATE foo SET val = 'second' where pk = 2;
> CHECKPOINT;
>
> -- "Upsert" is the same.
> INSERT INTO foo VALUES (2, 'second') ON CONFLICT (pk) DO UPDATE SET
> val = 'second';
> CHECKPOINT;
>
> If after any checkpoint you look at page data, you can see multiple
> versions of same row with "second".
>
> Unfortunately, I don't believe I can come up with a patch on my own,
> but will happily offer any further help with testing and ideas.
>
>
> Attached is a script with minimal test case.
>
> Kind regards,
> Gasper Zejn
>
>

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company