src/test/isolation/expected/merge-update.out - master - githubmirror/postgres

src/test/isolation/expected/merge-update.out
584 строки21 KB
Alexander Korotkov
Fix handling of updated tuples in the MERGE statement
05 мар 2026, 20:49
05 мар 2026, 20:491770373
Parsed test spec with 2 sessions

starting permutation: merge1 c1 select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step c1: COMMIT;
step select2: SELECT * FROM target;
key|val                     
---+------------------------
  2|setup1 updated by merge1
(1 row)

step c2: COMMIT;

starting permutation: merge1 c1 merge2a select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step c1: COMMIT;
step merge2a: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
  RETURNING merge_action(), old, new, t.*;

merge_action|old                           |new                                                         |key|val                                                   
------------+------------------------------+------------------------------------------------------------+---+------------------------------------------------------
UPDATE      |(2,"setup1 updated by merge1")|(3,"setup1 updated by merge1 source not matched by merge2a")|  3|setup1 updated by merge1 source not matched by merge2a
INSERT      |                              |(1,merge2a)                                                 |  1|merge2a                                               
(2 rows)

step select2: SELECT * FROM target;
key|val                                                   
---+------------------------------------------------------
  3|setup1 updated by merge1 source not matched by merge2a
  1|merge2a                                               
(2 rows)

step c2: COMMIT;

starting permutation: merge1 c1 explain_merge2a select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step c1: COMMIT;
step explain_merge2a: 
  SELECT explain_filter($$
  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
  RETURNING merge_action(), old, new, t.*
  $$);

explain_filter                                             
-----------------------------------------------------------
Merge on target t (actual rows=2.00 loops=1)               
  Tuples: inserted=1 updated=1                             
  ->  Hash Full Join (actual rows=2.00 loops=1)            
        Hash Cond: (t.key = (1))                           
        ->  Seq Scan on target t (actual rows=1.00 loops=1)
        ->  Hash (actual rows=1.00 loops=1)                
              ->  Result (actual rows=1.00 loops=1)        
(7 rows)

step select2: SELECT * FROM target;
key|val                                                   
---+------------------------------------------------------
  3|setup1 updated by merge1 source not matched by merge2a
  1|merge2a                                               
(2 rows)

step c2: COMMIT;

starting permutation: pa_merge1 c1 pa_merge2c_dup a2
step pa_merge1: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set val = t.val || ' updated by ' || s.val;

step c1: COMMIT;
step pa_merge2c_dup: 
  MERGE INTO pa_target t
  USING (VALUES (1), (1)) v(a)
  ON t.key = v.a
  WHEN MATCHED THEN
	UPDATE set val = t.val || ' updated by pa_merge2c_dup';  -- should fail

ERROR:  MERGE command cannot affect row a second time
step a2: ABORT;

starting permutation: merge1 merge2a c1 select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step merge2a: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
  RETURNING merge_action(), old, new, t.*;
 <waiting ...>
step c1: COMMIT;
step merge2a: <... completed>
merge_action|old                           |new                                                         |key|val                                                   
------------+------------------------------+------------------------------------------------------------+---+------------------------------------------------------
UPDATE      |(2,"setup1 updated by merge1")|(3,"setup1 updated by merge1 source not matched by merge2a")|  3|setup1 updated by merge1 source not matched by merge2a
INSERT      |                              |(1,merge2a)                                                 |  1|merge2a                                               
(2 rows)

step select2: SELECT * FROM target;
key|val                                                   
---+------------------------------------------------------
  3|setup1 updated by merge1 source not matched by merge2a
  1|merge2a                                               
(2 rows)

step c2: COMMIT;

starting permutation: merge1 explain_merge2a c1 select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step explain_merge2a: 
  SELECT explain_filter($$
  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
  RETURNING merge_action(), old, new, t.*
  $$);
 <waiting ...>
step c1: COMMIT;
step explain_merge2a: <... completed>
explain_filter                                             
-----------------------------------------------------------
Merge on target t (actual rows=2.00 loops=1)               
  Tuples: inserted=1 updated=1                             
  ->  Hash Full Join (actual rows=2.00 loops=1)            
        Hash Cond: (t.key = (1))                           
        ->  Seq Scan on target t (actual rows=1.00 loops=1)
        ->  Hash (actual rows=1.00 loops=1)                
              ->  Result (actual rows=1.00 loops=1)        
(7 rows)

step select2: SELECT * FROM target;
key|val                                                   
---+------------------------------------------------------
  3|setup1 updated by merge1 source not matched by merge2a
  1|merge2a                                               
(2 rows)

step c2: COMMIT;

starting permutation: merge1 merge2a a1 select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step merge2a: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
  RETURNING merge_action(), old, new, t.*;
 <waiting ...>
step a1: ABORT;
step merge2a: <... completed>
merge_action|old       |new                            |key|val                      
------------+----------+-------------------------------+---+-------------------------
UPDATE      |(1,setup1)|(2,"setup1 updated by merge2a")|  2|setup1 updated by merge2a
(1 row)

step select2: SELECT * FROM target;
key|val                      
---+-------------------------
  2|setup1 updated by merge2a
(1 row)

step c2: COMMIT;

starting permutation: merge1 merge2b c1 select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step merge2b: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2b' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED AND t.key < 2 THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
 <waiting ...>
step c1: COMMIT;
step merge2b: <... completed>
step select2: SELECT * FROM target;
key|val                     
---+------------------------
  2|setup1 updated by merge1
  1|merge2b                 
(2 rows)

step c2: COMMIT;

starting permutation: merge1 merge2c c1 select2 c2
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step merge2c: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2c' as val) s
  ON s.key = t.key AND t.key < 2
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
 <waiting ...>
step c1: COMMIT;
step merge2c: <... completed>
step select2: SELECT * FROM target;
key|val                     
---+------------------------
  2|setup1 updated by merge1
  1|merge2c                 
(2 rows)

step c2: COMMIT;

starting permutation: pa_merge1 pa_merge2a c1 pa_select2 c2
step pa_merge1: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set val = t.val || ' updated by ' || s.val;

step pa_merge2a: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
  RETURNING merge_action(), old, new, t.*;
 <waiting ...>
step c1: COMMIT;
step pa_merge2a: <... completed>
merge_action|old                               |new                                                     |key|val                                               
------------+----------------------------------+--------------------------------------------------------+---+--------------------------------------------------
UPDATE      |(1,"initial updated by pa_merge1")|(2,"initial updated by pa_merge1 updated by pa_merge2a")|  2|initial updated by pa_merge1 updated by pa_merge2a
UPDATE      |(2,initial)                       |(3,"initial source not matched by pa_merge2a")          |  3|initial source not matched by pa_merge2a          
(2 rows)

step pa_select2: SELECT * FROM pa_target;
key|val                                               
---+--------------------------------------------------
  2|initial updated by pa_merge1 updated by pa_merge2a
  3|initial source not matched by pa_merge2a          
(2 rows)

step c2: COMMIT;

starting permutation: pa_merge1 explain_pa_merge2a c1 pa_select2 c2
step pa_merge1: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set val = t.val || ' updated by ' || s.val;

step explain_pa_merge2a: 
  SELECT explain_filter($$
  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
  RETURNING merge_action(), old, new, t.*
  $$);
 <waiting ...>
step c1: COMMIT;
step explain_pa_merge2a: <... completed>
explain_filter                                                    
------------------------------------------------------------------
Merge on pa_target t (actual rows=2.00 loops=1)                   
  Merge on part1 t_1                                              
  Merge on part2 t_2                                              
  Merge on part3 t_3                                              
  Tuples: updated=2                                               
  ->  Hash Full Join (actual rows=2.00 loops=1)                   
        Hash Cond: (t.key = (1))                                  
        ->  Append (actual rows=2.00 loops=1)                     
              ->  Seq Scan on part1 t_1 (actual rows=1.00 loops=1)
              ->  Seq Scan on part2 t_2 (actual rows=1.00 loops=1)
              ->  Seq Scan on part3 t_3 (actual rows=0.00 loops=1)
        ->  Hash (actual rows=1.00 loops=1)                       
              ->  Result (actual rows=1.00 loops=1)               
(13 rows)

step pa_select2: SELECT * FROM pa_target;
key|val                                               
---+--------------------------------------------------
  2|initial updated by pa_merge1 updated by pa_merge2a
  3|initial source not matched by pa_merge2a          
(2 rows)

step c2: COMMIT;

starting permutation: pa_merge2 pa_merge2a c1 pa_select2 c2
step pa_merge2: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step pa_merge2a: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
  RETURNING merge_action(), old, new, t.*;
 <waiting ...>
step c1: COMMIT;
step pa_merge2a: <... completed>
ERROR:  tuple to be locked was already moved to another partition due to concurrent update
step pa_select2: SELECT * FROM pa_target;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
step c2: COMMIT;

starting permutation: pa_merge2 c1 pa_merge2a pa_select2 c2
step pa_merge2: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;

step c1: COMMIT;
step pa_merge2a: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
  RETURNING merge_action(), old, new, t.*;

merge_action|old                               |new                                                                |key|val                                                          
------------+----------------------------------+-------------------------------------------------------------------+---+-------------------------------------------------------------
UPDATE      |(2,initial)                       |(3,"initial source not matched by pa_merge2a")                     |  3|initial source not matched by pa_merge2a                     
UPDATE      |(2,"initial updated by pa_merge2")|(3,"initial updated by pa_merge2 source not matched by pa_merge2a")|  3|initial updated by pa_merge2 source not matched by pa_merge2a
INSERT      |                                  |(1,pa_merge2a)                                                     |  1|pa_merge2a                                                   
(3 rows)

step pa_select2: SELECT * FROM pa_target;
key|val                                                          
---+-------------------------------------------------------------
  1|pa_merge2a                                                   
  3|initial source not matched by pa_merge2a                     
  3|initial updated by pa_merge2 source not matched by pa_merge2a
(3 rows)

step c2: COMMIT;

starting permutation: pa_merge3 pa_merge2b_when c1 pa_select2 c2
step pa_merge3: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set val = 'prefix ' || t.val;

step pa_merge2b_when: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED AND t.val like 'initial%' THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
 <waiting ...>
step c1: COMMIT;
step pa_merge2b_when: <... completed>
step pa_select2: SELECT * FROM pa_target;
key|val           
---+--------------
  1|prefix initial
  2|initial       
(2 rows)

step c2: COMMIT;

starting permutation: pa_merge1 pa_merge2b_when c1 pa_select2 c2
step pa_merge1: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set val = t.val || ' updated by ' || s.val;

step pa_merge2b_when: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED AND t.val like 'initial%' THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
 <waiting ...>
step c1: COMMIT;
step pa_merge2b_when: <... completed>
step pa_select2: SELECT * FROM pa_target;
key|val                                                    
---+-------------------------------------------------------
  2|initial                                                
  2|initial updated by pa_merge1 updated by pa_merge2b_when
(2 rows)

step c2: COMMIT;

starting permutation: pa_merge1 pa_merge2c_dup c1 a2
step pa_merge1: 
  MERGE INTO pa_target t
  USING (SELECT 1 as key, 'pa_merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set val = t.val || ' updated by ' || s.val;

step pa_merge2c_dup: 
  MERGE INTO pa_target t
  USING (VALUES (1), (1)) v(a)
  ON t.key = v.a
  WHEN MATCHED THEN
	UPDATE set val = t.val || ' updated by pa_merge2c_dup';  -- should fail
 <waiting ...>
step c1: COMMIT;
step pa_merge2c_dup: <... completed>
ERROR:  MERGE command cannot affect row a second time
step a2: ABORT;

starting permutation: merge2a c1 s1beginrr merge1 c2
step merge2a: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge2a' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
  WHEN NOT MATCHED BY SOURCE THEN
	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
  RETURNING merge_action(), old, new, t.*;

merge_action|old       |new                            |key|val                      
------------+----------+-------------------------------+---+-------------------------
UPDATE      |(1,setup1)|(2,"setup1 updated by merge2a")|  2|setup1 updated by merge2a
(1 row)

step c1: COMMIT;
step s1beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
step merge1: 
  MERGE INTO target t
  USING (SELECT 1 as key, 'merge1' as val) s
  ON s.key = t.key
  WHEN NOT MATCHED THEN
	INSERT VALUES (s.key, s.val)
  WHEN MATCHED THEN
    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
 <waiting ...>
step c2: COMMIT;
step merge1: <... completed>
ERROR:  could not serialize access due to concurrent update