From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: luajk@qq.com
Subject: BUG #18103: bugs of concurrent merge into when use different join plan
Date: Sun, 10 Sep 2023 12:57:14 +0000
Message-ID: <18103-c4386baab8e355e3@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18103
Logged by: luajk
Email address: luajk@qq.com
PostgreSQL version: 16rc1
Operating system: ANY
Description:
Use Case:
drop table t1;
drop table t2;
create table t1(a int not null, b int);
create table t2(a int not null, b int);
insert into t1 values(generate_series(1,3), generate_series(1,3));
insert into t2 values(generate_series(1,3), generate_series(1,3));
execute in two concurrent sessions and do as following:
session1:
begin;
session2:
begin;
set enable_hashjoin = off;
set enable_mergejoin = off;
merge into t1 p using (select distinct a,b from t2) q on p.a = q.a when
matched then update set b = q.b when not matched then insert values(q.a,
q.b);
session1:
set enable_hashjoin = off;
set enable_mergejoin = off;
merge into t1 p using (select distinct a,b from t2) q on p.a = q.a when
matched then update set b = q.b when not matched then insert values(q.a,
q.b);
session2:
commit;
session1:
select count(*) from t1; -- there are 5 rows;
however:
session1:
begin;
session2:
begin;
set enable_nestloop = off;
set enable_mergejoin = off;
merge into t1 p using (select distinct a,b from t2) q on p.a = q.a when
matched then update set b = q.b when not matched then insert values(q.a,
q.b);
session1:
set enable_nestloop = off;
set enable_mergejoin = off;
merge into t1 p using (select distinct a,b from t2) q on p.a = q.a when
matched then update set b = q.b when not matched then insert values(q.a,
q.b);
session2:
commit;
session1:
select count(*) from t1; -- there are 3 rows;
nest loop join is the same as merge join in such scenes.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, luajk@qq.com
Subject: Re: BUG #18103: bugs of concurrent merge into when use different join plan
In-Reply-To: <18103-c4386baab8e355e3@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox