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
pg-duplicate-tuple.sh
Description: Bourne shell script
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
