Inconsistent update in the MERGE command

Dmitry <dsy.075@yandex.ru>

From: Dmitry <dsy.075@yandex.ru>
To: pgsql-hackers@lists.postgresql.org
Date: 2025-08-21T13:30:09Z
Lists: pgsql-hackers
Hi hackers,

I noticed an inconsistent update when executing MERGE commands, which 
looks more like a bug.
In my test example, the value of 'val' should increase in an ascending 
monotonous sequence.

Test system
===========
- Architecture: x86_64
- OS: Ubuntu 24.04.3 LTS (Noble Numbat)
- Tested postgres version(s):
      - latest 17 (17.6)

Steps to reproduce
==================
   postgres=# create table t_merge (id int primary key, val int);
   CREATE TABLE
   postgres=# create table t_merge_chk (val int primary key);
   CREATE TABLE
   postgres=# insert into t_merge values (1,0);
   INSERT 0 1

   pgbench --no-vacuum --exit-on-abort -c 10 --file=/dev/stdin <<'EOF'
   begin;
   merge into t_merge t
   using (select 1 id) s on (t.id = s.id)
   when matched then update set val = t.val + 1
   returning val \gset

   -- Checking the uniqueness of a value
   insert into t_merge_chk (val) values (:val);
   commit;
   EOF

   pgbench: error: client 3 script 0 aborted in command 2 query 0: 
ERROR:  duplicate key value violates unique constraint "t_merge_chk_pkey"
   DETAIL:  Key (val)=(2) already exists.


What do you think about this?

Best regards,
Dmitry




Commits

  1. Fix concurrent update issue with MERGE.