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

Hi,<br /><br /> To eliminate creation of new tuple version in this case it is necessary to check that update actually
doesn'tchange the record. <br /> It is not a cheapest test and it seems to be not so good idea to perform it always.<br
/>But if you fill that in your case there are many "identical" updates, you can always explicitly rewrite query by
addingextra check:<br /><pre wrap="">UPDATE foo SET val = 'second' where pk = 2 and val <> 'second';</pre><br
/><br/><br /><div class="moz-cite-prefix">On 20.01.2016 12:55, Gasper Zejn wrote:<br /></div><blockquote
cite="mid:CAMxXOOEZ4p0J6nke76XSjs1RfPR0mcb0Ddh-vbmAknjSPq-LTw@mail.gmail.com"type="cite"><pre wrap="">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
</pre><br /><fieldset class="mimeAttachmentHeader"></fieldset><br /><pre wrap="">
</pre></blockquote><br /><pre class="moz-signature" cols="72">-- 
Konstantin Knizhnik
Postgres Professional: <a class="moz-txt-link-freetext"
href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company </pre>