Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite6597dc

Browse files
MERGE SQL Command following SQL:2016
MERGE performs actions that modify rows in the target tableusing a source table or query. MERGE provides a single SQLstatement that can conditionally INSERT/UPDATE/DELETE rowsa task that would other require multiple PL statements.e.g.MERGE INTO target AS tUSING source AS sON t.tid = s.sidWHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.deltaWHEN MATCHED THEN DELETEWHEN NOT MATCHED AND s.delta > 0 THEN INSERT VALUES (s.sid, s.delta)WHEN NOT MATCHED THEN DO NOTHING;MERGE works with regular and partitioned tables, includingcolumn and row security enforcement, as well as support forrow, statement and transition triggers.MERGE is optimized for OLTP and is parameterizable, thoughalso useful for large scale ETL/ELT. MERGE is not intendedto be used in preference to existing single SQL commandsfor INSERT, UPDATE or DELETE since there is some overhead.MERGE can be used statically from PL/pgSQL.MERGE does not yet support inheritance, write rules,RETURNING clauses, updatable views or foreign tables.MERGE follows SQL Standard per the most recent SQL:2016.Includes full tests and documentation, including fullisolation tests to demonstrate the concurrent behavior.This version written from scratch in 2017 by Simon Riggs,using docs and tests originally written in 2009. Later workfrom Pavan Deolasee has been both complex and deep, leavingthe lead author credit now in his hands.Extensive discussion of concurrency from Peter Geoghegan,with thanks for the time and effort contributed.Various issues reported via sqlsmith by Andreas SeltenreichAuthors: Pavan Deolasee, Simon RiggsReviewers: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon RiggsDiscussion:https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.comhttps://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com
1 parenta92f24f commite6597dc

File tree

15 files changed

+5677
-0
lines changed

15 files changed

+5677
-0
lines changed

‎doc/src/sgml/ref/merge.sgml

Lines changed: 603 additions & 0 deletions
Large diffs are not rendered by default.

‎src/backend/executor/nodeMerge.c

Lines changed: 575 additions & 0 deletions
Large diffs are not rendered by default.

‎src/backend/parser/parse_merge.c

Lines changed: 660 additions & 0 deletions
Large diffs are not rendered by default.

‎src/include/executor/nodeMerge.h

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* nodeMerge.h
4+
*
5+
*
6+
* Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
7+
* Portions Copyright (c) 1994, Regents of the University of California
8+
*
9+
* src/include/executor/nodeMerge.h
10+
*
11+
*-------------------------------------------------------------------------
12+
*/
13+
#ifndefNODEMERGE_H
14+
#defineNODEMERGE_H
15+
16+
#include"nodes/execnodes.h"
17+
18+
externvoid
19+
ExecMerge(ModifyTableState*mtstate,EState*estate,TupleTableSlot*slot,
20+
JunkFilter*junkfilter,ResultRelInfo*resultRelInfo);
21+
22+
#endif/* NODEMERGE_H */

‎src/include/parser/parse_merge.h

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* parse_merge.h
4+
* handle merge-stmt in parser
5+
*
6+
*
7+
* Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
8+
* Portions Copyright (c) 1994, Regents of the University of California
9+
*
10+
* src/include/parser/parse_merge.h
11+
*
12+
*-------------------------------------------------------------------------
13+
*/
14+
#ifndefPARSE_MERGE_H
15+
#definePARSE_MERGE_H
16+
17+
#include"parser/parse_node.h"
18+
externQuery*transformMergeStmt(ParseState*pstate,MergeStmt*stmt);
19+
#endif
Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: delete c1 select2 c2
4+
step delete: DELETE FROM target t WHERE t.key = 1;
5+
step c1: COMMIT;
6+
step select2: SELECT * FROM target;
7+
key val
8+
9+
step c2: COMMIT;
10+
11+
starting permutation: merge_delete c1 select2 c2
12+
step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
13+
step c1: COMMIT;
14+
step select2: SELECT * FROM target;
15+
key val
16+
17+
step c2: COMMIT;
18+
19+
starting permutation: delete c1 update1 select2 c2
20+
step delete: DELETE FROM target t WHERE t.key = 1;
21+
step c1: COMMIT;
22+
step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
23+
step select2: SELECT * FROM target;
24+
key val
25+
26+
step c2: COMMIT;
27+
28+
starting permutation: merge_delete c1 update1 select2 c2
29+
step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
30+
step c1: COMMIT;
31+
step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
32+
step select2: SELECT * FROM target;
33+
key val
34+
35+
step c2: COMMIT;
36+
37+
starting permutation: delete c1 merge2 select2 c2
38+
step delete: DELETE FROM target t WHERE t.key = 1;
39+
step c1: COMMIT;
40+
step merge2: 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;
41+
step select2: SELECT * FROM target;
42+
key val
43+
44+
1 merge2a
45+
step c2: COMMIT;
46+
47+
starting permutation: merge_delete c1 merge2 select2 c2
48+
step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
49+
step c1: COMMIT;
50+
step merge2: 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;
51+
step select2: SELECT * FROM target;
52+
key val
53+
54+
1 merge2a
55+
step c2: COMMIT;
56+
57+
starting permutation: delete update1 c1 select2 c2
58+
step delete: DELETE FROM target t WHERE t.key = 1;
59+
step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
60+
step c1: COMMIT;
61+
step update1: <... completed>
62+
step select2: SELECT * FROM target;
63+
key val
64+
65+
step c2: COMMIT;
66+
67+
starting permutation: merge_delete update1 c1 select2 c2
68+
step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
69+
step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
70+
step c1: COMMIT;
71+
step update1: <... completed>
72+
step select2: SELECT * FROM target;
73+
key val
74+
75+
step c2: COMMIT;
76+
77+
starting permutation: delete merge2 c1 select2 c2
78+
step delete: DELETE FROM target t WHERE t.key = 1;
79+
step merge2: 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; <waiting ...>
80+
step c1: COMMIT;
81+
step merge2: <... completed>
82+
step select2: SELECT * FROM target;
83+
key val
84+
85+
1 merge2a
86+
step c2: COMMIT;
87+
88+
starting permutation: merge_delete merge2 c1 select2 c2
89+
step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
90+
step merge2: 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; <waiting ...>
91+
step c1: COMMIT;
92+
step merge2: <... completed>
93+
step select2: SELECT * FROM target;
94+
key val
95+
96+
1 merge2a
97+
step c2: COMMIT;
Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,84 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: merge1 c1 select2 c2
4+
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 val = t.val || ' updated by merge1';
5+
step c1: COMMIT;
6+
step select2: SELECT * FROM target;
7+
key val
8+
9+
1 merge1
10+
step c2: COMMIT;
11+
12+
starting permutation: merge1 c1 merge2 select2 c2
13+
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 val = t.val || ' updated by merge1';
14+
step c1: COMMIT;
15+
step merge2: MERGE INTO target t USING (SELECT 1 as key, '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 = t.val || ' updated by merge2';
16+
step select2: SELECT * FROM target;
17+
key val
18+
19+
1 merge1 updated by merge2
20+
step c2: COMMIT;
21+
22+
starting permutation: insert1 merge2 c1 select2 c2
23+
step insert1: INSERT INTO target VALUES (1, 'insert1');
24+
step merge2: MERGE INTO target t USING (SELECT 1 as key, '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 = t.val || ' updated by merge2'; <waiting ...>
25+
step c1: COMMIT;
26+
step merge2: <... completed>
27+
error in steps c1 merge2: ERROR: duplicate key value violates unique constraint "target_pkey"
28+
step select2: SELECT * FROM target;
29+
ERROR: current transaction is aborted, commands ignored until end of transaction block
30+
step c2: COMMIT;
31+
32+
starting permutation: merge1 merge2 c1 select2 c2
33+
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 val = t.val || ' updated by merge1';
34+
step merge2: MERGE INTO target t USING (SELECT 1 as key, '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 = t.val || ' updated by merge2'; <waiting ...>
35+
step c1: COMMIT;
36+
step merge2: <... completed>
37+
error in steps c1 merge2: ERROR: duplicate key value violates unique constraint "target_pkey"
38+
step select2: SELECT * FROM target;
39+
ERROR: current transaction is aborted, commands ignored until end of transaction block
40+
step c2: COMMIT;
41+
42+
starting permutation: merge1 merge2 a1 select2 c2
43+
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 val = t.val || ' updated by merge1';
44+
step merge2: MERGE INTO target t USING (SELECT 1 as key, '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 = t.val || ' updated by merge2'; <waiting ...>
45+
step a1: ABORT;
46+
step merge2: <... completed>
47+
step select2: SELECT * FROM target;
48+
key val
49+
50+
1 merge2
51+
step c2: COMMIT;
52+
53+
starting permutation: delete1 insert1 c1 merge2 select2 c2
54+
step delete1: DELETE FROM target WHERE key = 1;
55+
step insert1: INSERT INTO target VALUES (1, 'insert1');
56+
step c1: COMMIT;
57+
step merge2: MERGE INTO target t USING (SELECT 1 as key, '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 = t.val || ' updated by merge2';
58+
step select2: SELECT * FROM target;
59+
key val
60+
61+
1 insert1 updated by merge2
62+
step c2: COMMIT;
63+
64+
starting permutation: delete1 insert1 merge2 c1 select2 c2
65+
step delete1: DELETE FROM target WHERE key = 1;
66+
step insert1: INSERT INTO target VALUES (1, 'insert1');
67+
step merge2: MERGE INTO target t USING (SELECT 1 as key, '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 = t.val || ' updated by merge2'; <waiting ...>
68+
step c1: COMMIT;
69+
step merge2: <... completed>
70+
error in steps c1 merge2: ERROR: duplicate key value violates unique constraint "target_pkey"
71+
step select2: SELECT * FROM target;
72+
ERROR: current transaction is aborted, commands ignored until end of transaction block
73+
step c2: COMMIT;
74+
75+
starting permutation: delete1 insert1 merge2i c1 select2 c2
76+
step delete1: DELETE FROM target WHERE key = 1;
77+
step insert1: INSERT INTO target VALUES (1, 'insert1');
78+
step merge2i: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2';
79+
step c1: COMMIT;
80+
step select2: SELECT * FROM target;
81+
key val
82+
83+
1 insert1
84+
step c2: COMMIT;
Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,106 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: update1 merge_status c2 select1 c1
4+
step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
5+
step merge_status:
6+
MERGE INTO target t
7+
USING (SELECT 1 as key) s
8+
ON s.key = t.key
9+
WHEN MATCHED AND status = 's1' THEN
10+
UPDATE SET status = 's2', val = t.val || ' when1'
11+
WHEN MATCHED AND status = 's2' THEN
12+
UPDATE SET status = 's3', val = t.val || ' when2'
13+
WHEN MATCHED AND status = 's3' THEN
14+
UPDATE SET status = 's4', val = t.val || ' when3';
15+
<waiting ...>
16+
step c2: COMMIT;
17+
step merge_status: <... completed>
18+
step select1: SELECT * FROM target;
19+
key balance status val
20+
21+
1 170 s2 setup updated by update1 when1
22+
step c1: COMMIT;
23+
24+
starting permutation: update2 merge_status c2 select1 c1
25+
step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
26+
step merge_status:
27+
MERGE INTO target t
28+
USING (SELECT 1 as key) s
29+
ON s.key = t.key
30+
WHEN MATCHED AND status = 's1' THEN
31+
UPDATE SET status = 's2', val = t.val || ' when1'
32+
WHEN MATCHED AND status = 's2' THEN
33+
UPDATE SET status = 's3', val = t.val || ' when2'
34+
WHEN MATCHED AND status = 's3' THEN
35+
UPDATE SET status = 's4', val = t.val || ' when3';
36+
<waiting ...>
37+
step c2: COMMIT;
38+
step merge_status: <... completed>
39+
step select1: SELECT * FROM target;
40+
key balance status val
41+
42+
1 160 s3 setup updated by update2 when2
43+
step c1: COMMIT;
44+
45+
starting permutation: update3 merge_status c2 select1 c1
46+
step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
47+
step merge_status:
48+
MERGE INTO target t
49+
USING (SELECT 1 as key) s
50+
ON s.key = t.key
51+
WHEN MATCHED AND status = 's1' THEN
52+
UPDATE SET status = 's2', val = t.val || ' when1'
53+
WHEN MATCHED AND status = 's2' THEN
54+
UPDATE SET status = 's3', val = t.val || ' when2'
55+
WHEN MATCHED AND status = 's3' THEN
56+
UPDATE SET status = 's4', val = t.val || ' when3';
57+
<waiting ...>
58+
step c2: COMMIT;
59+
step merge_status: <... completed>
60+
step select1: SELECT * FROM target;
61+
key balance status val
62+
63+
1 160 s4 setup updated by update3 when3
64+
step c1: COMMIT;
65+
66+
starting permutation: update5 merge_status c2 select1 c1
67+
step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
68+
step merge_status:
69+
MERGE INTO target t
70+
USING (SELECT 1 as key) s
71+
ON s.key = t.key
72+
WHEN MATCHED AND status = 's1' THEN
73+
UPDATE SET status = 's2', val = t.val || ' when1'
74+
WHEN MATCHED AND status = 's2' THEN
75+
UPDATE SET status = 's3', val = t.val || ' when2'
76+
WHEN MATCHED AND status = 's3' THEN
77+
UPDATE SET status = 's4', val = t.val || ' when3';
78+
<waiting ...>
79+
step c2: COMMIT;
80+
step merge_status: <... completed>
81+
step select1: SELECT * FROM target;
82+
key balance status val
83+
84+
1 160 s5 setup updated by update5
85+
step c1: COMMIT;
86+
87+
starting permutation: update_bal1 merge_bal c2 select1 c1
88+
step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
89+
step merge_bal:
90+
MERGE INTO target t
91+
USING (SELECT 1 as key) s
92+
ON s.key = t.key
93+
WHEN MATCHED AND balance < 100 THEN
94+
UPDATE SET balance = balance * 2, val = t.val || ' when1'
95+
WHEN MATCHED AND balance < 200 THEN
96+
UPDATE SET balance = balance * 4, val = t.val || ' when2'
97+
WHEN MATCHED AND balance < 300 THEN
98+
UPDATE SET balance = balance * 8, val = t.val || ' when3';
99+
<waiting ...>
100+
step c2: COMMIT;
101+
step merge_bal: <... completed>
102+
step select1: SELECT * FROM target;
103+
key balance status val
104+
105+
1 100 s1 setup updated by update_bal1 when1
106+
step c1: COMMIT;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp