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

Commitd204ef6

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 RiggsReviewer: 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 parentaa5877b commitd204ef6

File tree

82 files changed

+2600
-165
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

82 files changed

+2600
-165
lines changed

‎contrib/test_decoding/expected/ddl.out‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -192,6 +192,52 @@ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'inc
192192
COMMIT
193193
(33 rows)
194194

195+
-- MERGE support
196+
BEGIN;
197+
MERGE INTO replication_example t
198+
USING (SELECT i as id, i as data, i as num FROM generate_series(-20, 5) i) s
199+
ON t.id = s.id
200+
WHEN MATCHED AND t.id < 0 THEN
201+
UPDATE SET somenum = somenum + 1
202+
WHEN MATCHED AND t.id >= 0 THEN
203+
DELETE
204+
WHEN NOT MATCHED THEN
205+
INSERT VALUES (s.*);
206+
COMMIT;
207+
/* display results */
208+
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
209+
data
210+
--------------------------------------------------------------------------------------------------------------------------------------------------
211+
BEGIN
212+
table public.replication_example: INSERT: id[integer]:-20 somedata[integer]:-20 somenum[integer]:-20 zaphod1[integer]:null zaphod2[integer]:null
213+
table public.replication_example: INSERT: id[integer]:-19 somedata[integer]:-19 somenum[integer]:-19 zaphod1[integer]:null zaphod2[integer]:null
214+
table public.replication_example: INSERT: id[integer]:-18 somedata[integer]:-18 somenum[integer]:-18 zaphod1[integer]:null zaphod2[integer]:null
215+
table public.replication_example: INSERT: id[integer]:-17 somedata[integer]:-17 somenum[integer]:-17 zaphod1[integer]:null zaphod2[integer]:null
216+
table public.replication_example: INSERT: id[integer]:-16 somedata[integer]:-16 somenum[integer]:-16 zaphod1[integer]:null zaphod2[integer]:null
217+
table public.replication_example: UPDATE: id[integer]:-15 somedata[integer]:-15 somenum[integer]:-14 zaphod1[integer]:null zaphod2[integer]:null
218+
table public.replication_example: UPDATE: id[integer]:-14 somedata[integer]:-14 somenum[integer]:-13 zaphod1[integer]:null zaphod2[integer]:null
219+
table public.replication_example: UPDATE: id[integer]:-13 somedata[integer]:-13 somenum[integer]:-12 zaphod1[integer]:null zaphod2[integer]:null
220+
table public.replication_example: UPDATE: id[integer]:-12 somedata[integer]:-12 somenum[integer]:-11 zaphod1[integer]:null zaphod2[integer]:null
221+
table public.replication_example: UPDATE: id[integer]:-11 somedata[integer]:-11 somenum[integer]:-10 zaphod1[integer]:null zaphod2[integer]:null
222+
table public.replication_example: UPDATE: id[integer]:-10 somedata[integer]:-10 somenum[integer]:-9 zaphod1[integer]:null zaphod2[integer]:null
223+
table public.replication_example: UPDATE: id[integer]:-9 somedata[integer]:-9 somenum[integer]:-8 zaphod1[integer]:null zaphod2[integer]:null
224+
table public.replication_example: UPDATE: id[integer]:-8 somedata[integer]:-8 somenum[integer]:-7 zaphod1[integer]:null zaphod2[integer]:null
225+
table public.replication_example: UPDATE: id[integer]:-7 somedata[integer]:-7 somenum[integer]:-6 zaphod1[integer]:null zaphod2[integer]:null
226+
table public.replication_example: UPDATE: id[integer]:-6 somedata[integer]:-6 somenum[integer]:-5 zaphod1[integer]:null zaphod2[integer]:null
227+
table public.replication_example: UPDATE: id[integer]:-5 somedata[integer]:-5 somenum[integer]:-4 zaphod1[integer]:null zaphod2[integer]:null
228+
table public.replication_example: UPDATE: id[integer]:-4 somedata[integer]:-4 somenum[integer]:-3 zaphod1[integer]:null zaphod2[integer]:null
229+
table public.replication_example: UPDATE: id[integer]:-3 somedata[integer]:-3 somenum[integer]:-2 zaphod1[integer]:null zaphod2[integer]:null
230+
table public.replication_example: UPDATE: id[integer]:-2 somedata[integer]:-2 somenum[integer]:-1 zaphod1[integer]:null zaphod2[integer]:null
231+
table public.replication_example: UPDATE: id[integer]:-1 somedata[integer]:-1 somenum[integer]:0 zaphod1[integer]:null zaphod2[integer]:null
232+
table public.replication_example: DELETE: id[integer]:0
233+
table public.replication_example: DELETE: id[integer]:1
234+
table public.replication_example: DELETE: id[integer]:2
235+
table public.replication_example: DELETE: id[integer]:3
236+
table public.replication_example: DELETE: id[integer]:4
237+
table public.replication_example: DELETE: id[integer]:5
238+
COMMIT
239+
(28 rows)
240+
195241
CREATE TABLE tr_unique(id2 serial unique NOT NULL, data int);
196242
INSERT INTO tr_unique(data) VALUES(10);
197243
ALTER TABLE tr_unique RENAME TO tr_pkey;

‎contrib/test_decoding/sql/ddl.sql‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,22 @@ COMMIT;
9393
/* display results*/
9494
SELECT dataFROM pg_logical_slot_get_changes('regression_slot',NULL,NULL,'include-xids','0','skip-empty-xacts','1');
9595

96+
-- MERGE support
97+
BEGIN;
98+
MERGE INTO replication_example t
99+
USING (SELECT ias id, ias data, ias numFROM generate_series(-20,5) i) s
100+
ONt.id=s.id
101+
WHEN MATCHEDANDt.id<0 THEN
102+
UPDATESET somenum= somenum+1
103+
WHEN MATCHEDANDt.id>=0 THEN
104+
DELETE
105+
WHEN NOT MATCHED THEN
106+
INSERTVALUES (s.*);
107+
COMMIT;
108+
109+
/* display results*/
110+
SELECT dataFROM pg_logical_slot_get_changes('regression_slot',NULL,NULL,'include-xids','0','skip-empty-xacts','1');
111+
96112
CREATETABLEtr_unique(id2serial uniqueNOT NULL, dataint);
97113
INSERT INTO tr_unique(data)VALUES(10);
98114
ALTERTABLE tr_unique RENAME TO tr_pkey;

‎doc/src/sgml/libpq.sgml‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3917,9 +3917,11 @@ char *PQcmdTuples(PGresult *res);
39173917
<structname>PGresult</structname>. This function can only be used following
39183918
the execution of a <command>SELECT</command>, <command>CREATE TABLE AS</command>,
39193919
<command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
3920-
<command>MOVE</command>, <command>FETCH</command>, or <command>COPY</command> statement,
3921-
or an <command>EXECUTE</command> of a prepared query that contains an
3922-
<command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command> statement.
3920+
<command>MERGE</command>, <command>MOVE</command>, <command>FETCH</command>,
3921+
or <command>COPY</command> statement, or an <command>EXECUTE</command> of a
3922+
prepared query that contains an <command>INSERT</command>,
3923+
<command>UPDATE</command>, <command>DELETE</command>
3924+
or <command>MERGE</command> statement.
39233925
If the command that generated the <structname>PGresult</structname> was anything
39243926
else, <function>PQcmdTuples</function> returns an empty string. The caller
39253927
should not free the return value directly. It will be freed when

‎doc/src/sgml/mvcc.sgml‎

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -422,6 +422,31 @@ COMMIT;
422422
<literal>11</literal>, which no longer matches the criteria.
423423
</para>
424424

425+
<para>
426+
The <command>MERGE</command> allows the user to specify various combinations
427+
of <command>INSERT</command>, <command>UPDATE</command> or
428+
<command>DELETE</command> subcommands. A <command>MERGE</command> command
429+
with both <command>INSERT</command> and <command>UPDATE</command>
430+
subcommands looks similar to <command>INSERT</command> with an
431+
<literal>ON CONFLICT DO UPDATE</literal> clause but does not guarantee
432+
that either <command>INSERT</command> and <command>UPDATE</command> will occur.
433+
434+
If MERGE attempts an UPDATE or DELETE and the row is concurrently updated
435+
but the join condition still passes for the current target and the current
436+
source tuple, then MERGE will behave the same as the UPDATE or DELETE commands
437+
and perform its action on the latest version of the row, using standard
438+
EvalPlanQual. MERGE actions can be conditional, so conditions must be
439+
re-evaluated on the latest row, starting from the first action.
440+
441+
On the other hand, if the row is concurrently updated or deleted so that
442+
the join condition fails, then MERGE will execute a NOT MATCHED action, if it
443+
exists and the AND WHEN qual evaluates to true.
444+
445+
If MERGE attempts an INSERT and a unique index is present and a duplicate
446+
row is concurrently inserted then a uniqueness violation is raised. MERGE
447+
does not attempt to avoid the ERROR by attempting an UPDATE.
448+
</para>
449+
425450
<para>
426451
Because Read Committed mode starts each command with a new snapshot
427452
that includes all transactions committed up to that instant,
@@ -900,7 +925,8 @@ ERROR: could not serialize access due to read/write dependencies among transact
900925

901926
<para>
902927
The commands <command>UPDATE</command>,
903-
<command>DELETE</command>, and <command>INSERT</command>
928+
<command>DELETE</command>, <command>INSERT</command> and
929+
<command>MERGE</command>
904930
acquire this lock mode on the target table (in addition to
905931
<literal>ACCESS SHARE</literal> locks on any other referenced
906932
tables). In general, this lock mode will be acquired by any

‎doc/src/sgml/plpgsql.sgml‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1246,7 +1246,7 @@ EXECUTE format('SELECT count(*) FROM %I '
12461246
</programlisting>
12471247
Another restriction on parameter symbols is that they only work in
12481248
<command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, and
1249-
<command>DELETE</command> commands. In other statement
1249+
<command>DELETE</command>and <command>MERGE</command>commands. In other statement
12501250
types (generically called utility statements), you must insert
12511251
values textually even if they are just data values.
12521252
</para>
@@ -1529,6 +1529,7 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
15291529
<listitem>
15301530
<para>
15311531
<command>UPDATE</command>, <command>INSERT</command>, and <command>DELETE</command>
1532+
and <command>MERGE</command>
15321533
statements set <literal>FOUND</literal> true if at least one
15331534
row is affected, false if no row is affected.
15341535
</para>

‎doc/src/sgml/ref/allfiles.sgml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,7 @@ Complete list of usable sgml source files in this directory.
159159
<!ENTITY load SYSTEM "load.sgml">
160160
<!ENTITY lock SYSTEM "lock.sgml">
161161
<!ENTITY move SYSTEM "move.sgml">
162+
<!ENTITY merge SYSTEM "merge.sgml">
162163
<!ENTITY notify SYSTEM "notify.sgml">
163164
<!ENTITY prepare SYSTEM "prepare.sgml">
164165
<!ENTITY prepareTransaction SYSTEM "prepare_transaction.sgml">

‎doc/src/sgml/ref/create_policy.sgml‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,6 +94,13 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
9494
exist, a <quote>default deny</quote> policy is assumed, so that no rows will
9595
be visible or updatable.
9696
</para>
97+
98+
<para>
99+
No separate policy exists for <command>MERGE</command>. Instead policies
100+
defined for <literal>SELECT</literal>, <literal>INSERT</literal>,
101+
<literal>UPDATE</literal> and <literal>DELETE</literal> are applied
102+
while executing MERGE, depending on the actions that are activated.
103+
</para>
97104
</refsect1>
98105

99106
<refsect1>

‎doc/src/sgml/ref/insert.sgml‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -579,6 +579,13 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
579579
is a partition, an error will occur if one of the input rows violates
580580
the partition constraint.
581581
</para>
582+
583+
<para>
584+
You may also wish to consider using <command>MERGE</command>, since that
585+
allows mixed <command>INSERT</command>, <command>UPDATE</command> and
586+
<command>DELETE</command> within a single statement.
587+
See <xref linkend="sql-merge"/>.
588+
</para>
582589
</refsect1>
583590

584591
<refsect1>
@@ -749,7 +756,9 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
749756
Also, the case in
750757
which a column name list is omitted, but not all the columns are
751758
filled from the <literal>VALUES</literal> clause or <replaceable>query</replaceable>,
752-
is disallowed by the standard.
759+
is disallowed by the standard. If you prefer a more SQL Standard
760+
conforming statement than <literal>ON CONFLICT</literal>, see
761+
<xref linkend="sql-merge"/>.
753762
</para>
754763

755764
<para>

‎doc/src/sgml/reference.sgml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -186,6 +186,7 @@
186186
&listen;
187187
&load;
188188
&lock;
189+
&merge;
189190
&move;
190191
&notify;
191192
&prepare;

‎doc/src/sgml/trigger.sgml‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -182,6 +182,26 @@
182182
will be fired.
183183
</para>
184184

185+
<para>
186+
No separate triggers are defined for <command>MERGE</command>. Instead,
187+
statement-level or row-level <command>UPDATE</command>,
188+
<command>DELETE</command> and <command>INSERT</command> triggers are fired
189+
depending on what actions are specified in the <command>MERGE</command> query
190+
and what actions are activated.
191+
</para>
192+
193+
<para>
194+
While running a <command>MERGE</command> command, statement-level
195+
<literal>BEFORE</literal> and <literal>AFTER</literal> triggers are fired for
196+
events specified in the actions of the <command>MERGE</command> command,
197+
irrespective of whether the action is finally activated or not. This is same as
198+
an <command>UPDATE</command> statement that updates no rows, yet
199+
statement-level triggers are fired. The row-level triggers are fired only
200+
when a row is actually updated, inserted or deleted. So it's perfectly legal
201+
that while statement-level triggers are fired for certain type of action, no
202+
row-level triggers are fired for the same kind of action.
203+
</para>
204+
185205
<para>
186206
Trigger functions invoked by per-statement triggers should always
187207
return <symbol>NULL</symbol>. Trigger functions invoked by per-row

0 commit comments

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