merge.v10a.patch
application/octet-stream
Filename: merge.v10a.patch
Type: application/octet-stream
Part: 0
Patch
Format: unified
| File | + | − |
|---|---|---|
| doc/src/sgml/mvcc.sgml | 2 | 1 |
| doc/src/sgml/plpgsql.sgml | 2 | 1 |
| doc/src/sgml/ref/allfiles.sgml | 1 | 0 |
| doc/src/sgml/reference.sgml | 1 | 0 |
| doc/src/sgml/ref/merge.sgml | 564 | 0 |
| src/backend/catalog/sql_features.txt | 3 | 3 |
| src/backend/commands/explain.c | 14 | 0 |
| src/backend/commands/prepare.c | 1 | 0 |
| src/backend/commands/trigger.c | 8 | 0 |
| src/backend/executor/execMain.c | 1 | 0 |
| src/backend/executor/nodeModifyTable.c | 323 | 20 |
| src/backend/executor/README | 8 | 0 |
| src/backend/executor/spi.c | 3 | 0 |
| src/backend/nodes/copyfuncs.c | 38 | 0 |
| src/backend/nodes/equalfuncs.c | 32 | 0 |
| src/backend/nodes/nodeFuncs.c | 17 | 3 |
| src/backend/nodes/outfuncs.c | 22 | 0 |
| src/backend/nodes/readfuncs.c | 4 | 0 |
| src/backend/optimizer/plan/createplan.c | 10 | 2 |
| src/backend/optimizer/plan/planner.c | 6 | 2 |
| src/backend/optimizer/plan/setrefs.c | 51 | 0 |
| src/backend/optimizer/prep/preptlist.c | 36 | 1 |
| src/backend/optimizer/util/pathnode.c | 5 | 1 |
| src/backend/optimizer/util/plancat.c | 4 | 0 |
| src/backend/parser/analyze.c | 371 | 1 |
| src/backend/parser/gram.y | 156 | 1 |
| src/backend/parser/parse_clause.c | 100 | 3 |
| src/backend/rewrite/rewriteHandler.c | 51 | 2 |
| src/backend/tcop/pquery.c | 5 | 0 |
| src/backend/tcop/utility.c | 16 | 0 |
| src/include/executor/spi.h | 1 | 0 |
| src/include/nodes/execnodes.h | 19 | 1 |
| src/include/nodes/nodes.h | 5 | 1 |
| src/include/nodes/parsenodes.h | 29 | 3 |
| src/include/nodes/plannodes.h | 5 | 2 |
| src/include/nodes/relation.h | 4 | 2 |
| src/include/optimizer/pathnode.h | 2 | 1 |
| src/include/parser/kwlist.h | 2 | 0 |
| src/include/parser/parse_clause.h | 3 | 0 |
| src/include/parser/parse_node.h | 3 | 2 |
| src/pl/plpgsql/src/pl_exec.c | 4 | 1 |
| src/pl/plpgsql/src/pl_gram.y | 8 | 0 |
| src/pl/plpgsql/src/plpgsql.h | 2 | 2 |
| src/pl/plpgsql/src/pl_scanner.c | 1 | 0 |
| src/test/regress/expected/merge.out | 814 | 0 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/serial_schedule | 1 | 0 |
| src/test/regress/sql/merge.sql | 553 | 0 |
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 24613e3..01a0cd7 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -900,7 +900,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 ddd054c..b0db82f 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1252,7 +1252,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>
@@ -1535,6 +1535,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 22e6893..4e01e56 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/merge.sgml b/doc/src/sgml/ref/merge.sgml
new file mode 100644
index 0000000..2e936c6
--- /dev/null
+++ b/doc/src/sgml/ref/merge.sgml
@@ -0,0 +1,564 @@
+<!--
+doc/src/sgml/ref/merge.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-MERGE">
+
+ <refmeta>
+ <refentrytitle>MERGE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>MERGE</refname>
+ <refpurpose>insert, update, or delete rows of a table based upon source data</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+MERGE INTO <replaceable class="parameter">target_table_name</replaceable> [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
+USING <replaceable class="parameter">data_source</replaceable>
+ON <replaceable class="parameter">join_condition</replaceable>
+<replaceable class="parameter">when_clause</replaceable> [...]
+
+where <replaceable class="parameter">data_source</replaceable> is
+
+{ <replaceable class="parameter">source_table_name</replaceable> |
+ ( source_query )
+}
+[ [ AS ] <replaceable class="parameter">source_alias</replaceable> ]
+
+and <replaceable class="parameter">when_clause</replaceable> is
+
+{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> } |
+ WHEN NOT MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING }
+}
+
+and <replaceable class="parameter">merge_update</replaceable> is
+
+UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
+ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] )
+ } [, ...]
+
+and <replaceable class="parameter">merge_insert</replaceable> is
+
+INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )]
+[ OVERRIDING { SYSTEM | USER } VALUE ]
+{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES }
+
+and <replaceable class="parameter">merge_delete</replaceable> is
+
+DELETE
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>MERGE</command> performs actions that modify rows in the
+ <replaceable class="parameter">target_table_name</replaceable>,
+ using the <replaceable class="parameter">data_source</replaceable>.
+ <command>MERGE</command> provides a single <acronym>SQL</acronym>
+ statement that can conditionally <command>INSERT</command>,
+ <command>UPDATE</command> or <command>DELETE</command> rows, a task
+ that would otherwise require multiple procedural language statements.
+ </para>
+
+ <para>
+ First, the <command>MERGE</command> command performs a left outer join
+ from <replaceable class="parameter">data_source</replaceable> to
+ <replaceable class="parameter">target_table_name</replaceable>
+ producing zero or more candidate change rows. For each candidate change
+ row the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal> is set
+ just once, after which <literal>WHEN</literal> clauses are evaluated
+ in the order specified. If one of them is activated, the specified
+ action occurs. No more than one <literal>WHEN</literal> clause can be
+ activated for any candidate change row.
+ </para>
+
+ <para>
+ <command>MERGE</command> actions have the same effect as
+ regular <command>UPDATE</command>, <command>INSERT</command>, or
+ <command>DELETE</command> commands of the same names. The syntax of
+ those commands is different, notably that there is no <literal>WHERE</literal>
+ clause and no tablename is specified. All actions refer to the
+ <replaceable class="parameter">target_table_name</replaceable>,
+ though modifications to other tables may be made using triggers.
+ </para>
+
+ <para>
+ There is no MERGE privilege.
+ You must have the <literal>UPDATE</literal> privilege on the column(s)
+ of the <replaceable class="parameter">target_table_name</replaceable>
+ referred to in the <literal>SET</literal> clause
+ if you specify an update action, the <literal>INSERT</literal> privilege
+ on the <replaceable class="parameter">target_table_name</replaceable>
+ if you specify an insert action and/or the <literal>DELETE</literal>
+ privilege on the <replaceable class="parameter">target_table_name</replaceable>
+ if you specify a delete action on the
+ <replaceable class="parameter">target_table_name</replaceable>.
+ Privileges are tested once at statement start and are checked
+ whether or not particular <literal>WHEN</literal> clauses are activated
+ during the subsequent execution.
+ You will require the <literal>SELECT</literal> privilege on the
+ <replaceable class="parameter">data_source</replaceable> and any column(s)
+ of the <replaceable class="parameter">target_table_name</replaceable>
+ referred to in a <literal>condition</literal>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">target_table_name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the target table to merge into.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">target_alias</replaceable></term>
+ <listitem>
+ <para>
+ A substitute name for the target table. When an alias is
+ provided, it completely hides the actual name of the table. For
+ example, given <literal>MERGE foo AS f</literal>, the remainder of the
+ <command>MERGE</command> statement must refer to this table as
+ <literal>f</literal> not <literal>foo</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">source_table_name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the source table, view or
+ transition table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">source_query</replaceable></term>
+ <listitem>
+ <para>
+ A query (<command>SELECT</command> statement or <command>VALUES</command>
+ statement) that supplies the rows to be merged into the
+ <replaceable class="parameter">target_table_name</replaceable>.
+ Refer to the <xref linkend="sql-select"/>
+ statement or <xref linkend="sql-values"/>
+ statement for a description of the syntax.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">source_alias</replaceable></term>
+ <listitem>
+ <para>
+ A substitute name for the data source. When an alias is
+ provided, it completely hides whether table or query was specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">join_condition</replaceable></term>
+ <listitem>
+ <para>
+ <replaceable class="parameter">join_condition</replaceable> is
+ an expression resulting in a value of type
+ <type>boolean</type> (similar to a <literal>WHERE</literal>
+ clause) that specifies which rows in the
+ <replaceable class="parameter">data_source</replaceable>
+ match rows in the
+ <replaceable class="parameter">target_table_name</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">when_clause</replaceable></term>
+ <listitem>
+ <para>
+ At least one <literal>WHEN</literal> clause is required.
+ </para>
+ <para>
+ If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal>
+ and the candidate change row matches a row in the
+ <replaceable class="parameter">target_table_name</replaceable>
+ the <literal>WHEN</literal> clause is activated if the
+ <replaceable class="parameter">condition</replaceable> is
+ absent or is present and evaluates to <literal>true</literal>.
+ If the <literal>WHEN</literal> clause specifies <literal>WHEN NOT MATCHED</literal>
+ and the candidate change row does not match a row in the
+ <replaceable class="parameter">target_table_name</replaceable>
+ the <literal>WHEN</literal> clause is activated if the
+ <replaceable class="parameter">condition</replaceable> is
+ absent or is present and evaluates to <literal>true</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">condition</replaceable></term>
+ <listitem>
+ <para>
+ An expression that returns a value of type <type>boolean</type>.
+ If this expression returns <literal>true</literal> then the <literal>WHEN</literal>
+ clause will be activated and the corresponding action will occur for
+ that row.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">merge_insert</replaceable></term>
+ <listitem>
+ <para>
+ The specification of an <literal>INSERT</literal> action that inserts
+ one row into the target table.
+ The target column names can be listed in any order. If no list of
+ column names is given at all, the default is all the columns of the
+ table in their declared order.
+ </para>
+ <para>
+ Each column not present in the explicit or implicit column list will be
+ filled with a default value, either its declared default value
+ or null if there is none.
+ </para>
+ <para>
+ If the expression for any column is not of the correct data type,
+ automatic type conversion will be attempted.
+ </para>
+ <para>
+ If <replaceable class="parameter">target_table_name</replaceable>
+ is a partitioned table, each row is routed to the appropriate partition
+ and inserted into it.
+ If <replaceable class="parameter">target_table_name</replaceable>
+ is a partition, an error will occur if one of the input rows violates
+ the partition constraint.
+ </para>
+ <para>
+ Do not include the table name, as you would normally do with an
+ <xref linkend="sql-insert"/> command.
+ For example, <literal>INSERT INTO tab VALUES (1, 50)</literal> is invalid.
+ Column names may not be specified more than once.
+ <command>INSERT</command> actions cannot contain sub-selects.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">merge_update</replaceable></term>
+ <listitem>
+ <para>
+ The specification of an <literal>UPDATE</literal> action that updates
+ the current row of the <replaceable
+ class="parameter">target_table_name</replaceable>.
+ Column names may not be specified more than once.
+ </para>
+ <para>
+ Do not include the table name, as you would normally do with an
+ <xref linkend="sql-update"/> command.
+ For example, <literal>UPDATE tab SET col = 1</literal> is invalid. Also,
+ do not include a <literal>WHERE</literal> clause, since only the current
+ row can be updated. For example,
+ <literal>UPDATE SET col = 1 WHERE key = 57</literal> is invalid.
+ <command>UPDATE</command> actions cannot contain sub-selects in the
+ <literal>SET</literal> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">merge_delete</replaceable></term>
+ <listitem>
+ <para>
+ Specifies a <literal>DELETE</literal> action that deletes the current row
+ of the <replaceable class="parameter">target_table_name</replaceable>.
+ Do not include the tablename or any other clauses, as you would normally
+ do with an <xref linkend="sql-delete"/> command.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">column_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a column in the <replaceable
+ class="parameter">target_table_name</replaceable>. The column name
+ can be qualified with a subfield name or array subscript, if
+ needed. (Inserting into only some fields of a composite
+ column leaves the other fields null.) When referencing a
+ column, do not include the table's name in the specification
+ of a target column.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>OVERRIDING SYSTEM VALUE</literal></term>
+ <listitem>
+ <para>
+ Without this clause, it is an error to specify an explicit value
+ (other than <literal>DEFAULT</literal>) for an identity column defined
+ as <literal>GENERATED ALWAYS</literal>. This clause overrides that
+ restriction.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>OVERRIDING USER VALUE</literal></term>
+ <listitem>
+ <para>
+ If this clause is specified, then any values supplied for identity
+ columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored
+ and the default sequence-generated values are applied.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DEFAULT VALUES</literal></term>
+ <listitem>
+ <para>
+ All columns will be filled with their default values.
+ (An <literal>OVERRIDING</literal> clause is not permitted in this
+ form.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">expression</replaceable></term>
+ <listitem>
+ <para>
+ An expression to assign to the column. The expression can use the
+ old values of this and other columns in the table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DEFAULT</literal></term>
+ <listitem>
+ <para>
+ Set the column to its default value (which will be NULL if no
+ specific default expression has been assigned to it).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Outputs</title>
+
+ <para>
+ On successful completion, a <command>MERGE</command> command returns a command
+ tag of the form
+<screen>
+MERGE <replaceable class="parameter">total-count</replaceable>
+</screen>
+ The <replaceable class="parameter">total-count</replaceable> is the total
+ number of rows changed (whether updated, inserted or deleted).
+ If <replaceable class="parameter">total-count</replaceable> is 0, no rows
+ were changed in any way.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ The following steps take place during the execution of
+ <command>MERGE</command>.
+ <orderedlist>
+ <listitem>
+ <para>
+ Perform any BEFORE STATEMENT triggers for all actions specified, whether or
+ not their <literal>WHEN</literal> clauses are activated during execution.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Perform left outer join from source to target table. Then for each
+ candidate change row
+ <orderedlist>
+ <listitem>
+ <para>
+ Evaluate whether each row is MATCHED or NOT MATCHED.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Test each WHEN condition in the order specified until one activates.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ When activated, perform the following actions
+ <orderedlist>
+ <listitem>
+ <para>
+ Perform any BEFORE ROW triggers that fire for the action's event type.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Apply the action specified, invoking any check constraints on the
+ target table.
+ However, it will not invoke rules.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Perform any AFTER ROW triggers that fire for the action's event type.
+ </para>
+ </listitem>
+ </orderedlist>
+ </para>
+ </listitem>
+ </orderedlist>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Perform any AFTER STATEMENT triggers for actions specified, whether or
+ not they actually occur. This is similar to the behavior of an
+ <command>UPDATE</command> statement that modifies no rows.
+ </para>
+ </listitem>
+ </orderedlist>
+ In summary, statement triggers for an event type (say, INSERT) will
+ be fired whenever we <emphasis>specify</emphasis> an action of that kind. Row-level
+ triggers will fire only for the one event type <emphasis>activated</emphasis>.
+ So a <command>MERGE</command> might fire statement triggers for both
+ <command>UPDATE</command> and <command>INSERT</command>, even though only
+ <command>UPDATE</command> row triggers were fired.
+ </para>
+
+ <para>
+ The order in which rows are generated from the data source is indeterminate
+ by default. A <replaceable class="parameter">source_query</replaceable>
+ can be used to specify a consistent ordering, if required, which might be
+ needed to avoid deadlocks between concurrent transactions.
+ </para>
+
+ <para>
+ You should ensure that the join produces at most one candidate change row
+ for each target row. In other words, a target row shouldn't join to more
+ than one data source row. If it does, then only one of the candidate change
+ rows will be used to modify the target row, later attempts to modify will
+ cause an error. This can also occur if row triggers make changes to the
+ target table which are then subsequently modified by <command>MERGE</command>.
+ If the repeated action is an <command>INSERT</command> this will
+ cause a uniqueness violation while a repeated <command>UPDATE</command> or
+ <command>DELETE</command> will cause a cardinality violation; the latter behavior
+ is required by the <acronym>SQL</acronym> Standard. This differs from
+ historical <productname>PostgreSQL</productname> behavior of joins in
+ <command>UPDATE</command> and <command>DELETE</command> statements where second and
+ subsequent attempts to modify are simply ignored.
+ </para>
+
+ <para>
+ If the <literal>ON</literal> clause is a constant expression that evaluates to false
+ then no join takes place and the source is used directly as candidate change
+ rows.
+ </para>
+
+ <para>
+ If a <literal>WHEN</literal> clause omits an <literal>AND</literal> clause it becomes
+ the final reachable clause of that kind (<literal>MATCHED</literal> or
+ <literal>NOT MATCHED</literal>). If a later <literal>WHEN</literal> clause of that kind
+ is specified it would be provably unreachable and an error is raised.
+ If a final reachable clause is omitted it is possible that no action
+ will be taken for a candidate change row - it should be noted that no error
+ is raised if that occurs.
+ </para>
+
+ <para>
+ There is no <literal>RETURNING</literal> clause with <command>MERGE</command>.
+ Actions of <command>INSERT</command>, <command>UPDATE</command> and <command>DELETE</command>
+ cannot contain <literal>RETURNING</literal> or <literal>WITH</literal> clauses.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Perform maintenance on CustomerAccounts based upon new Transactions.
+
+<programlisting>
+MERGE CustomerAccount CA
+USING RecentTransactions T
+ON T.CustomerId = CA.CustomerId
+WHEN MATCHED THEN
+ UPDATE SET Balance = Balance + TransactionValue
+WHEN NOT MATCHED THEN
+ INSERT (CustomerId, Balance)
+ VALUES (T.CustomerId, T.TransactionValue)
+;
+</programlisting>
+
+ notice that this would be exactly equivalent to the following
+ statement because the <literal>MATCHED</literal> result does not change
+ during execution
+
+<programlisting>
+MERGE CustomerAccount CA
+USING (Select CustomerId, TransactionValue From RecentTransactions) AS T
+ON CA.CustomerId = T.CustomerId
+WHEN NOT MATCHED THEN
+ INSERT (CustomerId, Balance)
+ VALUES (T.CustomerId, T.TransactionValue)
+WHEN MATCHED THEN
+ UPDATE SET Balance = Balance + TransactionValue
+;
+</programlisting>
+ </para>
+
+ <para>
+ Attempt to insert a new stock item along with the quantity of stock. If
+ the item already exists, instead update the stock count of the existing
+ item. Don't allow entries that have zero stock.
+<programlisting>
+MERGE INTO wines w
+USING wine_stock_changes s
+ON s.winename = w.winename
+WHEN NOT MATCHED AND s.stock_delta > 0 THEN
+ INSERT VALUES(s.winename, s.stock_delta)
+WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
+ UPDATE SET stock = w.stock + s.stock_delta;
+WHEN MATCHED THEN
+ DELETE
+;
+</programlisting>
+
+ The wine_stock_changes table might be, for example, a temporary table
+ recently loaded into the database.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+ <para>
+ This command conforms to the <acronym>SQL</acronym> standard.
+ </para>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index d27fb41..ef2270c 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/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 8e746f3..4584a4f 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -229,9 +229,9 @@ F311 Schema definition statement 02 CREATE TABLE for persistent base tables YES
F311 Schema definition statement 03 CREATE VIEW YES
F311 Schema definition statement 04 CREATE VIEW: WITH CHECK OPTION YES
F311 Schema definition statement 05 GRANT statement YES
-F312 MERGE statement NO consider INSERT ... ON CONFLICT DO UPDATE
-F313 Enhanced MERGE statement NO
-F314 MERGE statement with DELETE branch NO
+F312 MERGE statement YES consider INSERT ... ON CONFLICT DO UPDATE
+F313 Enhanced MERGE statement YES
+F314 MERGE statement with DELETE branch YES
F321 User authorization YES
F341 Usage tables NO no ROUTINE_*_USAGE tables
F361 Subprogram support YES
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 41cd47e..6bc034a 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -893,6 +893,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case CMD_DELETE:
pname = operation = "Delete";
break;
+ case CMD_MERGE:
+ pname = operation = "Merge";
+ break;
default:
pname = "???";
break;
@@ -2923,6 +2926,10 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
operation = "Delete";
foperation = "Foreign Delete";
break;
+ case CMD_MERGE:
+ operation = "Merge";
+ foperation = "Foreign Merge";
+ break;
default:
operation = "???";
foperation = "Foreign ???";
@@ -3043,6 +3050,13 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
ExplainPropertyFloat("Conflicting Tuples", other_path, 0, es);
}
}
+ else if (node->operation == CMD_MERGE)
+ {
+ /*
+ * XXX Add more detailed instrumentation for MERGE changes
+ * when running EXPLAIN ANALYZE?
+ */
+ }
if (labeltargets)
ExplainCloseGroup("Target Tables", "Target Tables", false, es);
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index b945b15..c3610b1 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -151,6 +151,7 @@ PrepareQuery(PrepareStmt *stmt, const char *queryString,
case CMD_INSERT:
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_MERGE:
/* OK */
break;
default:
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 1c488c3..8f49368 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -4428,6 +4428,14 @@ MakeTransitionCaptureState(TriggerDesc *trigdesc, Oid relid, CmdType cmdType)
need_old = trigdesc->trig_delete_old_table;
need_new = false;
break;
+ case CMD_MERGE:
+ if (trigdesc->trig_insert_new_table ||
+ trigdesc->trig_update_new_table ||
+ trigdesc->trig_update_old_table ||
+ trigdesc->trig_delete_old_table)
+ elog(ERROR, "cannot execute MERGE on table with transition capture triggers");
+ need_old = need_new = false;
+ break;
default:
elog(ERROR, "unexpected CmdType: %d", (int) cmdType);
need_old = need_new = false; /* keep compiler quiet */
diff --git a/src/backend/executor/README b/src/backend/executor/README
index b3e74aa..3cef654 100644
--- a/src/backend/executor/README
+++ b/src/backend/executor/README
@@ -37,6 +37,14 @@ the plan tree returns the computed tuples to be updated, plus a "junk"
one. For DELETE, the plan tree need only deliver a CTID column, and the
ModifyTable node visits each of those rows and marks the row deleted.
+MERGE runs one generic plan that returns candidate target rows. Each row
+consists of a super-row that contains all the columns needed by any of the
+individual actions, plus a CTID junk column. If the CTID column is set we
+attempt to activate WHEN MATCHED actions, or if it is NULL then we will
+attempt to activate WHEN NOT MATCHED actions. Once we know which action
+is activated we form the final result row and apply only those changes,
+so we project twice for each result row.
+
XXX a great deal more documentation needs to be written here...
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 16822e9..8dbdc5a 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -232,6 +232,7 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
case CMD_INSERT:
case CMD_DELETE:
case CMD_UPDATE:
+ case CMD_MERGE:
estate->es_output_cid = GetCurrentCommandId(true);
break;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index c5eca1b..07671b3 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -676,6 +676,7 @@ ExecDelete(ModifyTableState *mtstate,
ItemPointer tupleid,
HeapTuple oldtuple,
TupleTableSlot *planSlot,
+ bool error_on_SelfUpdate,
EPQState *epqstate,
EState *estate,
bool canSetTag)
@@ -766,6 +767,7 @@ ldelete:;
estate->es_crosscheck_snapshot,
true /* wait for commit */ ,
&hufd);
+
switch (result)
{
case HeapTupleSelfUpdated:
@@ -773,11 +775,23 @@ ldelete:;
/*
* The target tuple was already updated or deleted by the
* current command, or by a later command in the current
- * transaction. The former case is possible in a join DELETE
+ * transaction.
+ */
+
+ /*
+ * The former case is possible in a join UPDATE
* where multiple tuples join to the same target tuple. This
- * is somewhat questionable, but Postgres has always allowed
- * it: we just ignore additional deletion attempts.
- *
+ * is pretty questionable, but Postgres has always allowed it:
+ * we just execute the first update action and ignore
+ * additional update attempts. SQLStandard disallows this for
+ * MERGE, so allow the caller to select how to handle this.
+ */
+ if (error_on_SelfUpdate)
+ ereport(ERROR,
+ (errcode(ERRCODE_CARDINALITY_VIOLATION),
+ errmsg("MERGE command cannot affect row a second time")));
+
+ /*
* The latter case arises if the tuple is modified by a
* command in a BEFORE trigger, or perhaps by a command in a
* volatile function used in the query. In such situations we
@@ -937,6 +951,7 @@ ExecUpdate(ModifyTableState *mtstate,
HeapTuple oldtuple,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
+ bool error_on_SelfUpdate,
EPQState *epqstate,
EState *estate,
bool canSetTag)
@@ -1071,12 +1086,23 @@ lreplace:;
/*
* The target tuple was already updated or deleted by the
* current command, or by a later command in the current
- * transaction. The former case is possible in a join UPDATE
+ * transaction.
+ */
+
+ /*
+ * The former case is possible in a join UPDATE
* where multiple tuples join to the same target tuple. This
* is pretty questionable, but Postgres has always allowed it:
* we just execute the first update action and ignore
- * additional update attempts.
- *
+ * additional update attempts. SQLStandard disallows this for
+ * MERGE, so allow the caller to select how to handle this.
+ */
+ if (error_on_SelfUpdate)
+ ereport(ERROR,
+ (errcode(ERRCODE_CARDINALITY_VIOLATION),
+ errmsg("MERGE command cannot affect row a second time")));
+
+ /*
* The latter case arises if the tuple is modified by a
* command in a BEFORE trigger, or perhaps by a command in a
* volatile function used in the query. In such situations we
@@ -1250,7 +1276,7 @@ ExecOnConflictUpdate(ModifyTableState *mtstate,
* there's no historical behavior to break.
*
* It is the user's responsibility to prevent this situation from
- * occurring. These problems are why SQL-2003 similarly specifies
+ * occurring. These problems are why SQL Standard similarly specifies
* that for SQL MERGE, an exception must be raised in the event of
* an attempt to update the same row twice.
*/
@@ -1372,7 +1398,7 @@ ExecOnConflictUpdate(ModifyTableState *mtstate,
/* Execute UPDATE with projection */
*returning = ExecUpdate(mtstate, &tuple.t_self, NULL,
- mtstate->mt_conflproj, planSlot,
+ mtstate->mt_conflproj, planSlot, false,
&mtstate->mt_epqstate, mtstate->ps.state,
canSetTag);
@@ -1383,6 +1409,9 @@ ExecOnConflictUpdate(ModifyTableState *mtstate,
/*
* Process BEFORE EACH STATEMENT triggers
+ *
+ * The precedent set by ON CONFLICT is that we fire INSERT then UPDATE.
+ * MERGE follows the same logic, firing INSERT, then UPDATE, then DELETE.
*/
static void
fireBSTriggers(ModifyTableState *node)
@@ -1411,6 +1440,14 @@ fireBSTriggers(ModifyTableState *node)
case CMD_DELETE:
ExecBSDeleteTriggers(node->ps.state, resultRelInfo);
break;
+ case CMD_MERGE:
+ if (node->mt_mergeSTriggers & ACL_INSERT)
+ ExecBSInsertTriggers(node->ps.state, resultRelInfo);
+ if (node->mt_mergeSTriggers & ACL_UPDATE)
+ ExecBSUpdateTriggers(node->ps.state, resultRelInfo);
+ if (node->mt_mergeSTriggers & ACL_DELETE)
+ ExecBSDeleteTriggers(node->ps.state, resultRelInfo);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -1438,6 +1475,9 @@ getASTriggerResultRelInfo(ModifyTableState *node)
/*
* Process AFTER EACH STATEMENT triggers
+ *
+ * The precedent set by ON CONFLICT is that when we have multiple
+ * triggers to fire we do that in reverse order to fireBSTriggers()
*/
static void
fireASTriggers(ModifyTableState *node)
@@ -1462,6 +1502,17 @@ fireASTriggers(ModifyTableState *node)
ExecASDeleteTriggers(node->ps.state, resultRelInfo,
node->mt_transition_capture);
break;
+ case CMD_MERGE:
+ if (node->mt_mergeSTriggers & ACL_DELETE)
+ ExecASDeleteTriggers(node->ps.state, resultRelInfo,
+ node->mt_transition_capture);
+ if (node->mt_mergeSTriggers & ACL_UPDATE)
+ ExecASUpdateTriggers(node->ps.state, resultRelInfo,
+ node->mt_transition_capture);
+ if (node->mt_mergeSTriggers & ACL_INSERT)
+ ExecASInsertTriggers(node->ps.state, resultRelInfo,
+ node->mt_transition_capture);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -1581,6 +1632,7 @@ ExecModifyTable(PlanState *pstate)
ItemPointerData tuple_ctid;
HeapTupleData oldtupdata;
HeapTuple oldtuple;
+ bool matched = false;
CHECK_FOR_INTERRUPTS();
@@ -1707,7 +1759,9 @@ ExecModifyTable(PlanState *pstate)
/*
* extract the 'ctid' or 'wholerow' junk attribute.
*/
- if (operation == CMD_UPDATE || operation == CMD_DELETE)
+ if (operation == CMD_UPDATE ||
+ operation == CMD_DELETE ||
+ operation == CMD_MERGE)
{
char relkind;
Datum datum;
@@ -1720,12 +1774,24 @@ ExecModifyTable(PlanState *pstate)
junkfilter->jf_junkAttNo,
&isNull);
/* shouldn't ever get a null result... */
- if (isNull)
+ if (isNull && operation != CMD_MERGE)
elog(ERROR, "ctid is NULL");
- tupleid = (ItemPointer) DatumGetPointer(datum);
- tuple_ctid = *tupleid; /* be sure we don't free ctid!! */
- tupleid = &tuple_ctid;
+ if (isNull)
+ {
+ Assert(operation == CMD_MERGE);
+ matched = false;
+
+ tupleid = NULL; /* we don't need it for INSERT actions */
+ }
+ else
+ {
+ matched = true; /* Meaningful only for CMD_MERGE */
+
+ tupleid = (ItemPointer) DatumGetPointer(datum);
+ tuple_ctid = *tupleid; /* be sure we don't free ctid!! */
+ tupleid = &tuple_ctid;
+ }
}
/*
@@ -1767,9 +1833,9 @@ ExecModifyTable(PlanState *pstate)
}
/*
- * apply the junkfilter if needed.
+ * apply the junkfilter if needed - we do this later for CMD_MERGE
*/
- if (operation != CMD_DELETE)
+ if (operation == CMD_UPDATE || operation == CMD_INSERT)
slot = ExecFilterJunk(junkfilter, slot);
}
@@ -1781,13 +1847,162 @@ ExecModifyTable(PlanState *pstate)
estate, node->canSetTag);
break;
case CMD_UPDATE:
- slot = ExecUpdate(node, tupleid, oldtuple, slot, planSlot,
+ slot = ExecUpdate(node, tupleid, oldtuple, slot, planSlot, false,
&node->mt_epqstate, estate, node->canSetTag);
break;
case CMD_DELETE:
- slot = ExecDelete(node, tupleid, oldtuple, planSlot,
+ slot = ExecDelete(node, tupleid, oldtuple, planSlot, false,
&node->mt_epqstate, estate, node->canSetTag);
break;
+ case CMD_MERGE:
+ {
+ ListCell *l;
+ ExprContext *econtext = node->ps.ps_ExprContext;
+ HeapTupleData tuple;
+
+#ifdef MERGE_DEBUG
+ elog(NOTICE, "MERGE row: %s",
+ (!matched ? "not matched" : "matched"));
+#endif
+
+ /*
+ * Make tuple and any needed join variables available to ExecQual and
+ * ExecProject. The target's existing tuple is installed in the
+ * scantuple.
+ */
+ econtext->ecxt_scantuple = node->mt_existing;
+ econtext->ecxt_innertuple = planSlot; /* ? */
+ econtext->ecxt_outertuple = NULL;
+
+ foreach(l, node->mt_mergeActionStateList)
+ {
+ MergeActionState *action = (MergeActionState *) lfirst(l);
+
+#ifdef MERGE_DEBUG
+ elog(NOTICE, " action: %s %s",
+ (action->matched ?
+ (action->commandType == CMD_UPDATE ? "UPDATE" : "DELETE") :
+ (action->commandType == CMD_INSERT ? "INSERT" : "DO NOTHING")),
+ (action->matched == matched ? "act " : "skip"));
+#endif
+
+ /*
+ * Apply either MATCHED or NOT MATCHED actions.
+ *
+ * The presence of a NULL value for ctid indicates that
+ * the source query did not match a target row and so at
+ * the time of the snapshot there was no matching row.
+ *
+ * The state of matched or not matched should not change
+ * after the first action is tested, otherwise we would
+ * not have a deterministic outcome, hence why the matched
+ * variable is local and non-modifiable by functions.
+ *
+ * It is valid if no actions are activated, we just do
+ * nothing for that candidate change row and move to next.
+ */
+ if (action->matched != matched)
+ continue;
+
+ /*
+ * Test condition, if any
+ *
+ * In the absence of a condition we perform the action
+ * unconditionally.
+ *
+ * If the whole condition evaluates to NULL we assume this
+ * acts like a WHERE clause and rejects the row.
+ */
+ if (action->condition)
+ {
+ elog(NOTICE, "checking condition");
+ if (!ExecQual(action->condition, econtext))
+ continue;
+ }
+
+ /* Perform stated action */
+ switch (action->commandType)
+ {
+ case CMD_INSERT:
+ {
+ /*
+ * We set up the projection earlier, so all we do
+ * here is Project, no need for any other tasks
+ * prior to the ExecInsert.
+ */
+ ExecProject(action->proj);
+
+ slot = ExecInsert(node, action->slot, planSlot,
+ NULL, ONCONFLICT_NONE, estate, node->canSetTag);
+ }
+ break;
+ case CMD_UPDATE:
+ {
+ HeapUpdateFailureData hufd;
+ LockTupleMode lockmode;
+ HTSU_Result test;
+ Buffer buffer;
+ Relation relation = resultRelInfo->ri_RelationDesc;
+
+ /* Determine lock mode to use */
+ lockmode = ExecUpdateLockMode(estate, resultRelInfo);
+
+ /*
+ * Lock tuple for update.
+ *
+ * XXX Is this really needed? I put this in
+ * just to get hold of the existing tuple.
+ * But if we do need, then we probably
+ * should be looking at the return value of
+ * heap_lock_tuple() and take appropriate
+ * action. So more needed around concurrency.
+ */
+ tuple.t_self = *tupleid;
+ test = heap_lock_tuple(relation, &tuple, estate->es_output_cid,
+ lockmode, LockWaitBlock, false, &buffer,
+ &hufd);
+
+
+ /* Store target's existing tuple in the state's dedicated slot */
+ ExecStoreTuple(&tuple, node->mt_existing, buffer, false);
+
+ /*
+ * We set up the projection earlier, so all we do
+ * here is Project, no need for any other tasks
+ * prior to the ExecUpdate.
+ */
+ ExecProject(action->proj);
+
+ slot = ExecUpdate(node, tupleid, oldtuple,
+ action->slot, planSlot, true,
+ &node->mt_epqstate, estate, node->canSetTag);
+
+ ReleaseBuffer(buffer);
+ }
+ break;
+ case CMD_DELETE:
+ {
+ /* Nothing to Project for a DELETE action */
+
+ slot = ExecDelete(node, tupleid, oldtuple, planSlot, true,
+ &node->mt_epqstate, estate, node->canSetTag);
+ }
+ break;
+ case CMD_NOTHING:
+ /* Do Nothing */
+ break;
+ default:
+ elog(ERROR, "unknown action in MERGE WHEN clause");
+ }
+
+ /*
+ * We've activated one of the WHEN clauses, so we don't search further.
+ * This is required behaviour, not an optimisation.
+ */
+ break;
+ }
+ }
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -1948,6 +2163,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
* valid trigger query context, so skip it in explain-only mode.
*/
if (!(eflags & EXEC_FLAG_EXPLAIN_ONLY))
+ /* Check for transition tables on the directly targeted relation. */
ExecSetupTransitionCaptureState(mtstate, estate);
/*
@@ -2185,6 +2401,89 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
}
}
+ /*
+ * Initialize everything for CMD_MERGE
+ */
+ mtstate->mt_mergeActionStateList = NIL;
+ if (node->mergeActionList)
+ {
+ ListCell *l;
+ ExprContext *econtext;
+
+ Assert(operation == CMD_MERGE);
+
+ /* merge may only have one plan, inheritance is not expanded */
+ Assert(nplans == 1);
+
+ mtstate->mt_mergeSTriggers = 0;
+
+ if (mtstate->ps.ps_ExprContext == NULL)
+ ExecAssignExprContext(estate, &mtstate->ps);
+
+ econtext = mtstate->ps.ps_ExprContext;
+
+ /* initialize slot for the existing tuple */
+ mtstate->mt_existing = ExecInitExtraTupleSlot(mtstate->ps.state);
+ ExecSetSlotDescriptor(mtstate->mt_existing,
+ resultRelInfo->ri_RelationDesc->rd_att);
+
+ /*
+ * Create a MergeActionState for each action on the mergeActionList
+ */
+ foreach(l, node->mergeActionList)
+ {
+ MergeAction *action = (MergeAction *) lfirst(l);
+ MergeActionState *action_state = makeNode(MergeActionState);
+ TupleDesc tupDesc;
+
+ action_state->matched = action->matched;
+ action_state->commandType = action->commandType;
+ if (action->qual)
+ {
+// Commented out because it crashes at present
+// action_state->condition = ExecInitQual((List *) action->qual,
+// &mtstate->ps);
+ }
+
+ /* create target slot for this action's projection */
+ tupDesc = ExecTypeFromTL((List *) action->targetList,
+ resultRelInfo->ri_RelationDesc->rd_rel->relhasoids);
+ action_state->slot = ExecInitExtraTupleSlot(mtstate->ps.state);
+ ExecSetSlotDescriptor(action_state->slot, tupDesc);
+
+ /* build action projection state */
+ action_state->proj =
+ ExecBuildProjectionInfo(action->targetList, econtext,
+ action_state->slot, &mtstate->ps,
+ resultRelInfo->ri_RelationDesc->rd_att);
+
+ mtstate->mt_mergeActionStateList = lappend(mtstate->mt_mergeActionStateList,
+ action_state);
+
+ /*
+ * XXX if we support transition tables this would need to move earlier
+ * before ExecSetupTransitionCaptureState()
+ */
+ switch (action->commandType)
+ {
+ case CMD_INSERT:
+ mtstate->mt_mergeSTriggers |= ACL_INSERT;
+ break;
+ case CMD_UPDATE:
+ mtstate->mt_mergeSTriggers |= ACL_UPDATE;
+ break;
+ case CMD_DELETE:
+ mtstate->mt_mergeSTriggers |= ACL_DELETE;
+ break;
+ case CMD_NOTHING:
+ break;
+ default:
+ elog(ERROR, "unknown operation");
+ break;
+ }
+ }
+ }
+
/* select first subplan */
mtstate->mt_whichplan = 0;
subplan = (Plan *) linitial(node->plans);
@@ -2198,7 +2497,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
* --- no need to look first. Typically, this will be a 'ctid' or
* 'wholerow' attribute, but in the case of a foreign data wrapper it
* might be a set of junk attributes sufficient to identify the remote
- * row.
+ * row. We follow this logic for MERGE, so it always has a junk 'ctid'.
*
* If there are multiple result relations, each one needs its own junk
* filter. Note multiple rels are only possible for UPDATE/DELETE, so we
@@ -2226,6 +2525,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
break;
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_MERGE:
junk_filter_needed = true;
break;
default:
@@ -2241,6 +2541,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
JunkFilter *j;
subplan = mtstate->mt_plans[i]->plan;
+ /* XXX we probably need to check plan output for CMD_MERGE also */
if (operation == CMD_INSERT || operation == CMD_UPDATE)
ExecCheckPlanOutput(resultRelInfo->ri_RelationDesc,
subplan->targetlist);
@@ -2249,7 +2550,9 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
resultRelInfo->ri_RelationDesc->rd_att->tdhasoid,
ExecInitExtraTupleSlot(estate));
- if (operation == CMD_UPDATE || operation == CMD_DELETE)
+ if (operation == CMD_UPDATE ||
+ operation == CMD_DELETE ||
+ operation == CMD_MERGE)
{
/* For UPDATE/DELETE, find the appropriate junk attr now */
char relkind;
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 995f67d..1a7a469 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -2316,6 +2316,9 @@ _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, uint64 tcount)
else
res = SPI_OK_UPDATE;
break;
+ case CMD_MERGE:
+ res = SPI_OK_MERGE;
+ break;
default:
return SPI_ERROR_OPUNKNOWN;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index ddbbc79..fa48d0b 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -220,6 +220,8 @@ _copyModifyTable(const ModifyTable *from)
COPY_NODE_FIELD(onConflictWhere);
COPY_SCALAR_FIELD(exclRelRTI);
COPY_NODE_FIELD(exclRelTlist);
+ COPY_NODE_FIELD(mergeSourceTargetList);
+ COPY_NODE_FIELD(mergeActionList);
return newnode;
}
@@ -2963,6 +2965,8 @@ _copyQuery(const Query *from)
COPY_NODE_FIELD(setOperations);
COPY_NODE_FIELD(constraintDeps);
COPY_NODE_FIELD(withCheckOptions);
+ COPY_NODE_FIELD(mergeSourceTargetList);
+ COPY_NODE_FIELD(mergeActionList);
COPY_LOCATION_FIELD(stmt_location);
COPY_LOCATION_FIELD(stmt_len);
@@ -3026,6 +3030,34 @@ _copyUpdateStmt(const UpdateStmt *from)
return newnode;
}
+static MergeStmt *
+_copyMergeStmt(const MergeStmt *from)
+{
+ MergeStmt *newnode = makeNode(MergeStmt);
+
+ COPY_NODE_FIELD(relation);
+ COPY_NODE_FIELD(source_relation);
+ COPY_NODE_FIELD(join_condition);
+ COPY_NODE_FIELD(mergeActionList);
+
+ return newnode;
+}
+
+static MergeAction *
+_copyMergeAction(const MergeAction *from)
+{
+ MergeAction *newnode = makeNode(MergeAction);
+
+ COPY_SCALAR_FIELD(matched);
+ COPY_SCALAR_FIELD(commandType);
+ COPY_NODE_FIELD(condition);
+ COPY_NODE_FIELD(qual);
+ COPY_NODE_FIELD(stmt);
+ COPY_NODE_FIELD(targetList);
+
+ return newnode;
+}
+
static SelectStmt *
_copySelectStmt(const SelectStmt *from)
{
@@ -5085,6 +5117,12 @@ copyObjectImpl(const void *from)
case T_UpdateStmt:
retval = _copyUpdateStmt(from);
break;
+ case T_MergeStmt:
+ retval = _copyMergeStmt(from);
+ break;
+ case T_MergeAction:
+ retval = _copyMergeAction(from);
+ break;
case T_SelectStmt:
retval = _copySelectStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 30ccc9c..38c57ad 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -986,6 +986,8 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_NODE_FIELD(setOperations);
COMPARE_NODE_FIELD(constraintDeps);
COMPARE_NODE_FIELD(withCheckOptions);
+ COMPARE_NODE_FIELD(mergeSourceTargetList);
+ COMPARE_NODE_FIELD(mergeActionList);
COMPARE_LOCATION_FIELD(stmt_location);
COMPARE_LOCATION_FIELD(stmt_len);
@@ -1042,6 +1044,30 @@ _equalUpdateStmt(const UpdateStmt *a, const UpdateStmt *b)
}
static bool
+_equalMergeStmt(const MergeStmt *a, const MergeStmt *b)
+{
+ COMPARE_NODE_FIELD(relation);
+ COMPARE_NODE_FIELD(source_relation);
+ COMPARE_NODE_FIELD(join_condition);
+ COMPARE_NODE_FIELD(mergeActionList);
+
+ return true;
+}
+
+static bool
+_equalMergeAction(const MergeAction *a, const MergeAction *b)
+{
+ COMPARE_SCALAR_FIELD(matched);
+ COMPARE_SCALAR_FIELD(commandType);
+ COMPARE_NODE_FIELD(condition);
+ COMPARE_NODE_FIELD(qual);
+ COMPARE_NODE_FIELD(stmt);
+ COMPARE_NODE_FIELD(targetList);
+
+ return true;
+}
+
+static bool
_equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
{
COMPARE_NODE_FIELD(distinctClause);
@@ -3223,6 +3249,12 @@ equal(const void *a, const void *b)
case T_UpdateStmt:
retval = _equalUpdateStmt(a, b);
break;
+ case T_MergeStmt:
+ retval = _equalMergeStmt(a, b);
+ break;
+ case T_MergeAction:
+ retval = _equalMergeAction(a, b);
+ break;
case T_SelectStmt:
retval = _equalSelectStmt(a, b);
break;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 6c76c41..3cf579d 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3224,9 +3224,9 @@ query_or_expression_tree_mutator(Node *node,
* boundaries: we descend to everything that's possibly interesting.
*
* Currently, the node type coverage here extends only to DML statements
- * (SELECT/INSERT/UPDATE/DELETE) and nodes that can appear in them, because
- * this is used mainly during analysis of CTEs, and only DML statements can
- * appear in CTEs.
+ * (SELECT/INSERT/UPDATE/DELETE/MERGE) and nodes that can appear in them,
+ * because this is used mainly during analysis of CTEs, and only DML
+ * statements can appear in CTEs.
*/
bool
raw_expression_tree_walker(Node *node,
@@ -3406,6 +3406,20 @@ raw_expression_tree_walker(Node *node,
return true;
}
break;
+ case T_MergeStmt:
+ {
+ MergeStmt *stmt = (MergeStmt *) node;
+
+ if (walker(stmt->relation, context))
+ return true;
+ if (walker(stmt->source_relation, context))
+ return true;
+ if (walker(stmt->join_condition, context))
+ return true;
+ if (walker(stmt->mergeActionList, context))
+ return true;
+ }
+ break;
case T_SelectStmt:
{
SelectStmt *stmt = (SelectStmt *) node;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 5e72df1..c207762 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -388,6 +388,21 @@ _outModifyTable(StringInfo str, const ModifyTable *node)
WRITE_NODE_FIELD(onConflictWhere);
WRITE_UINT_FIELD(exclRelRTI);
WRITE_NODE_FIELD(exclRelTlist);
+ WRITE_NODE_FIELD(mergeSourceTargetList);
+ WRITE_NODE_FIELD(mergeActionList);
+}
+
+static void
+_outMergeAction(StringInfo str, const MergeAction *node)
+{
+ WRITE_NODE_TYPE("MERGEACTION");
+
+ WRITE_BOOL_FIELD(matched);
+ WRITE_ENUM_FIELD(commandType, CmdType);
+ WRITE_NODE_FIELD(condition);
+ WRITE_NODE_FIELD(qual);
+ /* We don't dump the stmt node */
+ WRITE_NODE_FIELD(targetList);
}
static void
@@ -2113,6 +2128,8 @@ _outModifyTablePath(StringInfo str, const ModifyTablePath *node)
WRITE_NODE_FIELD(rowMarks);
WRITE_NODE_FIELD(onconflict);
WRITE_INT_FIELD(epqParam);
+ WRITE_NODE_FIELD(mergeSourceTargetList);
+ WRITE_NODE_FIELD(mergeActionList);
}
static void
@@ -2930,6 +2947,8 @@ _outQuery(StringInfo str, const Query *node)
WRITE_NODE_FIELD(setOperations);
WRITE_NODE_FIELD(constraintDeps);
/* withCheckOptions intentionally omitted, see comment in parsenodes.h */
+ WRITE_NODE_FIELD(mergeSourceTargetList);
+ WRITE_NODE_FIELD(mergeActionList);
WRITE_LOCATION_FIELD(stmt_location);
WRITE_LOCATION_FIELD(stmt_len);
}
@@ -3640,6 +3659,9 @@ outNode(StringInfo str, const void *obj)
case T_ModifyTable:
_outModifyTable(str, obj);
break;
+ case T_MergeAction:
+ _outMergeAction(str, obj);
+ break;
case T_Append:
_outAppend(str, obj);
break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 9925866..b5534ce 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -270,6 +270,8 @@ _readQuery(void)
READ_NODE_FIELD(setOperations);
READ_NODE_FIELD(constraintDeps);
/* withCheckOptions intentionally omitted, see comment in parsenodes.h */
+ READ_NODE_FIELD(mergeSourceTargetList);
+ READ_NODE_FIELD(mergeActionList);
READ_LOCATION_FIELD(stmt_location);
READ_LOCATION_FIELD(stmt_len);
@@ -1584,6 +1586,8 @@ _readModifyTable(void)
READ_NODE_FIELD(onConflictWhere);
READ_UINT_FIELD(exclRelRTI);
READ_NODE_FIELD(exclRelTlist);
+ READ_NODE_FIELD(mergeSourceTargetList);
+ READ_NODE_FIELD(mergeActionList);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index e599283..1f61b1c 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -281,7 +281,9 @@ static ModifyTable *make_modifytable(PlannerInfo *root,
Index nominalRelation, List *partitioned_rels,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List *returningLists,
- List *rowMarks, OnConflictExpr *onconflict, int epqParam);
+ List *rowMarks, OnConflictExpr *onconflict,
+ List *mergeSourceTargetList,
+ List *mergeActionList, int epqParam);
static GatherMerge *create_gather_merge_plan(PlannerInfo *root,
GatherMergePath *best_path);
@@ -2379,6 +2381,8 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path)
best_path->returningLists,
best_path->rowMarks,
best_path->onconflict,
+ best_path->mergeSourceTargetList,
+ best_path->mergeActionList,
best_path->epqParam);
copy_generic_path_info(&plan->plan, &best_path->path);
@@ -6444,7 +6448,9 @@ make_modifytable(PlannerInfo *root,
Index nominalRelation, List *partitioned_rels,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List *returningLists,
- List *rowMarks, OnConflictExpr *onconflict, int epqParam)
+ List *rowMarks, OnConflictExpr *onconflict,
+ List *mergeSourceTargetList,
+ List *mergeActionList, int epqParam)
{
ModifyTable *node = makeNode(ModifyTable);
List *fdw_private_list;
@@ -6501,6 +6507,8 @@ make_modifytable(PlannerInfo *root,
node->withCheckOptionLists = withCheckOptionLists;
node->returningLists = returningLists;
node->rowMarks = rowMarks;
+ node->mergeSourceTargetList = mergeSourceTargetList;
+ node->mergeActionList = mergeActionList;
node->epqParam = epqParam;
/*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 7b52dad..63d5bd1 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1519,6 +1519,8 @@ inheritance_planner(PlannerInfo *root)
returningLists,
rowMarks,
NULL,
+ NULL,
+ NULL,
SS_assign_special_param(root)));
}
@@ -2084,8 +2086,8 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
}
/*
- * If this is an INSERT/UPDATE/DELETE, and we're not being called from
- * inheritance_planner, add the ModifyTable node.
+ * If this is an INSERT/UPDATE/DELETE/MERGE, and we're not being called
+ * from inheritance_planner, add the ModifyTable node.
*/
if (parse->commandType != CMD_SELECT && !inheritance_update)
{
@@ -2130,6 +2132,8 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
returningLists,
rowMarks,
parse->onConflict,
+ parse->mergeSourceTargetList,
+ parse->mergeActionList,
SS_assign_special_param(root));
}
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 4617d12..a8d7712 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -851,6 +851,57 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
fix_scan_list(root, splan->exclRelTlist, rtoffset);
}
+ /*
+ * The MERGE produces the target rows by performing a right
+ * join between the target relation and the source relation
+ * (which could be a plain relation or a subquery. The INSERT
+ * and UPDATE actions of the MERGE requires access to the
+ * columns from the source relation. We arrange things so that
+ * the source relation attributes are available as INNER_VAR
+ * and the target relation attributes are available from the
+ * scan tuple.
+ */
+ if (splan->mergeActionList != NIL)
+ {
+ indexed_tlist *itlist;
+
+ /*
+ * mergeSourceTargetList is already setup correctly to
+ * include all Vars coming from the source relation. So we
+ * fix the targetList of individual action nodes by
+ * ensuring that the source relation Vars are referenced as
+ * INNER_VAR. Note that for this to work correctly, during
+ * execution, the ecxt_innertuple must be set to the tuple
+ * obtained from the source relation.
+ *
+ * We leave the Vars from the result relation (i.e. the
+ * target relation) unchanged i.e. those Vars would be
+ * picked from the scan slot. So during execution, we must
+ * ensure that ecxt_scantuple is setup correctly to refer
+ * to the tuple from the target relation.
+ */
+ itlist = build_tlist_index(splan->mergeSourceTargetList);
+
+ foreach (l, splan->mergeActionList)
+ {
+ MergeAction *action = (MergeAction *) lfirst(l);
+
+ /* Fix targetList of each action. */
+ action->targetList = fix_join_expr(root,
+ action->targetList,
+ NULL, itlist,
+ linitial_int(splan->resultRelations),
+ rtoffset);
+
+ /* Fix quals too. */
+ action->qual = (Node *) fix_join_expr(root,
+ (List *) action->qual,
+ NULL, itlist,
+ linitial_int(splan->resultRelations),
+ rtoffset);
+ }
+ }
+
splan->nominalRelation += rtoffset;
splan->exclRelRTI += rtoffset;
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index 8603fee..3d62724 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -105,7 +105,9 @@ preprocess_targetlist(PlannerInfo *root)
* scribbles on parse->targetList, which is not very desirable, but we
* keep it that way to avoid changing APIs used by FDWs.
*/
- if (command_type == CMD_UPDATE || command_type == CMD_DELETE)
+ if (command_type == CMD_UPDATE ||
+ command_type == CMD_DELETE ||
+ command_type == CMD_MERGE)
rewriteTargetListUD(parse, target_rte, target_relation);
/*
@@ -119,6 +121,38 @@ preprocess_targetlist(PlannerInfo *root)
result_relation, target_relation);
/*
+ * For MERGE command, handle targetlist of each MergeAction separately. We
+ * give the same treatment to MergeAction->targetList as we would have
+ * given to a regular INSERT/UPDATE/DELETE.
+ */
+ if (command_type == CMD_MERGE)
+ {
+ ListCell *l;
+
+ foreach(l, parse->mergeActionList)
+ {
+ MergeAction *action = (MergeAction *) lfirst(l);
+
+ switch (action->commandType)
+ {
+ case CMD_INSERT:
+ case CMD_UPDATE:
+ action->targetList = expand_targetlist(action->targetList,
+ action->commandType,
+ result_relation, target_relation);
+ break;
+ case CMD_DELETE:
+ break;
+ case CMD_NOTHING:
+ break;
+ default:
+ elog(ERROR, "unknown action in MERGE WHEN clause");
+
+ }
+ }
+ }
+
+ /*
* Add necessary junk columns for rowmarked rels. These values are needed
* for locking of rels selected FOR UPDATE/SHARE, and to do EvalPlanQual
* rechecking. See comments for PlanRowMark in plannodes.h.
@@ -348,6 +382,7 @@ expand_targetlist(List *tlist, int command_type,
true /* byval */ );
}
break;
+ case CMD_MERGE:
case CMD_UPDATE:
if (!att_tup->attisdropped)
{
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index fa4b468..29f2f97 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3282,6 +3282,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel,
* 'rowMarks' is a list of PlanRowMarks (non-locking only)
* 'onconflict' is the ON CONFLICT clause, or NULL
* 'epqParam' is the ID of Param for EvalPlanQual re-eval
+ * 'mergeActionList' is a list of MERGE actions
*/
ModifyTablePath *
create_modifytable_path(PlannerInfo *root, RelOptInfo *rel,
@@ -3291,7 +3292,8 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel,
List *subroots,
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
- int epqParam)
+ List *mergeSourceTargetList,
+ List *mergeActionList, int epqParam)
{
ModifyTablePath *pathnode = makeNode(ModifyTablePath);
double total_size;
@@ -3362,6 +3364,8 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel,
pathnode->rowMarks = rowMarks;
pathnode->onconflict = onconflict;
pathnode->epqParam = epqParam;
+ pathnode->mergeSourceTargetList = mergeSourceTargetList;
+ pathnode->mergeActionList = mergeActionList;
return pathnode;
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 8c60b35..472981d 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1816,6 +1816,10 @@ has_row_triggers(PlannerInfo *root, Index rti, CmdType event)
trigDesc->trig_delete_before_row))
result = true;
break;
+ /* MERGE doesn't have triggers, only INSERT/UPDATE/DELETE */
+ case CMD_MERGE:
+ result = false;
+ break;
default:
elog(ERROR, "unrecognized CmdType: %d", (int) event);
break;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index e7b2bc7..65d622f 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -65,6 +65,7 @@ static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
static void determineRecursiveColTypes(ParseState *pstate,
Node *larg, List *nrtargetlist);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
+static Query *transformMergeStmt(ParseState *pstate, MergeStmt *stmt);
static List *transformReturningList(ParseState *pstate, List *returningList);
static List *transformUpdateTargetList(ParseState *pstate,
List *targetList);
@@ -263,6 +264,7 @@ transformStmt(ParseState *pstate, Node *parseTree)
case T_InsertStmt:
case T_UpdateStmt:
case T_DeleteStmt:
+ case T_MergeStmt:
(void) test_raw_expression_coverage(parseTree, NULL);
break;
default:
@@ -287,6 +289,10 @@ transformStmt(ParseState *pstate, Node *parseTree)
result = transformUpdateStmt(pstate, (UpdateStmt *) parseTree);
break;
+ case T_MergeStmt:
+ result = transformMergeStmt(pstate, (MergeStmt *) parseTree);
+ break;
+
case T_SelectStmt:
{
SelectStmt *n = (SelectStmt *) parseTree;
@@ -357,6 +363,7 @@ analyze_requires_snapshot(RawStmt *parseTree)
case T_InsertStmt:
case T_DeleteStmt:
case T_UpdateStmt:
+ case T_MergeStmt:
case T_SelectStmt:
result = true;
break;
@@ -2250,8 +2257,371 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
}
/*
+ * transformMergeStmt -
+ * transforms a MERGE statement
+ */
+static Query *
+transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
+{
+ Query *qry = makeNode(Query);
+ ListCell *l;
+ AclMode targetPerms = ACL_NO_RIGHTS;
+ bool is_terminal[2];
+ JoinExpr *joinexpr;
+
+ qry->commandType = CMD_MERGE;
+
+ /*
+ * Check WHEN clauses for permissions and sanity
+ */
+ is_terminal[0] = false;
+ is_terminal[1] = false;
+ foreach(l, stmt->mergeActionList)
+ {
+ MergeAction *action = (MergeAction *) lfirst(l);
+ uint when_type = (action->matched ? 0 : 1);
+
+ /*
+ * Collect action types so we can check Target permissions
+ */
+ switch (action->commandType)
+ {
+ case CMD_INSERT:
+ {
+ InsertStmt *istmt = (InsertStmt *) action->stmt;
+ SelectStmt *selectStmt = (SelectStmt *) istmt->selectStmt;
+
+ /*
+ * The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE,
+ * or WITH to a VALUES clause and also multiple VALUES clauses.
+ * If we have any of those, ERROR.
+ */
+ if (selectStmt && (selectStmt->valuesLists == NIL ||
+ selectStmt->sortClause != NIL ||
+ selectStmt->limitOffset != NULL ||
+ selectStmt->limitCount != NULL ||
+ selectStmt->lockingClause != NIL ||
+ selectStmt->withClause != NULL))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("SELECT not allowed in MERGE INSERT statement")));
+
+ if (selectStmt && list_length(selectStmt->valuesLists) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("Multiple VALUES clauses not allowed in MERGE INSERT statement")));
+
+ targetPerms |= ACL_INSERT;
+ }
+ break;
+ case CMD_UPDATE:
+ targetPerms |= ACL_UPDATE;
+ break;
+ case CMD_DELETE:
+ targetPerms |= ACL_DELETE;
+ break;
+ case CMD_NOTHING:
+ break;
+ default:
+ elog(ERROR, "unknown action in MERGE WHEN clause");
+ }
+
+ /*
+ * Check for unreachable WHEN clauses
+ */
+ if (action->condition == NULL)
+ is_terminal[when_type] = true;
+ else if (is_terminal[when_type])
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unreachable WHEN clause specified after unconditional WHEN clause")));
+ }
+
+ /*
+ * Construct a query of the form
+ * SELECT relation.ctid --junk attribute
+ * ,source_relation.<somecols>
+ * ,relation.<somecols>
+ * FROM relation RIGHT JOIN source_relation
+ * ON join_condition
+ * -- no WHERE clause - all conditions are applied in executor
+ *
+ * We specify the join as a RIGHT JOIN as a simple way of forcing
+ * the first (larg) RTE to refer to the target table.
+ *
+ * The MERGE query's join can be tuned in some cases, see below
+ * for these special case tweaks.
+ *
+ * We set QSRC_PARSER to show query constructed in parse analysis
+ *
+ * Note that we have only one Query for a MERGE statement and
+ * the planner is called only once. That query is executed once
+ * to produce our stream of candidate change rows, so the query
+ * must contain all of them columns required by any one of the
+ * targetlist or conditions.
+ *
+ * As top-level statements INSERT, UPDATE and DELETE have a Query,
+ * whereas with MERGE the individual actions do not require
+ * separate planning, only different handling in the executor.
+ * See nodeModifyTable handling of commandType CMD_MERGE.
+ *
+ * stmt->relation is the target relation, given as a RangeVar
+ * stmt->source_relation is a RangeVar or subquery
+ *
+ * A sub-query can include the Target, but otherwise the sub-query
+ * cannot reference the outermost Target table at all.
+ */
+ qry->querySource = QSRC_PARSER;
+ joinexpr = makeNode(JoinExpr);
+ joinexpr->isNatural = false;
+ joinexpr->alias = NULL;
+ joinexpr->usingClause = NIL;
+ joinexpr->quals = stmt->join_condition;
+
+ /*
+ * Simplify the MERGE query as much as possible
+ *
+ * If there are no INSERT actions we won't be using the non-matching
+ * candidate rows for anything, so no need for an outer join. We
+ * do still need an inner join for UPDATE and DELETE actions.
+ *
+ * XXX if we have a constant ON clause, we can skip join altogether
+ *
+ * XXX if we were really keen we could look through the actionList
+ * and pull out common conditions, if there were no terminal clauses
+ * and put them into the main query as an early row filter
+ * but that seems like an atypical case and so checking for it
+ * would be likely to just be wasted effort.
+ *
+ * These seem like things that could go into Optimizer, but
+ * they are semantic simplications rather than optimizations, per se.
+ */
+ joinexpr->larg = (Node *) stmt->relation;
+ joinexpr->rarg = (Node *) stmt->source_relation;
+ if (targetPerms & ACL_INSERT)
+ joinexpr->jointype = JOIN_RIGHT;
+ else
+ joinexpr->jointype = JOIN_INNER;
+
+ /*
+ * We use a special purpose transformation here because the normal
+ * routines don't quite work right for the MERGE case.
+ *
+ * A special mergeSourceTargetList is setup by transformMergeJoinClause().
+ * It refers to all the attributes provided by the source relation. This is
+ * later used by set_plan_refs() to fix the UPDATE/INSERT target lists to
+ * so that they can correctly fetch the attributes from the source
+ * relation.
+ */
+ qry->resultRelation = transformMergeJoinClause(pstate,
+ stmt->relation,
+ targetPerms,
+ (Node *) joinexpr,
+ &qry->mergeSourceTargetList);
+
+ /*
+ * This query should just provide the source relation columns. Later, in
+ * preprocess_targetlist(), we shall also add "ctid" attribute of the
+ * target relation to ensure that the target tuple can be fetched
+ * correctly.
+ *
+ * XXX It's not clear if this targetlist can also include columns from the
+ * target relation so that both source and target columns are available in
+ * the tuple obtained from executing the plan.
+ */
+ qry->targetList = qry->mergeSourceTargetList;
+
+ /* qry has no WHERE clause so absent quals are shown as NULL */
+ qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
+ qry->rtable = pstate->p_rtable;
+
+ /*
+ * MERGE is unsupported in various cases
+ */
+ if (!(pstate->p_target_relation->rd_rel->relkind == RELKIND_RELATION ||
+ pstate->p_target_relation->rd_rel->relkind == RELKIND_MATVIEW))
+ elog(ERROR, "MERGE is not supported on this relation type");
+
+ /*
+ * We now have a good query shape, so now look at the when conditions
+ * and action targetlists.
+ *
+ * Overall, the MERGE Query's targetlist is NIL.
+ *
+ * Each individual action has its own targetlist that needs separate
+ * transformation. These transforms don't do anything to the overall
+ * targetlist, since that is only used for resjunk columns.
+ *
+ * We can reference any column in Target or Source, which is OK
+ * because both of those already have RTEs. There is nothing like
+ * the EXCLUDED pseudo-relation for INSERT ON CONFLICT.
+ */
+ foreach(l, stmt->mergeActionList)
+ {
+ MergeAction *action = (MergeAction *) lfirst(l);
+
+ /*
+ * Transform the when condition.
+ *
+ * We don't have a separate plan for each action, so the
+ * when condition must be executed as a per-row check,
+ * making it very similar to a CHECK constraint and so we
+ * adopt the same semantics for that.
+ *
+ * SQL Standard says we should not allow anything that possibly
+ * modifies SQL-data. Parallel safety is a superset of that
+ * restriction and enforcing that makes it easier to consider
+ * running MERGE plans in parallel in future, so we adopt
+ * that restriction here.
+ *
+ * XXX where to make the check for pre-reqs of AND clause??
+ *
+ * Note that we don't add this to the MERGE Query's quals
+ */
+ action->qual = transformWhereClause(pstate, action->condition,
+ EXPR_KIND_CHECK_CONSTRAINT, "WHEN");
+
+ /*
+ * Transform target lists for each INSERT and UPDATE action stmt
+ */
+ switch (action->commandType)
+ {
+ case CMD_INSERT:
+ {
+ InsertStmt *istmt = (InsertStmt *) action->stmt;
+ SelectStmt *selectStmt = (SelectStmt *) istmt->selectStmt;
+ List *exprList = NIL;
+ ListCell *lc;
+ RangeTblEntry *rte;
+ ListCell *icols;
+ ListCell *attnos;
+ List *icolumns;
+ List *attrnos;
+
+ pstate->p_is_insert = true;
+
+ icolumns = checkInsertTargets(pstate, istmt->cols, &attrnos);
+ Assert(list_length(icolumns) == list_length(attrnos));
+
+ /*
+ * Handle INSERT much like in transformInsertStmt
+ *
+ * XXX currently ignore stmt->override, if present
+ */
+ if (selectStmt == NULL)
+ {
+ /*
+ * We have INSERT ... DEFAULT VALUES. We can handle this case by
+ * emitting an empty targetlist --- all columns will be defaulted when
+ * the planner expands the targetlist.
+ */
+ exprList = NIL;
+ }
+ else
+ {
+ /*
+ * Process INSERT ... VALUES with a single VALUES sublist. We treat
+ * this case separately for efficiency. The sublist is just computed
+ * directly as the Query's targetlist, with no VALUES RTE. So it
+ * works just like a SELECT without any FROM.
+ */
+ List *valuesLists = selectStmt->valuesLists;
+
+ Assert(list_length(valuesLists) == 1);
+ Assert(selectStmt->intoClause == NULL);
+
+ /*
+ * Do basic expression transformation (same as a ROW() expr, but allow
+ * SetToDefault at top level)
+ */
+ exprList = transformExpressionList(pstate,
+ (List *) linitial(valuesLists),
+ EXPR_KIND_VALUES_SINGLE,
+ true);
+ /*
+ * !!TODO
+ *
+ * We should really not allow referencing the
+ * columns from the target relation in the INSERT
+ * values. Note that the grammer allows INSERT action
+ * only for NOT MATCHED rows and hence there is no
+ * target row when the INSERT action is executed.
+ * Without this check, we get execution time errors.
+ * But we should ideally catch them during parsing
+ * itself.
+ */
+
+ /* Prepare row for assignment to target table */
+ exprList = transformInsertRow(pstate, exprList,
+ istmt->cols,
+ icolumns, attrnos,
+ false);
+ }
+
+ /*
+ * Generate query's target list using the computed list of expressions.
+ * Also, mark all the target columns as needing insert permissions.
+ */
+ rte = pstate->p_target_rangetblentry;
+ icols = list_head(icolumns);
+ attnos = list_head(attrnos);
+ foreach(lc, exprList)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+ ResTarget *col;
+ AttrNumber attr_num;
+ TargetEntry *tle;
+
+ col = lfirst_node(ResTarget, icols);
+ attr_num = (AttrNumber) lfirst_int(attnos);
+
+ tle = makeTargetEntry(expr,
+ attr_num,
+ col->name,
+ false);
+ action->targetList = lappend(action->targetList, tle);
+
+ rte->insertedCols = bms_add_member(rte->insertedCols,
+ attr_num - FirstLowInvalidHeapAttributeNumber);
+
+ icols = lnext(icols);
+ attnos = lnext(attnos);
+ }
+ }
+ break;
+ case CMD_UPDATE:
+ {
+ UpdateStmt *ustmt = (UpdateStmt *) action->stmt;
+
+ pstate->p_is_insert = false;
+ action->targetList = transformUpdateTargetList(pstate, ustmt->targetList);
+ }
+ break;
+ case CMD_DELETE:
+ case CMD_NOTHING:
+ action->targetList = NIL;
+ break;
+ default:
+ elog(ERROR, "unknown action in MERGE WHEN clause");
+ }
+ }
+
+ qry->mergeActionList = stmt->mergeActionList;
+
+ /* XXX maybe later */
+ qry->returningList = NULL;
+
+ qry->hasTargetSRFs = false;
+ qry->hasSubLinks = false;
+
+ assign_query_collations(pstate, qry);
+
+ return qry;
+}
+
+/*
* transformUpdateTargetList -
- * handle SET clause in UPDATE/INSERT ... ON CONFLICT UPDATE
+ * handle SET clause in UPDATE/MERGE/INSERT ... ON CONFLICT UPDATE
*/
static List *
transformUpdateTargetList(ParseState *pstate, List *origTlist)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e42b7ca..e9c3c79 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -282,6 +282,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CreateMatViewStmt RefreshMatViewStmt CreateAmStmt
CreatePublicationStmt AlterPublicationStmt
CreateSubscriptionStmt AlterSubscriptionStmt DropSubscriptionStmt
+ MergeStmt
%type <node> select_no_parens select_with_parens select_clause
simple_select values_clause
@@ -582,6 +583,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> hash_partbound partbound_datum_list range_datum_list
%type <defelt> hash_partbound_elem
+%type <node> merge_when_clause opt_and_condition
+%type <list> merge_when_list
+%type <node> merge_update merge_delete merge_insert
+
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
* They must be listed first so that their numeric codes do not depend on
@@ -649,7 +654,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED
- MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE
+ MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE METHOD
+ MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NONE
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
@@ -915,6 +921,7 @@ stmt :
| RefreshMatViewStmt
| LoadStmt
| LockStmt
+ | MergeStmt
| NotifyStmt
| PrepareStmt
| ReassignOwnedStmt
@@ -10614,6 +10621,7 @@ ExplainableStmt:
| InsertStmt
| UpdateStmt
| DeleteStmt
+ | MergeStmt
| DeclareCursorStmt
| CreateAsStmt
| CreateMatViewStmt
@@ -11045,6 +11053,151 @@ set_target_list:
/*****************************************************************************
*
* QUERY:
+ * MERGE STATEMENT
+ *
+ *****************************************************************************/
+
+MergeStmt:
+ MERGE INTO relation_expr_opt_alias
+ USING table_ref
+ ON a_expr
+ merge_when_list
+ {
+ MergeStmt *m = makeNode(MergeStmt);
+
+ m->relation = $3;
+ m->source_relation = $5;
+ m->join_condition = $7;
+ m->mergeActionList = $8;
+
+ $$ = (Node *)m;
+ }
+ ;
+
+
+merge_when_list:
+ merge_when_clause { $$ = list_make1($1); }
+ | merge_when_list merge_when_clause { $$ = lappend($1,$2); }
+ ;
+
+merge_when_clause:
+ WHEN MATCHED opt_and_condition THEN merge_update
+ {
+ MergeAction *m = makeNode(MergeAction);
+
+ m->matched = true;
+ m->commandType = CMD_UPDATE;
+ m->condition = $3;
+ m->stmt = $5;
+
+ $$ = (Node *)m;
+ }
+ | WHEN MATCHED opt_and_condition THEN merge_delete
+ {
+ MergeAction *m = makeNode(MergeAction);
+
+ m->matched = true;
+ m->commandType = CMD_DELETE;
+ m->condition = $3;
+ m->stmt = $5;
+
+ $$ = (Node *)m;
+ }
+ | WHEN NOT MATCHED opt_and_condition THEN merge_insert
+ {
+ MergeAction *m = makeNode(MergeAction);
+
+ m->matched = false;
+ m->commandType = CMD_INSERT;
+ m->condition = $4;
+ m->stmt = $6;
+
+ $$ = (Node *)m;
+ }
+ | WHEN NOT MATCHED opt_and_condition THEN DO NOTHING
+ {
+ MergeAction *m = makeNode(MergeAction);
+
+ m->matched = false;
+ m->commandType = CMD_NOTHING;
+ m->condition = $4;
+ m->stmt = NULL;
+
+ $$ = (Node *)m;
+ }
+ ;
+
+opt_and_condition:
+ AND a_expr { $$ = $2; }
+ | { $$ = NULL; }
+ ;
+
+merge_delete:
+ DELETE_P
+ {
+ DeleteStmt *n = makeNode(DeleteStmt);
+ $$ = (Node *)n;
+ }
+ ;
+
+merge_update:
+ UPDATE SET set_clause_list
+ {
+ UpdateStmt *n = makeNode(UpdateStmt);
+ n->targetList = $3;
+
+ $$ = (Node *)n;
+ }
+ ;
+
+merge_insert:
+ INSERT values_clause
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = NIL;
+ n->selectStmt = $2;
+
+ $$ = (Node *)n;
+ }
+ | INSERT OVERRIDING override_kind values_clause
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = NIL;
+ n->override = $3;
+ n->selectStmt = $4;
+
+ $$ = (Node *)n;
+ }
+ | INSERT '(' insert_column_list ')' values_clause
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = $3;
+ n->selectStmt = $5;
+
+ $$ = (Node *)n;
+ }
+ | INSERT '(' insert_column_list ')' OVERRIDING override_kind values_clause
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = $3;
+ n->override = $6;
+ n->selectStmt = $7;
+
+ $$ = (Node *)n;
+ }
+ | INSERT DEFAULT VALUES
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = NIL;
+ n->selectStmt = NULL;
+
+ $$ = (Node *)n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY:
* CURSOR STATEMENTS
*
*****************************************************************************/
@@ -15039,8 +15192,10 @@ unreserved_keyword:
| LOGGED
| MAPPING
| MATCH
+ | MATCHED
| MATERIALIZED
| MAXVALUE
+ | MERGE
| METHOD
| MINUTE_P
| MINVALUE
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 9fbcfd4..d2162f2 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -28,6 +28,7 @@
#include "commands/defrem.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/print.h"
#include "optimizer/tlist.h"
#include "optimizer/var.h"
#include "parser/analyze.h"
@@ -72,6 +73,7 @@ static TableSampleClause *transformRangeTableSample(ParseState *pstate,
RangeTableSample *rts);
static Node *transformFromClauseItem(ParseState *pstate, Node *n,
RangeTblEntry **top_rte, int *top_rti,
+ RangeTblEntry **right_rte, int *right_rti,
List **namespace);
static Node *buildMergedJoinVar(ParseState *pstate, JoinType jointype,
Var *l_colvar, Var *r_colvar);
@@ -132,6 +134,7 @@ transformFromClause(ParseState *pstate, List *frmList)
n = transformFromClauseItem(pstate, n,
&rte,
&rtindex,
+ NULL, NULL,
&namespace);
checkNameSpaceConflicts(pstate, pstate->p_namespace, namespace);
@@ -153,9 +156,94 @@ transformFromClause(ParseState *pstate, List *frmList)
}
/*
+ * Special handling for MERGE statement is required because we assemble
+ * the query manually. This is similar to setTargetTable() followed
+ * by transformFromClause() but with a few less steps.
+ *
+ * We open the target relation and acquire a write lock on it.
+ * This must be done before processing the FROM list so that we grab
+ * the write lock before any read lock.
+ *
+ * Process the FROM clause and add items to the query's range table,
+ * joinlist, and namespace.
+ *
+ * Note: we assume that the pstate's p_rtable, p_joinlist, and p_namespace
+ * lists were initialized to NIL when the pstate was created.
+ *
+ * A special targetlist comprising of the columns from the right-subtree of
+ * the join is populated and returned. Note that when the JoinExpr is
+ * setup by transformMergeStmt, the left subtree has the target result
+ * relation and the right subtree has the source relation.
+ *
+ * Finally, we mark the relation as requiring the permissions specified
+ * by requiredPerms.
+ *
+ * Returns the rangetable index of the target relation.
+ */
+int
+transformMergeJoinClause(ParseState *pstate, RangeVar *relation,
+ AclMode requiredPerms, Node *merge,
+ List **mergeTargetList)
+{
+ RangeTblEntry *rte, *rt_rte;
+ List *namespace;
+ int rtindex, rt_rtindex;
+ Node *n;
+
+ /*
+ * Open target rel and grab suitable lock (which we will hold till end of
+ * transaction).
+ *
+ * free_parsestate() will eventually do the corresponding heap_close(),
+ * but *not* release the lock.
+ */
+ pstate->p_target_relation = parserOpenTable(pstate, relation,
+ RowExclusiveLock);
+
+ n = transformFromClauseItem(pstate, merge,
+ &rte,
+ &rtindex,
+ &rt_rte,
+ &rt_rtindex,
+ &namespace);
+
+ pstate->p_joinlist = list_make1(n);
+ pstate->p_namespace = list_concat(pstate->p_namespace, namespace);
+ setNamespaceLateralState(pstate->p_namespace, false, true);
+
+ /*
+ * Target relation gets added as first RTE because we set that as larg,
+ * so our left outer join (if any) is specified as JOIN_RIGHT.
+ */
+ rtindex = 1;
+ rte = rt_fetch(rtindex, pstate->p_rtable);
+
+ /*
+ * Override addRangeTableEntry's default ACL_SELECT permissions check, and
+ * instead mark target table as requiring exactly the specified
+ * permissions.
+ *
+ * If we find an explicit reference to the rel later during parse
+ * analysis, we will add the ACL_SELECT bit back again; see
+ * markVarForSelectPriv and its callers.
+ */
+ rte->requiredPerms = requiredPerms;
+ pstate->p_target_rangetblentry = rte;
+
+ /*
+ * Expand the right relation and add its columns to the
+ * mergeTargetList. Note that the right relation can either be a plain
+ * relation or a subquery or anything that can have a RangeTableEntry.
+ */
+ *mergeTargetList = expandRelAttrs(pstate, rt_rte, rt_rtindex, 0, -1);
+
+ return rtindex;
+}
+
+/*
* setTargetTable
- * Add the target relation of INSERT/UPDATE/DELETE to the range table,
- * and make the special links to it in the ParseState.
+ * Add the target relation of INSERT/UPDATE/DELETE to the
+ * range table, and make the special links to it in the ParseState.
*
* We also open the target relation and acquire a write lock on it.
* This must be done before processing the FROM list, in case the target
@@ -1096,6 +1184,7 @@ getRTEForSpecialRelationTypes(ParseState *pstate, RangeVar *rv)
static Node *
transformFromClauseItem(ParseState *pstate, Node *n,
RangeTblEntry **top_rte, int *top_rti,
+ RangeTblEntry **right_rte, int *right_rti,
List **namespace)
{
if (IsA(n, RangeVar))
@@ -1187,7 +1276,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
/* Recursively transform the contained relation */
rel = transformFromClauseItem(pstate, rts->relation,
- top_rte, top_rti, namespace);
+ top_rte, top_rti, NULL, NULL, namespace);
/* Currently, grammar could only return a RangeVar as contained rel */
rtr = castNode(RangeTblRef, rel);
rte = rt_fetch(rtr->rtindex, pstate->p_rtable);
@@ -1233,6 +1322,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
j->larg = transformFromClauseItem(pstate, j->larg,
&l_rte,
&l_rtindex,
+ NULL, NULL,
&l_namespace);
/*
@@ -1260,6 +1350,7 @@ transformFromClauseItem(ParseState *pstate, Node *n,
j->rarg = transformFromClauseItem(pstate, j->rarg,
&r_rte,
&r_rtindex,
+ NULL, NULL,
&r_namespace);
/* Remove the left-side RTEs from the namespace list again */
@@ -1288,6 +1379,12 @@ transformFromClauseItem(ParseState *pstate, Node *n,
expandRTE(r_rte, r_rtindex, 0, -1, false,
&r_colnames, &r_colvars);
+ if (right_rte)
+ *right_rte = r_rte;
+
+ if (right_rti)
+ *right_rti = r_rtindex;
+
/*
* Natural join does not explicitly specify columns; must generate
* columns to join. Need to run through the list of columns from each
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 32e3798..509d567 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3330,13 +3330,56 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
}
else if (event == CMD_UPDATE)
{
+ Assert(parsetree->override == OVERRIDING_NOT_SET);
parsetree->targetList =
- rewriteTargetListIU(parsetree->targetList,
+ rewriteTargetListIU(parsetree->targetList,
parsetree->commandType,
parsetree->override,
rt_entry_relation,
parsetree->resultRelation, NULL);
}
+ else if (event == CMD_MERGE)
+ {
+ Assert(parsetree->override == OVERRIDING_NOT_SET);
+
+ /*
+ * Rewrite each action targetlist separately
+ */
+ foreach(lc1, parsetree->mergeActionList)
+ {
+ MergeAction *action = (MergeAction *) lfirst(lc1);
+
+ switch (action->commandType)
+ {
+ case CMD_NOTHING:
+ case CMD_DELETE: /* Nothing to do here */
+ break;
+ case CMD_UPDATE:
+ action->targetList =
+ rewriteTargetListIU(action->targetList,
+ action->commandType,
+ parsetree->override,
+ rt_entry_relation,
+ parsetree->resultRelation,
+ NULL);
+ break;
+ case CMD_INSERT:
+ /* InsertStmt *istmt = (InsertStmt *) action->stmt; */
+
+ action->targetList =
+ rewriteTargetListIU(action->targetList,
+ action->commandType,
+ parsetree->override, /* istmt->override, */
+ rt_entry_relation,
+ parsetree->resultRelation,
+ NULL);
+ break;
+ default:
+ elog(ERROR, "unrecognized commandType: %d", action->commandType);
+ break;
+ }
+ }
+ }
else if (event == CMD_DELETE)
{
/* Nothing to do here */
@@ -3350,7 +3393,13 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
locks = matchLocks(event, rt_entry_relation->rd_rules,
result_relation, parsetree, &hasUpdate);
- product_queries = fireRules(parsetree,
+ /*
+ * First rule of MERGE club is we don't talk about rules
+ */
+ if (event == CMD_MERGE)
+ product_queries = NIL;
+ else
+ product_queries = fireRules(parsetree,
result_relation,
event,
locks,
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 66cc5c3..50f852a 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -193,6 +193,11 @@ ProcessQuery(PlannedStmt *plan,
"DELETE " UINT64_FORMAT,
queryDesc->estate->es_processed);
break;
+ case CMD_MERGE:
+ snprintf(completionTag, COMPLETION_TAG_BUFSIZE,
+ "MERGE " UINT64_FORMAT,
+ queryDesc->estate->es_processed);
+ break;
default:
strcpy(completionTag, "???");
break;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index ec98a61..135466a 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -109,6 +109,7 @@ CommandIsReadOnly(PlannedStmt *pstmt)
case CMD_UPDATE:
case CMD_INSERT:
case CMD_DELETE:
+ case CMD_MERGE:
return false;
case CMD_UTILITY:
/* For now, treat all utility commands as read/write */
@@ -1823,6 +1824,8 @@ QueryReturnsTuples(Query *parsetree)
case CMD_SELECT:
/* returns tuples */
return true;
+ case CMD_MERGE:
+ return false;
case CMD_INSERT:
case CMD_UPDATE:
case CMD_DELETE:
@@ -2067,6 +2070,10 @@ CreateCommandTag(Node *parsetree)
tag = "UPDATE";
break;
+ case T_MergeStmt:
+ tag = "MERGE";
+ break;
+
case T_SelectStmt:
tag = "SELECT";
break;
@@ -2810,6 +2817,9 @@ CreateCommandTag(Node *parsetree)
case CMD_DELETE:
tag = "DELETE";
break;
+ case CMD_MERGE:
+ tag = "MERGE";
+ break;
case CMD_UTILITY:
tag = CreateCommandTag(stmt->utilityStmt);
break;
@@ -2870,6 +2880,9 @@ CreateCommandTag(Node *parsetree)
case CMD_DELETE:
tag = "DELETE";
break;
+ case CMD_MERGE:
+ tag = "MERGE";
+ break;
case CMD_UTILITY:
tag = CreateCommandTag(stmt->utilityStmt);
break;
@@ -2918,6 +2931,7 @@ GetCommandLogLevel(Node *parsetree)
case T_InsertStmt:
case T_DeleteStmt:
case T_UpdateStmt:
+ case T_MergeStmt:
lev = LOGSTMT_MOD;
break;
@@ -3357,6 +3371,7 @@ GetCommandLogLevel(Node *parsetree)
case CMD_UPDATE:
case CMD_INSERT:
case CMD_DELETE:
+ case CMD_MERGE:
lev = LOGSTMT_MOD;
break;
@@ -3387,6 +3402,7 @@ GetCommandLogLevel(Node *parsetree)
case CMD_UPDATE:
case CMD_INSERT:
case CMD_DELETE:
+ case CMD_MERGE:
lev = LOGSTMT_MOD;
break;
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
index 43580c5..18a168c 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -64,6 +64,7 @@ typedef struct _SPI_plan *SPIPlanPtr;
#define SPI_OK_REL_REGISTER 15
#define SPI_OK_REL_UNREGISTER 16
#define SPI_OK_TD_REGISTER 17
+#define SPI_OK_MERGE 18
/* These used to be functions, now just no-ops for backwards compatibility */
#define SPI_push() ((void) 0)
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 4bb5cb1..dc7cacf 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -961,13 +961,28 @@ typedef struct ProjectSetState
} ProjectSetState;
/* ----------------
+ * MergeActionState information
+ * ----------------
+ */
+typedef struct MergeActionState
+{
+ NodeTag type;
+ bool matched; /* MATCHED or NOT MATCHED */
+ ExprState *condition; /* conditional expr (transformWhereClause) */
+ CmdType commandType; /* type of action */
+ Node *stmt; /* T_UpdateStmt etc */
+ TupleTableSlot *slot; /* instead of ResultRelInfo */
+ ProjectionInfo *proj; /* instead of ResultRelInfo */
+} MergeActionState;
+
+/* ----------------
* ModifyTableState information
* ----------------
*/
typedef struct ModifyTableState
{
PlanState ps; /* its first field is NodeTag */
- CmdType operation; /* INSERT, UPDATE, or DELETE */
+ CmdType operation; /* INSERT, UPDATE, DELETE or MERGE */
bool canSetTag; /* do we set the command tag/es_processed? */
bool mt_done; /* are we done? */
PlanState **mt_plans; /* subplans (one per target rel) */
@@ -993,6 +1008,9 @@ typedef struct ModifyTableState
/* controls transition table population for INSERT...ON CONFLICT UPDATE */
TupleConversionMap **mt_transition_tupconv_maps;
/* Per plan/partition tuple conversion */
+ List *mt_mergeActionList; /* List of MERGE actions */
+ List *mt_mergeActionStateList; /* List of MERGE action states */
+ AclMode mt_mergeSTriggers; /* Statement Trigger flags */
} ModifyTableState;
/* ----------------
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 2eb3d6d..ca385f7 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -96,6 +96,7 @@ typedef enum NodeTag
T_PlanState,
T_ResultState,
T_ProjectSetState,
+ T_MergeActionState,
T_ModifyTableState,
T_AppendState,
T_MergeAppendState,
@@ -307,6 +308,8 @@ typedef enum NodeTag
T_InsertStmt,
T_DeleteStmt,
T_UpdateStmt,
+ T_MergeStmt,
+ T_MergeAction,
T_SelectStmt,
T_AlterTableStmt,
T_AlterTableCmd,
@@ -655,7 +658,8 @@ typedef enum CmdType
CMD_SELECT, /* select stmt */
CMD_UPDATE, /* update stmt */
CMD_INSERT, /* insert stmt */
- CMD_DELETE,
+ CMD_DELETE, /* delete stmt */
+ CMD_MERGE, /* merge stmt */
CMD_UTILITY, /* cmds like create, destroy, copy, vacuum,
* etc. */
CMD_NOTHING /* dummy command for instead nothing rules
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b72178e..93895a2 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -38,7 +38,7 @@ typedef enum OverridingKind
typedef enum QuerySource
{
QSRC_ORIGINAL, /* original parsetree (explicit query) */
- QSRC_PARSER, /* added by parse analysis (now unused) */
+ QSRC_PARSER, /* added by parse analysis in MERGE */
QSRC_INSTEAD_RULE, /* added by unconditional INSTEAD rule */
QSRC_QUAL_INSTEAD_RULE, /* added by conditional INSTEAD rule */
QSRC_NON_INSTEAD_RULE /* added by non-INSTEAD rule */
@@ -107,7 +107,7 @@ typedef struct Query
{
NodeTag type;
- CmdType commandType; /* select|insert|update|delete|utility */
+ CmdType commandType; /* select|insert|update|delete|merge|utility */
QuerySource querySource; /* where did I come from? */
@@ -118,7 +118,7 @@ typedef struct Query
Node *utilityStmt; /* non-null if commandType == CMD_UTILITY */
int resultRelation; /* rtable index of target relation for
- * INSERT/UPDATE/DELETE; 0 for SELECT */
+ * INSERT/UPDATE/DELETE/MERGE; 0 for SELECT */
bool hasAggs; /* has aggregates in tlist or havingQual */
bool hasWindowFuncs; /* has window functions in tlist */
@@ -169,6 +169,8 @@ typedef struct Query
List *withCheckOptions; /* a list of WithCheckOption's, which are
* only added during rewrite and therefore
* are not written out as part of Query. */
+ List *mergeSourceTargetList;
+ List *mergeActionList; /* list of actions for MERGE (only) */
/*
* The following two fields identify the portion of the source text string
@@ -1487,6 +1489,30 @@ typedef struct UpdateStmt
} UpdateStmt;
/* ----------------------
+ * Merge Statement
+ * ----------------------
+ */
+typedef struct MergeStmt
+{
+ NodeTag type;
+ RangeVar *relation; /* target relation to merge */
+ Node *source_relation;/* source relation */
+ Node *join_condition; /* join condition between source and target */
+ List *mergeActionList;/* list of MergeAction(s) */
+} MergeStmt;
+
+typedef struct MergeAction
+{
+ NodeTag type;
+ bool matched; /* MATCHED or NOT MATCHED */
+ Node *condition; /* conditional expr (raw parser) */
+ Node *qual; /* conditional expr (transformWhereClause) */
+ CmdType commandType; /* type of action */
+ Node *stmt; /* T_UpdateStmt etc - not planned */
+ List *targetList; /* the target list (of ResTarget) */
+} MergeAction;
+
+/* ----------------------
* Select Statement
*
* A "simple" SELECT is represented in the output of gram.y by a single
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 74e9fb5..b09e908 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -18,6 +18,7 @@
#include "lib/stringinfo.h"
#include "nodes/bitmapset.h"
#include "nodes/lockoptions.h"
+#include "nodes/parsenodes.h"
#include "nodes/primnodes.h"
@@ -42,7 +43,7 @@ typedef struct PlannedStmt
{
NodeTag type;
- CmdType commandType; /* select|insert|update|delete|utility */
+ CmdType commandType; /* select|insert|update|delete|merge|utility */
uint64 queryId; /* query identifier (copied from Query) */
@@ -214,7 +215,7 @@ typedef struct ProjectSet
typedef struct ModifyTable
{
Plan plan;
- CmdType operation; /* INSERT, UPDATE, or DELETE */
+ CmdType operation; /* INSERT, UPDATE, DELETE or MERGE */
bool canSetTag; /* do we set the command tag/es_processed? */
Index nominalRelation; /* Parent RT index for use of EXPLAIN */
/* RT indexes of non-leaf tables in a partition tree */
@@ -235,6 +236,8 @@ typedef struct ModifyTable
Node *onConflictWhere; /* WHERE for ON CONFLICT UPDATE */
Index exclRelRTI; /* RTI of the EXCLUDED pseudo relation */
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
+ List *mergeSourceTargetList;
+ List *mergeActionList; /* actions for MERGE */
} ModifyTable;
/* ----------------
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 71689b8..1f40322 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -1660,7 +1660,7 @@ typedef struct LockRowsPath
} LockRowsPath;
/*
- * ModifyTablePath represents performing INSERT/UPDATE/DELETE modifications
+ * ModifyTablePath represents performing INSERT/UPDATE/DELETE/MERGE
*
* We represent most things that will be in the ModifyTable plan node
* literally, except we have child Path(s) not Plan(s). But analysis of the
@@ -1669,7 +1669,7 @@ typedef struct LockRowsPath
typedef struct ModifyTablePath
{
Path path;
- CmdType operation; /* INSERT, UPDATE, or DELETE */
+ CmdType operation; /* INSERT, UPDATE, DELETE or MERGE */
bool canSetTag; /* do we set the command tag/es_processed? */
Index nominalRelation; /* Parent RT index for use of EXPLAIN */
/* RT indexes of non-leaf tables in a partition tree */
@@ -1682,6 +1682,8 @@ typedef struct ModifyTablePath
List *rowMarks; /* PlanRowMarks (non-locking only) */
OnConflictExpr *onconflict; /* ON CONFLICT clause, or NULL */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
+ List *mergeSourceTargetList;
+ List *mergeActionList; /* actions for MERGE */
} ModifyTablePath;
/*
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 725694f..ac782a1 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -246,7 +246,8 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root,
List *subroots,
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
- int epqParam);
+ List *mergeSourceTargetList,
+ List *mergeActionList, int epqParam);
extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 26af944..58894ce 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -243,8 +243,10 @@ PG_KEYWORD("locked", LOCKED, UNRESERVED_KEYWORD)
PG_KEYWORD("logged", LOGGED, UNRESERVED_KEYWORD)
PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD)
PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD)
+PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD)
PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD)
PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD)
+PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD)
PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD)
PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD)
diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h
index 2c0e092..47c6ddc 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -17,6 +17,9 @@
#include "parser/parse_node.h"
extern void transformFromClause(ParseState *pstate, List *frmList);
+extern int transformMergeJoinClause(ParseState *pstate, RangeVar *relation,
+ AclMode requiredPerms, Node *merge,
+ List **mergeTargetList);
extern int setTargetTable(ParseState *pstate, RangeVar *relation,
bool inh, bool alsoSource, AclMode requiredPerms);
extern bool interpretOidsOption(List *defList, bool allowOids);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 4e96fa7..2ef4221 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -126,7 +126,8 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param,
* p_parent_cte: CommonTableExpr that immediately contains the current query,
* if any.
*
- * p_target_relation: target relation, if query is INSERT, UPDATE, or DELETE.
+ * p_target_relation: target relation, if query is INSERT, UPDATE, DELETE
+ * or MERGE.
*
* p_target_rangetblentry: target relation's entry in the rtable list.
*
@@ -180,7 +181,7 @@ struct ParseState
List *p_ctenamespace; /* current namespace for common table exprs */
List *p_future_ctes; /* common table exprs not yet in namespace */
CommonTableExpr *p_parent_cte; /* this query's containing CTE */
- Relation p_target_relation; /* INSERT/UPDATE/DELETE target rel */
+ Relation p_target_relation; /* INSERT/UPDATE/DELETE/MERGE target rel */
RangeTblEntry *p_target_rangetblentry; /* target rel's RTE */
bool p_is_insert; /* process assignment like INSERT not UPDATE */
List *p_windowdefs; /* raw representations of window clauses */
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index d096f24..4f1f665 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -3562,7 +3562,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
/*
* On the first call for this statement generate the plan, and detect
- * whether the statement is INSERT/UPDATE/DELETE
+ * whether the statement is INSERT/UPDATE/DELETE/MERGE
*/
if (expr->plan == NULL)
{
@@ -3583,6 +3583,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
{
if (q->commandType == CMD_INSERT ||
q->commandType == CMD_UPDATE ||
+ q->commandType == CMD_MERGE ||
q->commandType == CMD_DELETE)
stmt->mod_stmt = true;
}
@@ -3640,6 +3641,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
case SPI_OK_INSERT_RETURNING:
case SPI_OK_UPDATE_RETURNING:
case SPI_OK_DELETE_RETURNING:
+ case SPI_OK_MERGE:
Assert(stmt->mod_stmt);
exec_set_found(estate, (SPI_processed != 0));
break;
@@ -3817,6 +3819,7 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
case SPI_OK_INSERT_RETURNING:
case SPI_OK_UPDATE_RETURNING:
case SPI_OK_DELETE_RETURNING:
+ case SPI_OK_MERGE:
case SPI_OK_UTILITY:
case SPI_OK_REWRITTEN:
break;
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index d9cab1a..03c9e1c 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -299,6 +299,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_LAST
%token <keyword> K_LOG
%token <keyword> K_LOOP
+%token <keyword> K_MERGE
%token <keyword> K_MESSAGE
%token <keyword> K_MESSAGE_TEXT
%token <keyword> K_MOVE
@@ -1921,6 +1922,10 @@ stmt_execsql : K_IMPORT
{
$$ = make_execsql_stmt(K_INSERT, @1);
}
+ | K_MERGE
+ {
+ $$ = make_execsql_stmt(K_MERGE, @1);
+ }
| T_WORD
{
int tok;
@@ -2415,6 +2420,7 @@ unreserved_keyword :
| K_IS
| K_LAST
| K_LOG
+ | K_MERGE
| K_MESSAGE
| K_MESSAGE_TEXT
| K_MOVE
@@ -2876,6 +2882,8 @@ make_execsql_stmt(int firsttoken, int location)
{
if (prev_tok == K_INSERT)
continue; /* INSERT INTO is not an INTO-target */
+ if (prev_tok == K_MERGE)
+ continue; /* MERGE INTO is not an INTO-target */
if (firsttoken == K_IMPORT)
continue; /* IMPORT ... INTO is not an INTO-target */
if (have_into)
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index ee9aef8..1363148 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -135,6 +135,7 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("is", K_IS, UNRESERVED_KEYWORD)
PG_KEYWORD("last", K_LAST, UNRESERVED_KEYWORD)
PG_KEYWORD("log", K_LOG, UNRESERVED_KEYWORD)
+ PG_KEYWORD("merge", K_MERGE, UNRESERVED_KEYWORD)
PG_KEYWORD("message", K_MESSAGE, UNRESERVED_KEYWORD)
PG_KEYWORD("message_text", K_MESSAGE_TEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("move", K_MOVE, UNRESERVED_KEYWORD)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index c571afa..40fae97 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -740,8 +740,8 @@ typedef struct PLpgSQL_stmt_execsql
PLpgSQL_stmt_type cmd_type;
int lineno;
PLpgSQL_expr *sqlstmt;
- bool mod_stmt; /* is the stmt INSERT/UPDATE/DELETE? Note:
- * mod_stmt is set when we plan the query */
+ bool mod_stmt; /* is the stmt INSERT/UPDATE/DELETE/MERGE?
+ * Note mod_stmt is set when we plan the query */
bool into; /* INTO supplied? */
bool strict; /* INTO STRICT flag */
PLpgSQL_variable *target; /* INTO target (record or row) */
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 0000000..53a47df
--- /dev/null
+++ b/src/test/regress/expected/merge.out
@@ -0,0 +1,814 @@
+--
+-- MERGE
+--
+--\set VERBOSITY verbose
+--set debug_print_rewritten = true;
+--set debug_print_parse = true;
+--set debug_print_pretty = true;
+CREATE USER merge_privs;
+CREATE USER merge_no_privs;
+DROP TABLE IF EXISTS target;
+NOTICE: table "target" does not exist, skipping
+DROP TABLE IF EXISTS source;
+NOTICE: table "source" does not exist, skipping
+CREATE TABLE target (tid integer, balance integer);
+CREATE TABLE source (sid integer, delta integer); --no index
+INSERT INTO target VALUES (1, 10);
+INSERT INTO target VALUES (2, 20);
+INSERT INTO target VALUES (3, 30);
+SELECT t.ctid is not null as matched, t.*, s.* FROM source s FULL OUTER JOIN target t ON s.sid = t.tid ORDER BY t.tid, s.sid;
+ matched | tid | balance | sid | delta
+---------+-----+---------+-----+-------
+ t | 1 | 10 | |
+ t | 2 | 20 | |
+ t | 3 | 30 | |
+(3 rows)
+
+ALTER TABLE target OWNER TO merge_privs;
+ALTER TABLE source OWNER TO merge_privs;
+CREATE TABLE target2 (tid integer, balance integer);
+CREATE TABLE source2 (sid integer, delta integer);
+ALTER TABLE target2 OWNER TO merge_no_privs;
+ALTER TABLE source2 OWNER TO merge_no_privs;
+GRANT INSERT ON target TO merge_no_privs;
+SET SESSION AUTHORIZATION merge_privs;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+ QUERY PLAN
+----------------------------------------
+ Merge on target t
+ -> Merge Join
+ Merge Cond: (t.tid = s.sid)
+ -> Sort
+ Sort Key: t.tid
+ -> Seq Scan on target t
+ -> Sort
+ Sort Key: s.sid
+ -> Seq Scan on source s
+(9 rows)
+
+--
+-- Errors
+--
+MERGE INTO target t RANDOMWORD
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+ERROR: syntax error at or near "RANDOMWORD"
+LINE 1: MERGE INTO target t RANDOMWORD
+ ^
+-- MATCHED/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+ERROR: syntax error at or near "INSERT"
+LINE 5: INSERT DEFAULT VALUES
+ ^
+-- incorrectly specifying INTO target
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT INTO target DEFAULT VALUES
+;
+ERROR: syntax error at or near "INTO"
+LINE 5: INSERT INTO target DEFAULT VALUES
+ ^
+-- NOT MATCHED/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ UPDATE SET balance = 0
+;
+ERROR: syntax error at or near "UPDATE"
+LINE 5: UPDATE SET balance = 0
+ ^
+-- UPDATE tablename
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE target SET balance = 0
+;
+ERROR: syntax error at or near "target"
+LINE 5: UPDATE target SET balance = 0
+ ^
+-- permissions
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+ERROR: permission denied for relation source2
+GRANT INSERT ON target TO merge_no_privs;
+SET SESSION AUTHORIZATION merge_no_privs;
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+ERROR: permission denied for relation target
+GRANT UPDATE ON target2 TO merge_privs;
+SET SESSION AUTHORIZATION merge_privs;
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN MATCHED THEN
+ DELETE
+;
+ERROR: permission denied for relation target2
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN NOT MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+ERROR: permission denied for relation target2
+--
+-- initial tests
+--
+-- zero rows in source has no effect
+MERGE INTO target
+USING source
+ON target.tid = source.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+ROLLBACK;
+-- insert some non-matching source rows to work from
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source ORDER BY sid;
+ sid | delta
+-----+-------
+ 4 | 40
+(1 row)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ DO NOTHING
+;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ |
+(4 rows)
+
+ROLLBACK;
+-- index plans
+INSERT INTO target SELECT generate_series(1000,2500), 0;
+ALTER TABLE target ADD PRIMARY KEY (tid);
+ANALYZE target;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+ QUERY PLAN
+----------------------------------------
+ Merge on target t
+ -> Hash Join
+ Hash Cond: (s.sid = t.tid)
+ -> Seq Scan on source s
+ -> Hash
+ -> Seq Scan on target t
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+ QUERY PLAN
+----------------------------------------
+ Merge on target t
+ -> Hash Join
+ Hash Cond: (s.sid = t.tid)
+ -> Seq Scan on source s
+ -> Hash
+ -> Seq Scan on target t
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, NULL);
+ QUERY PLAN
+----------------------------------------
+ Merge on target t
+ -> Hash Left Join
+ Hash Cond: (s.sid = t.tid)
+ -> Seq Scan on source s
+ -> Hash
+ -> Seq Scan on target t
+(6 rows)
+
+;
+DELETE FROM target WHERE tid > 100;
+ANALYZE target;
+-- insert some matching source rows to work from
+INSERT INTO source VALUES (2, 5);
+INSERT INTO source VALUES (3, 20);
+SELECT * FROM source ORDER BY sid;
+ sid | delta
+-----+-------
+ 2 | 5
+ 3 | 20
+ 4 | 40
+(3 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+-- equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 0
+ 3 | 0
+(3 rows)
+
+ROLLBACK;
+-- equivalent of a DELETE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+(1 row)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, NULL)
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 |
+(4 rows)
+
+ROLLBACK;
+-- duplicate source row causes multiple target row update ERROR
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+ sid | delta
+-----+-------
+ 2 | 5
+ 2 | 5
+ 3 | 20
+ 4 | 40
+(4 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+ERROR: MERGE command cannot affect row a second time
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+ERROR: MERGE command cannot affect row a second time
+ROLLBACK;
+-- correct source data
+DELETE FROM source WHERE sid = 2;
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+ sid | delta
+-----+-------
+ 2 | 5
+ 3 | 20
+ 4 | 40
+(3 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+-- remove constraints
+alter table target drop CONSTRAINT target_pkey;
+alter table target alter column tid drop not null;
+-- multiple actions
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, 4)
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 0
+ 3 | 0
+ 4 | 4
+(4 rows)
+
+ROLLBACK;
+-- should be equivalent
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, 4);
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 0
+ 3 | 0
+ 4 | 4
+(4 rows)
+
+ROLLBACK;
+-- column references
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.delta
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 25
+ 3 | 50
+(3 rows)
+
+ROLLBACK;
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+-- and again with explicitly identified column list
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+-- and again with a subtle error: referring to non-existent target row for NOT MATCHED
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (t.tid, s.delta)
+;
+ERROR: cannot extract attribute from empty tuple slot
+-- and again with a constant ON clause
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON (SELECT true)
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (t.tid, s.delta)
+;
+ERROR: cannot handle unplanned sub-select
+SELECT * FROM target ORDER BY tid;
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+-- now the classic UPSERT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.delta
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 25
+ 3 | 50
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+-- unreachable WHEN clause should ERROR
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN /* Terminal WHEN clause for MATCHED */
+ DELETE
+WHEN MATCHED AND s.delta > 0 THEN
+ UPDATE SET balance = t.balance - s.delta
+;
+ERROR: unreachable WHEN clause specified after unconditional WHEN clause
+ROLLBACK;
+-- conditional WHEN clause
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND s.delta > 0 THEN
+ UPDATE SET balance = t.balance - s.delta
+;
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 15
+ 3 | 10
+(3 rows)
+
+ROLLBACK;
+-- test triggers
+create or replace function trigfunc () returns trigger
+language plpgsql as
+$$
+BEGIN
+ RAISE NOTICE '% % % trigger', TG_WHEN, TG_OP, TG_LEVEL;
+ IF (TG_WHEN = 'BEFORE' AND TG_LEVEL = 'ROW') THEN
+ IF (TG_OP = 'DELETE') THEN
+ RETURN OLD;
+ ELSE
+ RETURN NEW;
+ END IF;
+ ELSE
+ RETURN NULL;
+ END IF;
+END;
+$$;
+CREATE TRIGGER merge_bsi BEFORE INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bsu BEFORE UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bsd BEFORE DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_asi AFTER INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_asu AFTER UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_asd AFTER DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bri BEFORE INSERT ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bru BEFORE UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_brd BEFORE DELETE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_ari AFTER INSERT ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_aru AFTER UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_ard AFTER DELETE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+-- now the classic UPSERT, with a DELETE
+BEGIN;
+MERGE INTO target 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 THEN
+ INSERT VALUES (s.sid, s.delta)
+;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: BEFORE UPDATE STATEMENT trigger
+NOTICE: BEFORE DELETE STATEMENT trigger
+NOTICE: BEFORE INSERT ROW trigger
+NOTICE: BEFORE UPDATE ROW trigger
+NOTICE: BEFORE UPDATE ROW trigger
+NOTICE: AFTER INSERT ROW trigger
+NOTICE: AFTER UPDATE ROW trigger
+NOTICE: AFTER UPDATE ROW trigger
+NOTICE: AFTER DELETE STATEMENT trigger
+NOTICE: AFTER UPDATE STATEMENT trigger
+NOTICE: AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 15
+ 3 | 10
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+-- test from PL/pgSQL
+-- make sure MERGE INTO isn't interpreted to mean returning variables like SELECT INTO
+BEGIN;
+DO LANGUAGE plpgsql $$
+BEGIN
+MERGE INTO target 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
+;
+END;
+$$;
+NOTICE: BEFORE UPDATE STATEMENT trigger
+NOTICE: BEFORE UPDATE ROW trigger
+NOTICE: BEFORE UPDATE ROW trigger
+NOTICE: AFTER UPDATE ROW trigger
+NOTICE: AFTER UPDATE ROW trigger
+NOTICE: AFTER UPDATE STATEMENT trigger
+ROLLBACK;
+--source constants
+BEGIN;
+MERGE INTO target t
+USING (SELECT 9 AS sid, 57 AS delta) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: BEFORE INSERT ROW trigger
+NOTICE: AFTER INSERT ROW trigger
+NOTICE: AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 9 | 57
+(4 rows)
+
+ROLLBACK;
+--source query
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: BEFORE INSERT ROW trigger
+NOTICE: AFTER INSERT ROW trigger
+NOTICE: AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta as newname FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.newname)
+;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: BEFORE INSERT ROW trigger
+NOTICE: AFTER INSERT ROW trigger
+NOTICE: AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+--self-merge
+BEGIN;
+MERGE INTO target t
+USING (SELECT tid as sid, balance as delta FROM target WHERE balance > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING
+(SELECT sid, max(delta) AS delta
+ FROM source
+ GROUP BY sid
+ HAVING count(*) = 1
+ ORDER BY sid ASC) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: BEFORE INSERT ROW trigger
+NOTICE: AFTER INSERT ROW trigger
+NOTICE: AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+-- plpgsql parameters and results
+BEGIN;
+CREATE FUNCTION merge_func (p_id integer, p_bal integer)
+RETURNS INTEGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ result integer;
+BEGIN
+MERGE INTO target t
+USING (SELECT p_id AS sid) AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance - p_bal
+;
+IF FOUND THEN
+ GET DIAGNOSTICS result := ROW_COUNT;
+END IF;
+RETURN result;
+END;
+$$;
+SELECT merge_func(3, 4);
+NOTICE: BEFORE UPDATE STATEMENT trigger
+NOTICE: BEFORE UPDATE ROW trigger
+NOTICE: AFTER UPDATE ROW trigger
+NOTICE: AFTER UPDATE STATEMENT trigger
+ merge_func
+------------
+ 1
+(1 row)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 26
+(3 rows)
+
+ROLLBACK;
+-- SERIALIZABLE test
+-- handled in isolation tests
+-- test triggers
+-- TODO
+-- prepare
+RESET SESSION AUTHORIZATION;
+DROP TABLE target;
+DROP TABLE target2;
+DROP TABLE source;
+DROP TABLE source2;
+DROP USER merge_privs;
+ERROR: role "merge_privs" cannot be dropped because some objects depend on it
+DETAIL: owner of function trigfunc()
+DROP USER merge_no_privs;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index e224977..6f44e6a 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -84,7 +84,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
# Another group of parallel tests
# ----------
-test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password
+test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password merge
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 9fc5f1a..8735b0d 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -122,6 +122,7 @@ test: tablesample
test: groupingsets
test: drop_operator
test: password
+test: merge
test: alter_generic
test: alter_operator
test: misc
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 0000000..8a5f372
--- /dev/null
+++ b/src/test/regress/sql/merge.sql
@@ -0,0 +1,553 @@
+--
+-- MERGE
+--
+--\set VERBOSITY verbose
+
+--set debug_print_rewritten = true;
+--set debug_print_parse = true;
+--set debug_print_pretty = true;
+
+
+CREATE USER merge_privs;
+CREATE USER merge_no_privs;
+DROP TABLE IF EXISTS target;
+DROP TABLE IF EXISTS source;
+CREATE TABLE target (tid integer, balance integer);
+CREATE TABLE source (sid integer, delta integer); --no index
+INSERT INTO target VALUES (1, 10);
+INSERT INTO target VALUES (2, 20);
+INSERT INTO target VALUES (3, 30);
+SELECT t.ctid is not null as matched, t.*, s.* FROM source s FULL OUTER JOIN target t ON s.sid = t.tid ORDER BY t.tid, s.sid;
+
+ALTER TABLE target OWNER TO merge_privs;
+ALTER TABLE source OWNER TO merge_privs;
+
+CREATE TABLE target2 (tid integer, balance integer);
+CREATE TABLE source2 (sid integer, delta integer);
+
+ALTER TABLE target2 OWNER TO merge_no_privs;
+ALTER TABLE source2 OWNER TO merge_no_privs;
+
+GRANT INSERT ON target TO merge_no_privs;
+
+SET SESSION AUTHORIZATION merge_privs;
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+
+--
+-- Errors
+--
+MERGE INTO target t RANDOMWORD
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+-- MATCHED/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+-- incorrectly specifying INTO target
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT INTO target DEFAULT VALUES
+;
+-- NOT MATCHED/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ UPDATE SET balance = 0
+;
+-- UPDATE tablename
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE target SET balance = 0
+;
+
+-- permissions
+
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+
+GRANT INSERT ON target TO merge_no_privs;
+SET SESSION AUTHORIZATION merge_no_privs;
+
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+
+GRANT UPDATE ON target2 TO merge_privs;
+SET SESSION AUTHORIZATION merge_privs;
+
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN MATCHED THEN
+ DELETE
+;
+
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN NOT MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+
+--
+-- initial tests
+--
+-- zero rows in source has no effect
+MERGE INTO target
+USING source
+ON target.tid = source.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+ROLLBACK;
+
+-- insert some non-matching source rows to work from
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ DO NOTHING
+;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT DEFAULT VALUES
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- index plans
+INSERT INTO target SELECT generate_series(1000,2500), 0;
+ALTER TABLE target ADD PRIMARY KEY (tid);
+ANALYZE target;
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, NULL);
+;
+DELETE FROM target WHERE tid > 100;
+ANALYZE target;
+
+-- insert some matching source rows to work from
+INSERT INTO source VALUES (2, 5);
+INSERT INTO source VALUES (3, 20);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+
+-- equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- equivalent of a DELETE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, NULL)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- duplicate source row causes multiple target row update ERROR
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ DELETE
+;
+ROLLBACK;
+
+-- correct source data
+DELETE FROM source WHERE sid = 2;
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+
+-- remove constraints
+alter table target drop CONSTRAINT target_pkey;
+alter table target alter column tid drop not null;
+
+-- multiple actions
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, 4)
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- should be equivalent
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+ INSERT VALUES (4, 4);
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- column references
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.delta
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- and again with explicitly identified column list
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- and again with a subtle error: referring to non-existent target row for NOT MATCHED
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (t.tid, s.delta)
+;
+
+-- and again with a constant ON clause
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON (SELECT true)
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (t.tid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- now the classic UPSERT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.delta
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- unreachable WHEN clause should ERROR
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN /* Terminal WHEN clause for MATCHED */
+ DELETE
+WHEN MATCHED AND s.delta > 0 THEN
+ UPDATE SET balance = t.balance - s.delta
+;
+ROLLBACK;
+
+-- conditional WHEN clause
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND s.delta > 0 THEN
+ UPDATE SET balance = t.balance - s.delta
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- test triggers
+create or replace function trigfunc () returns trigger
+language plpgsql as
+$$
+BEGIN
+ RAISE NOTICE '% % % trigger', TG_WHEN, TG_OP, TG_LEVEL;
+ IF (TG_WHEN = 'BEFORE' AND TG_LEVEL = 'ROW') THEN
+ IF (TG_OP = 'DELETE') THEN
+ RETURN OLD;
+ ELSE
+ RETURN NEW;
+ END IF;
+ ELSE
+ RETURN NULL;
+ END IF;
+END;
+$$;
+CREATE TRIGGER merge_bsi BEFORE INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bsu BEFORE UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bsd BEFORE DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_asi AFTER INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_asu AFTER UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_asd AFTER DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bri BEFORE INSERT ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_bru BEFORE UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_brd BEFORE DELETE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_ari AFTER INSERT ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_aru AFTER UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+CREATE TRIGGER merge_ard AFTER DELETE ON target FOR EACH ROW EXECUTE PROCEDURE trigfunc ();
+
+-- now the classic UPSERT, with a DELETE
+BEGIN;
+MERGE INTO target 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 THEN
+ INSERT VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- test from PL/pgSQL
+-- make sure MERGE INTO isn't interpreted to mean returning variables like SELECT INTO
+BEGIN;
+DO LANGUAGE plpgsql $$
+BEGIN
+MERGE INTO target 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
+;
+END;
+$$;
+ROLLBACK;
+
+--source constants
+BEGIN;
+MERGE INTO target t
+USING (SELECT 9 AS sid, 57 AS delta) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+--source query
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta as newname FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.newname)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+--self-merge
+BEGIN;
+MERGE INTO target t
+USING (SELECT tid as sid, balance as delta FROM target WHERE balance > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING
+(SELECT sid, max(delta) AS delta
+ FROM source
+ GROUP BY sid
+ HAVING count(*) = 1
+ ORDER BY sid ASC) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+ INSERT (tid, balance) VALUES (s.sid, s.delta)
+;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- plpgsql parameters and results
+BEGIN;
+CREATE FUNCTION merge_func (p_id integer, p_bal integer)
+RETURNS INTEGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ result integer;
+BEGIN
+MERGE INTO target t
+USING (SELECT p_id AS sid) AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance - p_bal
+;
+IF FOUND THEN
+ GET DIAGNOSTICS result := ROW_COUNT;
+END IF;
+RETURN result;
+END;
+$$;
+SELECT merge_func(3, 4);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- SERIALIZABLE test
+-- handled in isolation tests
+
+-- test triggers
+-- TODO
+
+-- prepare
+
+RESET SESSION AUTHORIZATION;
+DROP TABLE target;
+DROP TABLE target2;
+DROP TABLE source;
+DROP TABLE source2;
+DROP USER merge_privs;
+DROP USER merge_no_privs;