I've been trying to use PostgreSQL upsert in order to update values in case such row already exists. In my use case it's very likely that new values will be exactly the same as the existing values, so I wanted to be sure that no update will be performed (that is - no new row with higher xmax version will be created) if the values are the same. I've tried using IS DISTINCT FROM clause for this, and while it works as expected in regular update statement (xmax stays the same for updates that have no effect), it still seems to create new rows when used in an upsert expression.

Please consider the following example:

1. Create test table:

create table upsert_test (id integer, name text, primary key (id));

2. Insert row and get its xmax version:

insert into upsert_test (id, name) values (1, 'hello');
select *, xmax from upsert_test;

3. Do upsert that has no effect. Observe that xmax is incremented (unexpectedly) with each execution:

insert into upsert_test (id, name) values (1, 'hello')
on conflict on constraint upsert_test_pkey do update
set name = excluded.name where upsert_test is distinct from excluded;

select *, xmax from upsert_test ;

Is this an expected behavior? Is there's a way to prevent PostgreSQL from creation new rows for this case?

CodePudding user response：

If you look at ctid as well, you will see that that that value didn't change, so the row is still the same. xmax was changed because the row was locked during the operation.

The row is not updated, but modified, and that modification will cause WAL writes and dirties the block with the row, so that it will cause a write. That may be unfortunate for you, but there is no way around it.