Attached is a patch fixing a few doc omissions for MERGE.
I don't think that it's necessary to update every place that could
possibly apply to MERGE, but there are a few places where we give a
list of commands that may be used in a particular context, and I think
those should mention MERGE, if it applies.
Also, there were a couple of other places where it seemed worth giving
slightly more detail about what happens for MERGE.
Regards,
Dean
diff --git a/doc/src/sgml/arch-dev.sgml b/doc/src/sgml/arch-dev.sgml
new file mode 100644
index 0c7a53c..976db1e
--- a/doc/src/sgml/arch-dev.sgml
+++ b/doc/src/sgml/arch-dev.sgml
@@ -530,13 +530,15 @@
</para>
<para>
- The executor mechanism is used to evaluate all four basic SQL query
+ The executor mechanism is used to evaluate all five basic SQL query
types: <command>SELECT</command>, <command>INSERT</command>,
- <command>UPDATE</command>, and <command>DELETE</command>.
+ <command>UPDATE</command>, <command>DELETE</command>, and
+ <command>MERGE</command>.
For <command>SELECT</command>, the top-level executor code
only needs to send each row returned by the query plan tree
off to the client. <command>INSERT ... SELECT</command>,
- <command>UPDATE</command>, and <command>DELETE</command>
+ <command>UPDATE</command>, <command>DELETE</command>, and
+ <command>MERGE</command>
are effectively <command>SELECT</command>s under a special
top-level plan node called <literal>ModifyTable</literal>.
</para>
@@ -552,7 +554,13 @@
mark the old row deleted. For <command>DELETE</command>, the only
column that is actually returned by the plan is the TID, and the
<literal>ModifyTable</literal> node simply uses the TID to visit each
- target row and mark it deleted.
+ target row and mark it deleted. For <command>MERGE</command>, the
+ planner joins the source and target relations, and includes all
+ column values required by any of the <literal>WHEN</literal> clauses,
+ plus the TID of the target row; this data is fed up to the
+ <literal>ModifyTable</literal> node, which uses the information to
+ work out which <literal>WHEN</literal> clause to execute, and then
+ inserts, updates or deletes the target row, as required.
</para>
<para>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
new file mode 100644
index 8dc8d7a..5179125
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1804,7 +1804,8 @@ REVOKE ALL ON accounts FROM PUBLIC;
view, or other table-like object.
Also allows use of <command>COPY TO</command>.
This privilege is also needed to reference existing column values in
- <command>UPDATE</command> or <command>DELETE</command>.
+ <command>UPDATE</command>, <command>DELETE</command>,
+ or <command>MERGE</command>.
For sequences, this privilege also allows use of the
<function>currval</function> function.
For large objects, this privilege allows the object to be read.
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
new file mode 100644
index f180607..9d0deae
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1611,7 +1611,8 @@ synchronous_standby_names = 'ANY 2 (s1,
<listitem>
<para>
Data Manipulation Language (DML): <command>INSERT</command>,
- <command>UPDATE</command>, <command>DELETE</command>, <command>COPY FROM</command>,
+ <command>UPDATE</command>, <command>DELETE</command>,
+ <command>MERGE</command>, <command>COPY FROM</command>,
<command>TRUNCATE</command>.
Note that there are no allowed actions that result in a trigger
being executed during recovery. This restriction applies even to
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
new file mode 100644
index 54f329c..90822b3
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -788,9 +788,10 @@ ROLLBACK;
<para>
As seen in this example, when the query is an <command>INSERT</command>,
- <command>UPDATE</command>, or <command>DELETE</command> command, the actual work of
+ <command>UPDATE</command>, <command>DELETE</command>, or
+ <command>MERGE</command> command, the actual work of
applying the table changes is done by a top-level Insert, Update,
- or Delete plan node. The plan nodes underneath this node perform
+ Delete, or Merge plan node. The plan nodes underneath this node perform
the work of locating the old rows and/or computing the new data.
So above, we see the same sort of bitmap table scan we've seen already,
and its output is fed to an Update node that stores the updated rows.
@@ -803,7 +804,8 @@ ROLLBACK;
</para>
<para>
- When an <command>UPDATE</command> or <command>DELETE</command> command affects an
+ When an <command>UPDATE</command>, <command>DELETE</command>, or
+ <command>MERGE</command> command affects an
inheritance hierarchy, the output might look like this:
<screen>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 8897a54..7c8a49f
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1044,7 +1044,8 @@ INSERT INTO mytable VALUES (1,'one'), (2
<application>PL/pgSQL</application> variable values can be
automatically inserted into optimizable SQL commands, which
are <command>SELECT</command>, <command>INSERT</command>,
- <command>UPDATE</command>, <command>DELETE</command>, and certain
+ <command>UPDATE</command>, <command>DELETE</command>,
+ <command>MERGE</command>, and certain
utility commands that incorporate one of these, such
as <command>EXPLAIN</command> and <command>CREATE TABLE ... AS
SELECT</command>. In these commands,
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
new file mode 100644
index 93fc716..73b7f44
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -4116,6 +4116,13 @@ psql "dbname=postgres replication=databa
</para>
<para>
+ For a <command>MERGE</command> command, the tag is
+ <literal>MERGE <replaceable>rows</replaceable></literal> where
+ <replaceable>rows</replaceable> is the number of rows inserted,
+ updated, or deleted.
+ </para>
+
+ <para>
For a <command>SELECT</command> or <command>CREATE TABLE AS</command>
command, the tag is <literal>SELECT <replaceable>rows</replaceable></literal>
where <replaceable>rows</replaceable> is the number of rows retrieved.
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
new file mode 100644
index 45741e7..6986ec5
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -2064,8 +2064,8 @@ SELECT <replaceable>select_list</replace
in a <literal>WITH</literal> clause can be a <command>SELECT</command>,
<command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>; and the
<literal>WITH</literal> clause itself is attached to a primary statement that can
- also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, or
- <command>DELETE</command>.
+ be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
+ <command>DELETE</command>, or <command>MERGE</command>.
</para>
<sect2 id="queries-with-select">
@@ -2587,7 +2587,8 @@ SELECT * FROM w AS w1 JOIN w AS w2 ON w1
<para>
The examples above only show <literal>WITH</literal> being used with
<command>SELECT</command>, but it can be attached in the same way to
- <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>.
+ <command>INSERT</command>, <command>UPDATE</command>,
+ <command>DELETE</command>, or <command>MERGE</command>.
In each case it effectively provides temporary table(s) that can
be referred to in the main command.
</para>
@@ -2597,8 +2598,9 @@ SELECT * FROM w AS w1 JOIN w AS w2 ON w1
<title>Data-Modifying Statements in <literal>WITH</literal></title>
<para>
- You can use data-modifying statements (<command>INSERT</command>,
- <command>UPDATE</command>, or <command>DELETE</command>) in <literal>WITH</literal>. This
+ You can use most data-modifying statements (<command>INSERT</command>,
+ <command>UPDATE</command>, or <command>DELETE</command>, but not
+ <command>MERGE</command>) in <literal>WITH</literal>. This
allows you to perform several different operations in the same query.
An example is:
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
new file mode 100644
index 370dac2..a2946fe
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -307,6 +307,12 @@ CREATE PUBLICATION <replaceable class="p
</para>
<para>
+ For a <command>MERGE</command> command, the publication will publish an
+ <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>
+ for each row inserted, updated, or deleted.
+ </para>
+
+ <para>
<command>ATTACH</command>ing a table into a partition tree whose root is
published using a publication with <literal>publish_via_partition_root</literal>
set to <literal>true</literal> does not result in the table's existing contents
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
new file mode 100644
index d4895b9..0fce622
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -94,7 +94,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replace
statement will happen as usual. If you wish to use
<command>EXPLAIN ANALYZE</command> on an
<command>INSERT</command>, <command>UPDATE</command>,
- <command>DELETE</command>, <command>CREATE TABLE AS</command>,
+ <command>DELETE</command>, <command>MERGE</command>,
+ <command>CREATE TABLE AS</command>,
or <command>EXECUTE</command> statement
without letting the command affect your data, use this approach:
<programlisting>
@@ -272,7 +273,8 @@ ROLLBACK;
<listitem>
<para>
Any <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
- <command>DELETE</command>, <command>VALUES</command>, <command>EXECUTE</command>,
+ <command>DELETE</command>, <command>MERGE</command>,
+ <command>VALUES</command>, <command>EXECUTE</command>,
<command>DECLARE</command>, <command>CREATE TABLE AS</command>, or
<command>CREATE MATERIALIZED VIEW AS</command> statement, whose execution
plan you wish to see.
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
new file mode 100644
index f9e0cdc..8ee9439
--- a/doc/src/sgml/ref/prepare.sgml
+++ b/doc/src/sgml/ref/prepare.sgml
@@ -116,7 +116,8 @@ PREPARE <replaceable class="parameter">n
<listitem>
<para>
Any <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
- <command>DELETE</command>, or <command>VALUES</command> statement.
+ <command>DELETE</command>, <command>MERGE</command>, or <command>VALUES</command>
+ statement.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/set_transaction.sgml b/doc/src/sgml/ref/set_transaction.sgml
new file mode 100644
index d394e62..727a927
--- a/doc/src/sgml/ref/set_transaction.sgml
+++ b/doc/src/sgml/ref/set_transaction.sgml
@@ -121,7 +121,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTI
The transaction isolation level cannot be changed after the first query or
data-modification statement (<command>SELECT</command>,
<command>INSERT</command>, <command>DELETE</command>,
- <command>UPDATE</command>, <command>FETCH</command>, or
+ <command>UPDATE</command>, <command>MERGE</command>,
+ <command>FETCH</command>, or
<command>COPY</command>) of a transaction has been executed. See
<xref linkend="mvcc"/> for more information about transaction
isolation and concurrency control.
@@ -131,8 +132,9 @@ SET SESSION CHARACTERISTICS AS TRANSACTI
The transaction access mode determines whether the transaction is
read/write or read-only. Read/write is the default. When a
transaction is read-only, the following SQL commands are
- disallowed: <literal>INSERT</literal>, <literal>UPDATE</literal>,
- <literal>DELETE</literal>, and <literal>COPY FROM</literal> if the
+ disallowed: <command>INSERT</command>, <command>UPDATE</command>,
+ <command>DELETE</command>, <command>MERGE</command>, and
+ <command>COPY FROM</command> if the
table they would write to is not a temporary table; all
<literal>CREATE</literal>, <literal>ALTER</literal>, and
<literal>DROP</literal> commands; <literal>COMMENT</literal>,
@@ -169,7 +171,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTI
start of a transaction, before the first query or
data-modification statement (<command>SELECT</command>,
<command>INSERT</command>, <command>DELETE</command>,
- <command>UPDATE</command>, <command>FETCH</command>, or
+ <command>UPDATE</command>, <command>MERGE</command>,
+ <command>FETCH</command>, or
<command>COPY</command>) of the transaction. Furthermore, the transaction
must already be set to <literal>SERIALIZABLE</literal> or
<literal>REPEATABLE READ</literal> isolation level (otherwise, the snapshot
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
new file mode 100644
index e2a5496..9620ea9
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -186,7 +186,8 @@
language can be packaged together and defined as a function.
Besides <command>SELECT</command> queries, the commands can include data
modification queries (<command>INSERT</command>,
- <command>UPDATE</command>, and <command>DELETE</command>), as well as
+ <command>UPDATE</command>, <command>DELETE</command>, and
+ <command>MERGE</command>), as well as
other SQL commands. (You cannot use transaction control commands, e.g.,
<command>COMMIT</command>, <command>SAVEPOINT</command>, and some utility
commands, e.g., <literal>VACUUM</literal>, in <acronym>SQL</acronym> functions.)
