Inconsistent update in the MERGE command

From: Dmitry <dsy(dot)075(at)yandex(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Inconsistent update in the MERGE command
Date: 2025-08-21 13:30:09
Message-ID: 1570d30e-2b95-4239-b9c3-f7bf2f2f8556@yandex.ru
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
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

Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2025-08-21 13:44:01 Re: vacuumdb --missing-stats-only and permission issue
Previous Message Jim Jones 2025-08-21 12:17:19 Re: Add GUC to enable libxml2's XML_PARSE_HUGE