2.2.109 MERGEコマンドの並列実行時の出力結果変更

非互換

READ COMMITTEDのトランザクションでMERGEコマンドを並列実行すると、出力結果が変更される場合があります。

Fujitsu Enterprise Postgres 15以前の場合

session 1

postgres=# CREATE TABLE t1(a int not null, b int);
CREATE TABLE
postgres=# CREATE TABLE t2(a int not null, b int);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(generate_series(1,3), generate_series(1,3));
INSERT 0 3
postgres=# INSERT INTO t2 VALUES(generate_series(1,3), generate_series(1,3));
INSERT 0 3
postgres=# BEGIN;
BEGIN

session 2

postgres=# BEGIN;
BEGIN
postgres=*# set enable_hashjoin = off;
SET
postgres=*# set enable_mergejoin = off;
SET
postgres=*# MERGE INTO t1 p USING (select distinct a,b from t2) q on p.a = q.a WHEN
postgres-*# MATCHED THEN UPDATE SET b = q.b WHEN NOT MATCHED THEN 
postgres(*# INSERT VALUES(q.a,q.b);
MERGE 3

session 1

postgres=*# set enable_hashjoin = off;
SET
postgres=*# set enable_mergejoin = off;
SET
postgres=*# MERGE INTO t1 p USING (select distinct a,b from t2) q on p.a = q.a WHEN
postgres-*# MATCHED THEN UPDATE SET b = q.b WHEN NOT MATCHED THEN 
postgres(*# INSERT VALUES(q.a,q.b);

session 2
```
postgres=*# COMMIT;
```

session 1

postgres=*# SELECT COUNT(*) FROM t1;
 count
-------
     5
(1 row)

Fujitsu Enterprise Postgres 16の場合

session 1

postgres=# CREATE TABLE t1(a int not null, b int);
CREATE TABLE
postgres=# CREATE TABLE t2(a int not null, b int);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(generate_series(1,3), generate_series(1,3));
INSERT 0 3
postgres=# INSERT INTO t2 VALUES(generate_series(1,3), generate_series(1,3));
INSERT 0 3
postgres=# BEGIN;
BEGIN

session 2

postgres=# BEGIN;
BEGIN
postgres=*# set enable_hashjoin = off;
SET
postgres=*# set enable_mergejoin = off;
SET
postgres=*# MERGE INTO t1 p USING (select distinct a,b from t2) q on p.a = q.a WHEN
postgres-*# MATCHED THEN UPDATE SET b = q.b WHEN NOT MATCHED THEN 
postgres(*# INSERT VALUES(q.a,q.b);
MERGE 3

session 1

postgres=*# set enable_hashjoin = off;
SET
postgres=*# set enable_mergejoin = off;
SET
postgres=*# MERGE INTO t1 p USING (select distinct a,b from t2) q on p.a = q.a WHEN
postgres-*# MATCHED THEN UPDATE SET b = q.b WHEN NOT MATCHED THEN 
postgres(*# INSERT VALUES(q.a,q.b);

session 2
```
postgres=*# COMMIT;
```

session 1

postgres=*# SELECT COUNT(*) FROM t1;
 count
-------
     3
(1 row)

対処方法

ありません。