From d204ef63776b8a00ca220adec23979091564e465 Mon Sep 17 00:00:00 2001 From: Simon Riggs Date: Tue, 3 Apr 2018 09:28:16 +0100 Subject: MERGE SQL Command following SQL:2016 MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows a task that would other require multiple PL statements. e.g. MERGE INTO target AS t USING source AS s ON t.tid = s.sid WHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.delta WHEN MATCHED THEN DELETE WHEN 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, including column and row security enforcement, as well as support for row, statement and transition triggers. MERGE is optimized for OLTP and is parameterizable, though also useful for large scale ETL/ELT. MERGE is not intended to be used in preference to existing single SQL commands for 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 full isolation 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 work from Pavan Deolasee has been both complex and deep, leaving the 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 Seltenreich Authors: Pavan Deolasee, Simon Riggs Reviewer: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon Riggs Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com --- doc/src/sgml/libpq.sgml | 8 +++++--- doc/src/sgml/mvcc.sgml | 28 +++++++++++++++++++++++++++- doc/src/sgml/plpgsql.sgml | 3 ++- doc/src/sgml/ref/allfiles.sgml | 1 + doc/src/sgml/ref/create_policy.sgml | 7 +++++++ doc/src/sgml/ref/insert.sgml | 11 ++++++++++- doc/src/sgml/reference.sgml | 1 + doc/src/sgml/trigger.sgml | 20 ++++++++++++++++++++ 8 files changed, 73 insertions(+), 6 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 943adfef77..8729ccd5c5 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -3917,9 +3917,11 @@ char *PQcmdTuples(PGresult *res); PGresult. This function can only be used following the execution of a SELECT, CREATE TABLE AS, INSERT, UPDATE, DELETE, - MOVE, FETCH, or COPY statement, - or an EXECUTE of a prepared query that contains an - INSERT, UPDATE, or DELETE statement. + MERGE, MOVE, FETCH, + or COPY statement, or an EXECUTE of a + prepared query that contains an INSERT, + UPDATE, DELETE + or MERGE statement. If the command that generated the PGresult was anything else, PQcmdTuples returns an empty string. The caller should not free the return value directly. It will be freed when diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index 24613e3c75..0e3e89af56 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -422,6 +422,31 @@ COMMIT; 11, which no longer matches the criteria. + + The MERGE allows the user to specify various combinations + of INSERT, UPDATE or + DELETE subcommands. A MERGE command + with both INSERT and UPDATE + subcommands looks similar to INSERT with an + ON CONFLICT DO UPDATE clause but does not guarantee + that either INSERT and UPDATE will occur. + + If MERGE attempts an UPDATE or DELETE and the row is concurrently updated + but the join condition still passes for the current target and the current + source tuple, then MERGE will behave the same as the UPDATE or DELETE commands + and perform its action on the latest version of the row, using standard + EvalPlanQual. MERGE actions can be conditional, so conditions must be + re-evaluated on the latest row, starting from the first action. + + On the other hand, if the row is concurrently updated or deleted so that + the join condition fails, then MERGE will execute a NOT MATCHED action, if it + exists and the AND WHEN qual evaluates to true. + + If MERGE attempts an INSERT and a unique index is present and a duplicate + row is concurrently inserted then a uniqueness violation is raised. MERGE + does not attempt to avoid the ERROR by attempting an UPDATE. + + Because Read Committed mode starts each command with a new snapshot 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 The commands UPDATE, - DELETE, and INSERT + DELETE, INSERT and + MERGE acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 5b2aac618e..59f6112b07 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1246,7 +1246,7 @@ EXECUTE format('SELECT count(*) FROM %I ' Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and - DELETE commands. In other statement + DELETE and MERGE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values. @@ -1529,6 +1529,7 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; UPDATE, INSERT, and DELETE + and MERGE statements set FOUND true if at least one row is affected, false if no row is affected. diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 22e6893211..4e01e5641c 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -159,6 +159,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 0e35b0ef43..32f39a48ba 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -94,6 +94,13 @@ CREATE POLICY name ON default deny policy is assumed, so that no rows will be visible or updatable. + + + No separate policy exists for MERGE. Instead policies + defined for SELECT, INSERT, + UPDATE and DELETE are applied + while executing MERGE, depending on the actions that are activated. + diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 62e142fd8e..da294aaa46 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -579,6 +579,13 @@ INSERT oid count + + + You may also wish to consider using MERGE, since that + allows mixed INSERT, UPDATE and + DELETE within a single statement. + See . + @@ -749,7 +756,9 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, - is disallowed by the standard. + is disallowed by the standard. If you prefer a more SQL Standard + conforming statement than ON CONFLICT, see + . diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index d27fb414f7..ef2270c467 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -186,6 +186,7 @@ &listen; &load; &lock; + &merge; &move; ¬ify; &prepare; diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index c43dbc9786..cce58fbf1d 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -182,6 +182,26 @@ will be fired. + + No separate triggers are defined for MERGE. Instead, + statement-level or row-level UPDATE, + DELETE and INSERT triggers are fired + depending on what actions are specified in the MERGE query + and what actions are activated. + + + + While running a MERGE command, statement-level + BEFORE and AFTER triggers are fired for + events specified in the actions of the MERGE command, + irrespective of whether the action is finally activated or not. This is same as + an UPDATE statement that updates no rows, yet + statement-level triggers are fired. The row-level triggers are fired only + when a row is actually updated, inserted or deleted. So it's perfectly legal + that while statement-level triggers are fired for certain type of action, no + row-level triggers are fired for the same kind of action. + + Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row -- cgit v1.2.1