BUG #19355: Attempt to insert data unexpectedly during concurrent update

The following bug has been logged on the website:

Bug reference:      19355
Logged by:          Bihua Wang
Email address:      wangbihua.cn@gmail.com
PostgreSQL version: 18.1
Operating system:   linux
Description:

Start two transaction and update on same tuple, raise concurrent update and
evalplanqual.  It will be found out that the session with evalplanqual did
not successfully update the data, but instead attempted to insert a row of
data incorrectly.

-- postgresql version
psql (18.1)
Type "help" for help.

postgres=# SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (EulerOS 4.8.5-28), 64-bit

-- prepare relation
drop table t1,t2,t3;
create table t1(a int primary key, b int);
create table t2(a int, b int);
create table t3(a int, b int);

insert into t1 values(1,0),(2,0);
insert into t2 values(1,1),(2,2);
insert into t3 values(3,3);
-- test sql
merge /*+ nestloop(t3 t1)) */ into t1 using
        (select t2.a as a from t2 group by t2.a) as t3
        on (t1.a = t3.a)
when matched then
        update set b = t1.b + 1
when not matched then
        insert (a,b) values (1,1);

--make sure plan like this, may need to execute  "set enable_hashjoin=off"
if necessay
postgres=*#  explain merge /*+ nestloop(t3 t1)) */ into t1 using
        (select t2.a as a from t2 group by t2.a) as t3
        on (t1.a = t3.a)
when matched then
        update set b = t1.b + 1
when not matched then
        insert (a,b) values (1,1);
postgres-*# postgres-*# postgres-*# postgres-*# postgres-*# postgres-*#
QUERY PLAN
-------------------------------------------------------------------------------
 Merge on t1  (cost=38.41..124.75 rows=0 width=0)
   ->  Nested Loop Left Join  (cost=38.41..124.75 rows=200 width=34)
         ->  Subquery Scan on t3  (cost=38.25..42.25 rows=200 width=32)
               ->  HashAggregate  (cost=38.25..40.25 rows=200 width=4)
                     Group Key: t2.a
                     ->  Seq Scan on t2  (cost=0.00..32.60 rows=2260
width=4)
         ->  Index Scan using t1_pkey on t1  (cost=0.15..0.41 rows=1
width=10)
               Index Cond: (a = t3.a)
(8 rows)


-- session 1:
postgres=# begin;
BEGIN
postgres=*#  explain merge /*+ nestloop(t3 t1)) */ into t1 using
        (select t2.a as a from t2 group by t2.a) as t3
        on (t1.a = t3.a)
when matched then
        update set b = t1.b + 1
when not matched then
        insert (a,b) values (1,1);
postgres-*# postgres-*# postgres-*# postgres-*# postgres-*# postgres-*#
QUERY PLAN
-------------------------------------------------------------------------------
 Merge on t1  (cost=38.41..124.75 rows=0 width=0)
   ->  Nested Loop Left Join  (cost=38.41..124.75 rows=200 width=34)
         ->  Subquery Scan on t3  (cost=38.25..42.25 rows=200 width=32)
               ->  HashAggregate  (cost=38.25..40.25 rows=200 width=4)
                     Group Key: t2.a
                     ->  Seq Scan on t2  (cost=0.00..32.60 rows=2260
width=4)
         ->  Index Scan using t1_pkey on t1  (cost=0.15..0.41 rows=1
width=10)
               Index Cond: (a = t3.a)
(8 rows)

postgres=*# merge /*+ nestloop(t3 t1)) */ into t1 using
        (select t2.a as a from t2 group by t2.a) as t3
        on (t1.a = t3.a)
when matched then
        update set b = t1.b + 1
when not matched then
        insert (a,b) values (1,1);
postgres-*# postgres-*# postgres-*# postgres-*# postgres-*# postgres-*#
MERGE 2
postgres=*# end;
COMMIT

-- session 2
postgres=# begin;
BEGIN
postgres=*# explain merge /*+ nestloop(t3 t1)) */ into t1 using
        (select t2.a as a from t2 group by t2.a) as t3
        on (t1.a = t3.a)
when matched then
        update set b = t1.b + 1
when not matched then
        insert (a,b) values (1,1);
postgres-*# postgres-*# postgres-*# postgres-*# postgres-*# postgres-*#
QUERY PLAN
-------------------------------------------------------------------------------
 Merge on t1  (cost=38.41..124.75 rows=0 width=0)
   ->  Nested Loop Left Join  (cost=38.41..124.75 rows=200 width=34)
         ->  Subquery Scan on t3  (cost=38.25..42.25 rows=200 width=32)
               ->  HashAggregate  (cost=38.25..40.25 rows=200 width=4)
                     Group Key: t2.a
                     ->  Seq Scan on t2  (cost=0.00..32.60 rows=2260
width=4)
         ->  Index Scan using t1_pkey on t1  (cost=0.15..0.41 rows=1
width=10)
               Index Cond: (a = t3.a)
(8 rows)

postgres=*# merge /*+ nestloop(t3 t1)) */ into t1 using
        (select t2.a as a from t2 group by t2.a) as t3
        on (t1.a = t3.a)
when matched then
        update set b = t1.b + 1
when not matched then
        insert (a,b) values (1,1);
postgres-*# postgres-*# postgres-*# postgres-*# postgres-*# postgres-*#
ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(1) already exists.