Modified files for MERGE

8 files changed, 73 insertions, 6 deletions

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 943adfef774..8729ccd5c5a 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -3917,9 +3917,11 @@ char *PQcmdTuples(PGresult *res);
        <structname>PGresult</structname>. This function can only be used following
        the execution of a <command>SELECT</command>, <command>CREATE TABLE AS</command>,
        <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
-       <command>MOVE</command>, <command>FETCH</command>, or <command>COPY</command> statement,
-       or an <command>EXECUTE</command> of a prepared query that contains an
-       <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command> statement.
+       <command>MERGE</command>, <command>MOVE</command>, <command>FETCH</command>,
+       or <command>COPY</command> statement, or an <command>EXECUTE</command> of a
+       prepared query that contains an <command>INSERT</command>,
+       <command>UPDATE</command>, <command>DELETE</command>
+       or <command>MERGE</command> statement.
        If the command that generated the <structname>PGresult</structname> was anything
        else, <function>PQcmdTuples</function> 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 24613e3c754..0e3e89af560 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -423,6 +423,31 @@ COMMIT;
    </para>
 
    <para>
+    The <command>MERGE</command> allows the user to specify various combinations
+    of <command>INSERT</command>, <command>UPDATE</command> or
+    <command>DELETE</command> subcommands. A <command>MERGE</command> command
+    with both <command>INSERT</command> and <command>UPDATE</command>
+    subcommands looks similar to <command>INSERT</command> with an
+    <literal>ON CONFLICT DO UPDATE</literal> clause but does not guarantee
+    that either <command>INSERT</command> and <command>UPDATE</command> 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.
+   </para>
+
+   <para>
     Because Read Committed mode starts each command with a new snapshot
     that includes all transactions committed up to that instant,
     subsequent commands in the same transaction will see the effects
@@ -900,7 +925,8 @@ ERROR:  could not serialize access due to read/write dependencies among transact
 
         <para>
          The commands <command>UPDATE</command>,
-         <command>DELETE</command>, and <command>INSERT</command>
+         <command>DELETE</command>, <command>INSERT</command> and
+         <command>MERGE</command>
          acquire this lock mode on the target table (in addition to
          <literal>ACCESS SHARE</literal> 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 5b2aac618e3..59f6112b07c 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1246,7 +1246,7 @@ EXECUTE format('SELECT count(*) FROM %I '
 </programlisting>
      Another restriction on parameter symbols is that they only work in
      <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, and
-     <command>DELETE</command> commands.  In other statement
+     <command>DELETE</command> and <command>MERGE</command> commands.  In other statement
      types (generically called utility statements), you must insert
      values textually even if they are just data values.
     </para>
@@ -1529,6 +1529,7 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
           <listitem>
            <para>
             <command>UPDATE</command>, <command>INSERT</command>, and <command>DELETE</command>
+            and <command>MERGE</command>
             statements set <literal>FOUND</literal> true if at least one
             row is affected, false if no row is affected.
            </para>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 22e68932115..4e01e5641cf 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.
 <!ENTITY load               SYSTEM "load.sgml">
 <!ENTITY lock               SYSTEM "lock.sgml">
 <!ENTITY move               SYSTEM "move.sgml">
+<!ENTITY merge              SYSTEM "merge.sgml">
 <!ENTITY notify             SYSTEM "notify.sgml">
 <!ENTITY prepare            SYSTEM "prepare.sgml">
 <!ENTITY prepareTransaction SYSTEM "prepare_transaction.sgml">
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index 0e35b0ef43e..32f39a48ba9 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -94,6 +94,13 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
    exist, a <quote>default deny</quote> policy is assumed, so that no rows will
    be visible or updatable.
   </para>
+
+  <para>
+   No separate policy exists for <command>MERGE</command>. Instead policies
+   defined for <literal>SELECT</literal>, <literal>INSERT</literal>,
+   <literal>UPDATE</literal> and <literal>DELETE</literal> are applied
+   while executing MERGE, depending on the actions that are activated.
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 62e142fd8ef..da294aaa46a 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -579,6 +579,13 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
    is a partition, an error will occur if one of the input rows violates
    the partition constraint.
   </para>
+
+  <para>
+   You may also wish to consider using <command>MERGE</command>, since that
+   allows mixed <command>INSERT</command>, <command>UPDATE</command> and
+   <command>DELETE</command> within a single statement.
+   See <xref linkend="sql-merge"/>.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -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 <literal>VALUES</literal> clause or <replaceable>query</replaceable>,
-   is disallowed by the standard.
+   is disallowed by the standard. If you prefer a more SQL Standard
+   conforming statement than <literal>ON CONFLICT</literal>, see
+   <xref linkend="sql-merge"/>.
   </para>
 
   <para>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index d27fb414f7c..ef2270c4673 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -186,6 +186,7 @@
    &listen;
    &load;
    &lock;
+   &merge;
    &move;
    &notify;
    &prepare;
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index c43dbc9786e..cce58fbf1d0 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -183,6 +183,26 @@
    </para>
 
    <para>
+    No separate triggers are defined for <command>MERGE</command>. Instead,
+    statement-level or row-level <command>UPDATE</command>,
+    <command>DELETE</command> and <command>INSERT</command> triggers are fired
+    depending on what actions are specified in the <command>MERGE</command> query
+    and what actions are activated.
+   </para>
+
+   <para>
+    While running a <command>MERGE</command> command, statement-level
+    <literal>BEFORE</literal> and <literal>AFTER</literal> triggers are fired for
+    events specified in the actions of the <command>MERGE</command> command,
+    irrespective of whether the action is finally activated or not. This is same as
+    an <command>UPDATE</command> 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.
+   </para>
+
+   <para>
     Trigger functions invoked by per-statement triggers should always
     return <symbol>NULL</symbol>. Trigger functions invoked by per-row
     triggers can return a table row (a value of