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
