merge_v103.tar
application/x-tar
merge_v103.patch 0000644 0000764 0001040 00000277306 11431162020 015022 0 ustar bxzhai Administrators diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 7518c84..3aa9aab 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -119,6 +119,7 @@ Complete list of usable sgml source files in this directory.
<!entity listen system "listen.sgml">
<!entity load system "load.sgml">
<!entity lock system "lock.sgml">
+<!entity merge system "merge.sgml">
<!entity move system "move.sgml">
<!entity notify system "notify.sgml">
<!entity prepare system "prepare.sgml">
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
new file mode 100644
index 0000000..a4a0849
--- /dev/null
+++ b/doc/src/sgml/ref/merge.sgml
@@ -0,0 +1,409 @@
+<!--
+$PostgreSQL$
+-->
+
+<refentry id="SQL-MERGE">
+ <refmeta>
+ <refentrytitle id="SQL-MERGE-TITLE">MERGE</refentrytitle>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>MERGE</refname>
+ <refpurpose>update, insert or delete rows of a table based upon source data</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-merge">
+ <primary>MERGE</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+MERGE INTO <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
+USING <replaceable class="PARAMETER">source-query</replaceable>
+ON <replaceable class="PARAMETER">join_condition</replaceable>
+[<replaceable class="PARAMETER">when_clause</replaceable> [...]]
+
+where <replaceable class="PARAMETER">when_clause</replaceable> is
+
+{ WHEN MATCHED [ AND <replaceable class="PARAMETER">condition</replaceable> ] THEN { <replaceable class="PARAMETER">merge_update</replaceable> | DELETE | DO NOTHING | RAISE ERROR}
+ WHEN NOT MATCHED [ AND <replaceable class="PARAMETER">condition</replaceable> ] THEN { <replaceable class="PARAMETER">merge_insert</replaceable> | DO NOTHING | RAISE ERROR} }
+
+where <replaceable class="PARAMETER">merge_update</replaceable> is
+
+UPDATE SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
+ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
+
+and <replaceable class="PARAMETER">merge_insert</replaceable> is
+
+INSERT [( <replaceable class="PARAMETER">column</replaceable> [, ...] )] { VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES }
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>MERGE</command> performs at most one action on each row from
+ the target table, driven by the rows from the source query. This
+ provides a way to specify a single SQL statement that can conditionally
+ <command>UPDATE</command> or <command>INSERT</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 source query to target table, producing zero or more merged rows. For
+ each merged row, <literal>WHEN</> clauses are evaluated in the
+ specified order until one of them is activated. The corresponding action
+ is then applied and processing continues for the next 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, though the syntax
+ is slightly different.
+ </para>
+
+ <para>
+ If no <literal>WHEN</> clause activates then an implicit action of
+ <literal>RAISE ERROR</> is performed for that row. If that
+ implicit action is not desirable an explicit action of
+ <literal>DO NOTHING</> may be specified instead.
+ </para>
+
+ <para>
+ <command>MERGE</command> will only affect rows only in the specified table.
+ </para>
+
+ <para>
+ There is no <literal>RETURNING</> clause with <command>MERGE</command>.
+ </para>
+
+ <para>
+ There is no MERGE privilege.
+ You must have the <literal>UPDATE</literal> privilege on the table
+ if you specify an update action, the <literal>INSERT</literal> privilege if
+ you specify an insert action and/or the <literal>DELETE</literal> privilege
+ if you wish to delete. You will also require the
+ <literal>SELECT</literal> privilege to any table whose values are read
+ in the <replaceable class="parameter">expressions</replaceable> or
+ <replaceable class="parameter">condition</replaceable>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="PARAMETER">table</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the table to merge into.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">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</>, the remainder of the
+ <command>MERGE</command> statement must refer to this table as
+ <literal>f</> not <literal>foo</>.
+ </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 target table.
+ 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">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 join are considered to
+ match. You should ensure that the join produces at most one output
+ row for each row to be modified. An attempt to modify any row of the
+ target table more than once will result in an error. This behaviour
+ requires the user to take greater care in using <command>MERGE</command>,
+ though is required explicitly by the SQL Standard.
+ </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</> then the <literal>WHEN</>
+ clause will be activated and the corresponding action will occur for
+ that row.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">merge_update</replaceable></term>
+ <listitem>
+ <para>
+ The specification of an <literal>UPDATE</> action. 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</> is invalid. Also,
+ do not include a <literal>WHERE</> clause, since only the current
+ can be updated. For example,
+ <literal>UPDATE SET col = 1 WHERE key = 57</> is invalid.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">merge_insert</replaceable></term>
+ <listitem>
+ <para>
+ The specification of an <literal>INSERT</> action. 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)</> is invalid.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">column</replaceable></term>
+ <listitem>
+ <para>
+ The name of a column in <replaceable
+ class="PARAMETER">table</replaceable>.
+ The column name can be qualified with a subfield name or array
+ subscript, if needed. Do not include the table's name in the
+ specification of a target column — for example,
+ <literal>UPDATE SET tab.col = 1</> is invalid.
+ </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 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 number
+ of rows changed (either updated, inserted or deleted).
+ If <replaceable class="parameter">total-count</replaceable> is 0, no rows
+ were changed (this is not considered an error).
+ </para>
+
+ <para>
+ The number of rows updated, inserted or deleted is not available as part
+ of the command tag. An optional NOTIFY message can be generated to
+ present this information, if desired.
+<screen>
+NOTIFY: 34 rows processed: 11 updated, 5 deleted, 15 inserted, 3 default inserts, 0 no action
+</screen>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ What essentially happens is that the target table is left outer-joined to
+ the tables mentioned in the <replaceable>source-query</replaceable>, and
+ each output row of the join may then activate at most one when-clause.
+ The row will be matched only once per statement, so the status of
+ <literal>MATCHED</> or <literal>NOT MATCHED</> cannot change once testing
+ of <literal>WHEN</> clauses has begun. <command>MERGE</command> will not
+ invoke Rules.
+ </para>
+
+ <para>
+ The following steps take place during the execution of
+ <command>MERGE</command>.
+ <orderedlist>
+ <listitem>
+ <para>
+ Perform any BEFORE STATEMENT triggers for actions specified, whether or
+ not they actually occur.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Perform left outer join from source to target table. Then for each 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.
+ Identify the action and its event type.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Perform any BEFORE ROW triggers that fire for the action's event type.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Apply the action specified.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Perform any AFTER ROW triggers that fire for the action's event type.
+ </para>
+ </listitem>
+ </orderedlist>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Perform any AFTER STATEMENT triggers for actions specified, whether or
+ not they actually occur.
+ </para>
+ </listitem>
+ </orderedlist>
+ In summary, statement triggers for an event type (say, INSERT) will
+ be fired whenever we <emphasis>specify</> an action of that kind. Row-level
+ triggers will fire only for event type <emphasis>activated</>.
+ So a <command>MERGE</command> might fire statement triggers for both
+ <literal>UPDATE</> and <literal>INSERT</>, even though only
+ <literal>UPDATE</> row triggers were fired.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <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.
+<programlisting>
+MERGE INTO wines w
+USING (VALUES('Chateau Lafite 2003', '24')) v
+ON v.column1 = w.winename
+WHEN NOT MATCHED THEN
+ INSERT VALUES(v.column1, v.column2)
+WHEN MATCHED THEN
+ UPDATE SET stock = stock + v.column2;
+</programlisting>
+ </para>
+
+ <para>
+ Perform maintenance on CustomerAccounts based upon new Transactions.
+ The following statement will fail if any accounts have had more than
+ one transaction
+
+<programlisting>
+MERGE CustomerAccount CA
+
+USING (SELECT CustomerId, TransactionValue,
+ FROM Transactions
+ WHERE TransactionId > 35345678) AS 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>
+
+ so the right way to do this is to pre-aggregate the data
+
+<programlisting>
+MERGE CustomerAccount CA
+
+USING (SELECT CustomerId, Sum(TransactionValue) As TransactionSum
+ FROM Transactions
+ WHERE TransactionId > 35345678
+ GROUP BY CustomerId) AS T
+
+ON T.CustomerId = CA.CustomerId
+
+WHEN MATCHED THEN
+ UPDATE SET Balance = Balance - TransactionSum
+
+WHEN NOT MATCHED THEN
+ INSERT (CustomerId, Balance)
+ VALUES (T.CustomerId, T.TransactionSum)
+;
+</programlisting>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ This command conforms to the <acronym>SQL</acronym> standard, except
+ that the <literal>DELETE</literal> and <literal>DO NOTHING</> actions
+ are <productname>PostgreSQL</productname> extensions.
+ </para>
+
+ <para>
+ According to the standard, the column-list syntax for an <literal>UPDATE</>
+ action should allow a list of columns to be assigned from a single
+ row-valued expression.
+ This is not currently implemented — the source must be a list
+ of independent expressions.
+ </para>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 5268794..677390c 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -327,6 +327,9 @@ UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;
</programlisting>
+
+ This operation can be executed in a single statement using
+ <xref linkend="sql-merge" endterm="sql-merge-title">.
</para>
<para>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index c33d883..5068235 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -147,6 +147,7 @@
&listen;
&load;
&lock;
+ &merge;
&move;
¬ify;
&prepare;
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index b776ad1..1a9e39a 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -73,6 +73,8 @@ static void show_sort_keys(SortState *sortstate, List *ancestors,
static void show_sort_info(SortState *sortstate, ExplainState *es);
static void show_hash_info(HashState *hashstate, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);
+static void ExplainMergeActions(ModifyTableState *mt_planstate,
+ List *ancestors, ExplainState *es);
static void ExplainScanTarget(Scan *plan, ExplainState *es);
static void ExplainMemberNodes(List *plans, PlanState **planstates,
List *ancestors, ExplainState *es);
@@ -636,6 +638,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case CMD_DELETE:
pname = operation = "Delete";
break;
+ case CMD_MERGE:
+ pname = operation = "Merge";
+ break;
default:
pname = "???";
break;
@@ -1190,6 +1195,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
switch (nodeTag(plan))
{
case T_ModifyTable:
+ ExplainMergeActions((ModifyTableState *)planstate, ancestors, es);
+
ExplainMemberNodes(((ModifyTable *) plan)->plans,
((ModifyTableState *) planstate)->mt_plans,
ancestors, es);
@@ -1482,6 +1489,64 @@ explain_get_index_name(Oid indexId)
return result;
}
+static void
+ExplainMergeActions(ModifyTableState *mt_planstate, List *ancestors, ExplainState *es)
+{
+ ListCell *l;
+ StringInfo buf = makeStringInfo();
+
+ if(mt_planstate->operation != CMD_MERGE || mt_planstate->mergeActPstates == NIL)
+ return;
+
+ foreach(l,mt_planstate->mergeActPstates)
+ {
+ ModifyTableState *mt_state = (ModifyTableState *)lfirst(l);
+
+ MergeActionState *act_pstate = (MergeActionState *)mt_state->mt_plans[0];
+
+ MergeAction *act_plan = (MergeAction *)act_pstate->ps.plan;
+
+ resetStringInfo(buf);
+
+ /*prepare the string for printing*/
+ switch(act_pstate->operation)
+ {
+ case CMD_INSERT:
+ appendStringInfoString(buf, "INSERT WHEN ");
+ break;
+ case CMD_UPDATE:
+ appendStringInfoString(buf, "UPDATE WHEN ");
+ break;
+ case CMD_DELETE:
+ appendStringInfoString(buf, "DELETE WHEN ");
+ break;
+ case CMD_DONOTHING:
+ appendStringInfoString(buf, "DO NOTHING WHEN ");
+ break;
+ case CMD_RAISEERR:
+ appendStringInfoString(buf, "RAISE ERROR WHEN ");
+ break;
+ default:
+ elog(ERROR, "unknown merge action type when explain");
+ }
+
+ if(act_plan->matched)
+ appendStringInfoString(buf, "MATCHED ");
+ else
+ appendStringInfoString(buf, "NOT MATCHED ");
+
+ if(act_plan->flattenedqual)
+ appendStringInfoString(buf, "AND ");
+
+ /*print it*/
+ ExplainPropertyText("ACTION", buf->data, es);
+
+ show_qual(act_plan->flattenedqual, " qual", &act_pstate->ps, ancestors, true, es);
+
+ }
+
+}
+
/*
* Show the target of a Scan node
*/
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 8b017ae..cbf59df 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2342,6 +2342,107 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
false, NULL, NULL, NIL, NULL);
}
+void
+ExecBSMergeTriggers(ModifyTableState *mt_state)
+{
+ ListCell *l;
+
+ bool doUpdateTriggers = false;
+ bool doInsertTriggers = false;
+ bool doDeleteTriggers = false;
+
+ foreach(l, mt_state->mergeActPstates)
+ {
+ ModifyTableState *actmtstate;
+ MergeActionState *actPstate;
+ MergeAction *actplan;
+
+ actmtstate = (ModifyTableState *)lfirst(l);
+
+ actPstate = (MergeActionState *)actmtstate->mt_plans[0];
+
+ actplan = (MergeAction *)actPstate->ps.plan;
+ /*the replace action does not fire triggers*/
+ if(actplan->replaced)
+ continue;
+
+ if(actplan->operation == CMD_UPDATE)
+ doUpdateTriggers = true;
+ else if(actplan->operation == CMD_INSERT)
+ doInsertTriggers = true;
+ else if(actplan->operation == CMD_DELETE)
+ doDeleteTriggers = true;
+
+ }
+
+ /*fire the triggers*/
+ if(doUpdateTriggers)
+ ExecBSUpdateTriggers(mt_state->ps.state,
+ mt_state->ps.state->es_result_relations);
+
+
+ if(doInsertTriggers)
+ ExecBSInsertTriggers(mt_state->ps.state,
+ mt_state->ps.state->es_result_relations);
+
+
+ if(doDeleteTriggers)
+ ExecBSDeleteTriggers(mt_state->ps.state,
+ mt_state->ps.state->es_result_relations);
+
+ return;
+}
+
+void
+ExecASMergeTriggers(ModifyTableState *mt_state)
+{
+ ListCell *l;
+
+ bool doUpdateTriggers = false;
+ bool doInsertTriggers = false;
+ bool doDeleteTriggers = false;
+
+ foreach(l, mt_state->mergeActPstates)
+ {
+ ModifyTableState *actmtstate;
+ MergeActionState *actPstate;
+ MergeAction *actplan;
+
+ actmtstate = (ModifyTableState *)lfirst(l);
+
+ actPstate = (MergeActionState *)actmtstate->mt_plans[0];
+
+ actplan = (MergeAction *)actPstate->ps.plan;
+ /*the replace action does not fire triggers*/
+ if(actplan->replaced)
+ continue;
+
+ if(actplan->operation == CMD_UPDATE)
+ doUpdateTriggers = true;
+ else if(actplan->operation == CMD_INSERT)
+ doInsertTriggers = true;
+ else if(actplan->operation == CMD_DELETE)
+ doDeleteTriggers = true;
+
+ }
+
+ /*fire the triggers*/
+ if(doUpdateTriggers)
+ ExecASUpdateTriggers(mt_state->ps.state,
+ mt_state->ps.state->es_result_relations);
+
+
+ if(doInsertTriggers)
+ ExecASInsertTriggers(mt_state->ps.state,
+ mt_state->ps.state->es_result_relations);
+
+
+ if(doDeleteTriggers)
+ ExecASDeleteTriggers(mt_state->ps.state,
+ mt_state->ps.state->es_result_relations);
+
+ return;
+}
static HeapTuple
GetTupleForTrigger(EState *estate,
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index b34a154..3a943a2 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -170,6 +170,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/execProcnode.c b/src/backend/executor/execProcnode.c
index f4cc7d9..2d1a4e7 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -153,6 +153,11 @@ ExecInitNode(Plan *node, EState *estate, int eflags)
estate, eflags);
break;
+ case T_MergeAction:
+ result = (PlanState *) ExecInitMergeAction((MergeAction*) node,
+ estate, eflags);
+ break;
+
case T_Append:
result = (PlanState *) ExecInitAppend((Append *) node,
estate, eflags);
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 8619ce3..8ab81ae 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -582,6 +582,143 @@ lreplace:;
return NULL;
}
+static TupleTableSlot *
+MergeRaiseErr(void)
+{
+ elog(NOTICE, "one tuple is ERROR");
+ return NULL;
+}
+
+static TupleTableSlot *
+ExecMerge(ItemPointer tupleid,
+ TupleTableSlot *slot,
+ TupleTableSlot *planSlot,
+ ModifyTableState *node,
+ EState *estate)
+{
+
+ TupleTableSlot *actslot = NULL;
+ TupleTableSlot *res = NULL;
+ ListCell *each;
+
+ /*
+ * try the merge actions one by one
+ */
+ foreach(each, node->mergeActPstates)
+ {
+ ModifyTableState *mt_pstate;
+
+ MergeActionState *action_pstate;
+
+ ExprContext *econtext;
+
+ bool matched;
+
+
+ mt_pstate = (ModifyTableState *)lfirst(each);
+
+ /*
+ * mt_pstate is supposed to have only ONE mt_plans,
+ * which is a MergeActionState
+ */
+ Assert(mt_pstate->mt_nplans == 1);
+
+ action_pstate = (MergeActionState *)mt_pstate->mt_plans[0];
+
+ matched = ((MergeAction *)action_pstate->ps.plan)->matched;
+
+
+ /*
+ * If tupleid == NULL, it is a NOT MATCHED case,
+ * else, it is a MATCHED case,
+ */
+ if((tupleid == NULL && matched) || (tupleid != NULL && !matched))
+ {
+ continue;
+ }
+
+ /*Setup the expression context*/
+ econtext = action_pstate->ps.ps_ExprContext;
+
+ /*
+ If the action has an additional qual,
+ which is not satisfied, skip it
+ */
+ if(action_pstate->ps.qual)
+ {
+ ResetExprContext(econtext);
+
+ econtext->ecxt_scantuple = slot;
+ econtext->ecxt_outertuple = planSlot;
+
+ if(!ExecQual(action_pstate->ps.qual, econtext,false))
+ {
+ continue;
+ }
+ }
+
+
+ /*
+ * OK, the input tuple is caugth by current action.
+ * If this action is "replaced" by rules, we will skip it
+ * AND THE REMAINING ACTIONS.
+ */
+ Assert(IsA(action_pstate->ps.plan, MergeAction));
+ if(((MergeAction *)action_pstate->ps.plan)->replaced)
+ return NULL;
+
+
+ /*Now we start to exec this action.
+ We have 5 action types*/
+
+ /*1. do nothing for a DO NOTHING action*/
+ if(action_pstate->operation == CMD_DONOTHING)
+ return NULL;
+
+ /*2. throw an error for a RAISE ERROR action*/
+ if(action_pstate->operation == CMD_RAISEERR)
+ return MergeRaiseErr();
+
+ /*3. project the result tuple slot, for INSERT/UPDATE action*/
+ if(action_pstate->operation != CMD_DELETE)
+ actslot = ExecProcessReturning(action_pstate->ps.ps_ProjInfo,
+ slot, planSlot);
+
+ switch (action_pstate->operation)
+ {
+ case CMD_INSERT:
+ res = ExecInsert(actslot, planSlot, estate);
+ return res;
+ break;
+ case CMD_UPDATE:
+ res = ExecUpdate(tupleid,
+ actslot,
+ planSlot,
+ &mt_pstate->mt_epqstate,
+ estate);
+ return res;
+ break;
+ case CMD_DELETE:
+ res = ExecDelete(tupleid,
+ planSlot,
+ &mt_pstate->mt_epqstate,
+ estate);
+ return res;
+ break;
+ default:
+ elog(ERROR, "unknown merge action type for excute");
+ break;
+ }
+
+ }
+
+ /*
+ * Here, no action is taken. Let's do the default thing,
+ * which is Raise Error in crrent edition
+ */
+ return MergeRaiseErr();
+
+}
/*
* Process BEFORE EACH STATEMENT triggers
@@ -603,6 +740,9 @@ fireBSTriggers(ModifyTableState *node)
ExecBSDeleteTriggers(node->ps.state,
node->ps.state->es_result_relations);
break;
+ case CMD_MERGE:
+ ExecBSMergeTriggers(node);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -629,6 +769,9 @@ fireASTriggers(ModifyTableState *node)
ExecASDeleteTriggers(node->ps.state,
node->ps.state->es_result_relations);
break;
+ case CMD_MERGE:
+ ExecASMergeTriggers(node);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -708,20 +851,34 @@ ExecModifyTable(ModifyTableState *node)
/*
* extract the 'ctid' junk attribute.
*/
- if (operation == CMD_UPDATE || operation == CMD_DELETE)
+ if (operation == CMD_UPDATE || operation == CMD_DELETE || operation == CMD_MERGE)
{
Datum datum;
bool isNull;
datum = ExecGetJunkAttribute(slot, junkfilter->jf_junkAttNo,
&isNull);
- /* shouldn't ever get a null result... */
+
if (isNull)
- elog(ERROR, "ctid is NULL");
-
- tupleid = (ItemPointer) DatumGetPointer(datum);
- tuple_ctid = *tupleid; /* be sure we don't free the ctid!! */
- tupleid = &tuple_ctid;
+ {
+ /*
+ * shouldn't ever get a null result for update and delete.
+ * Merge command will get a null ctid in "NOT MATCHED" case
+ */
+ if(operation != CMD_MERGE)
+ elog(ERROR, "ctid is NULL");
+ else
+ tupleid = NULL;
+ }
+ else
+ {
+
+ tupleid = (ItemPointer) DatumGetPointer(datum);
+
+ tuple_ctid = *tupleid; /* be sure we don't free the ctid!! */
+ tupleid = &tuple_ctid;
+
+ }
}
/*
@@ -744,6 +901,10 @@ ExecModifyTable(ModifyTableState *node)
slot = ExecDelete(tupleid, planSlot,
&node->mt_epqstate, estate);
break;
+ case CMD_MERGE:
+ slot = ExecMerge(tupleid, slot, planSlot,
+ node, estate);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -771,6 +932,74 @@ ExecModifyTable(ModifyTableState *node)
return NULL;
}
+/*
+* When init a merge plan, we also need init its action plans.
+* These action plans are "MergeAction" plans .
+*
+* This function mainly handles the tlist and qual in the plan.
+* The returning result is a "MergeActionState".
+*/
+MergeActionState *
+ExecInitMergeAction(MergeAction *node, EState *estate, int eflags)
+{
+ MergeActionState *result;
+
+ /*
+ * do nothing when we get to the end of a leaf on tree.
+ */
+ if (node == NULL)
+ return NULL;
+
+ /*
+ * create state structure
+ */
+ result = makeNode(MergeActionState);
+ result->operation = node->operation;
+ result->ps.plan = (Plan *)node;
+ result->ps.state = estate;
+
+ /*
+ * tuple table initialization
+ */
+ ExecInitResultTupleSlot(estate, &result->ps);
+
+ /*
+ * initialize tuple type
+ */
+ ExecAssignResultTypeFromTL(&result->ps);
+
+
+ /*
+ * create expression context for node
+ */
+
+ ExecAssignExprContext(estate, &result->ps);
+
+
+ /*
+ * initialize child expressions
+ */
+ result->ps.targetlist = (List *)
+ ExecInitExpr((Expr *) node->plan.targetlist, &result->ps);
+
+
+ result->ps.qual = (List *)
+ ExecInitExpr((Expr *) node->plan.qual, &result->ps);
+
+
+ /*
+ * init the projection information
+ */
+ ExecAssignProjectionInfo(&result->ps, NULL);
+
+ /*
+ do we need a check for the plan output here ?
+ (by calling the ExecCheckPlanOutput() function
+ */
+
+ return result;
+}
+
/* ----------------------------------------------------------------
* ExecInitModifyTable
* ----------------------------------------------------------------
@@ -786,6 +1015,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
Plan *subplan;
ListCell *l;
int i;
+ bool isMergeAction = false;
/* check for unsupported flags */
Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
@@ -826,6 +1056,16 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
foreach(l, node->plans)
{
subplan = (Plan *) lfirst(l);
+
+ /*
+ * test if this subplan node is a MergeAction.
+ * We need this information for setting the junckfilter.
+ * juckfiler is necessary for an ordinary UPDATE/DELETE plan,
+ * but not for an UPDATE/DELETE merge action
+ */
+ if(IsA(subplan, MergeAction))
+ isMergeAction = true;
+
mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags);
estate->es_result_relation_info++;
i++;
@@ -955,7 +1195,12 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
break;
case CMD_UPDATE:
case CMD_DELETE:
- junk_filter_needed = true;
+ case CMD_MERGE:
+ if(!isMergeAction)
+ junk_filter_needed = true;
+ break;
+ case CMD_DONOTHING:
+ case CMD_RAISEERR:
break;
default:
elog(ERROR, "unknown operation");
@@ -978,9 +1223,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 ctid junk attr now */
+ /* For UPDATE/DELETE/MERGE, find the ctid junk attr now */
j->jf_junkAttNo = ExecFindJunkAttribute(j, "ctid");
if (!AttributeNumberIsValid(j->jf_junkAttNo))
elog(ERROR, "could not find junk ctid column");
@@ -1006,6 +1251,19 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
if (estate->es_trig_tuple_slot == NULL)
estate->es_trig_tuple_slot = ExecInitExtraTupleSlot(estate);
+ /*
+ * for the merge actions, we need to do similar things as above
+ */
+ foreach(l, node->mergeActPlan)
+ {
+ PlanState *actpstate = ExecInitNode((Plan *)lfirst(l), estate, 0);
+ /*
+ * put the pstates of each action into ModifyTableState
+ */
+ mtstate->mergeActPstates = lappend(mtstate->mergeActPstates, actpstate);
+
+ }
+
return mtstate;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 69262d6..1379686 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -176,6 +176,7 @@ _copyModifyTable(ModifyTable *from)
COPY_NODE_FIELD(returningLists);
COPY_NODE_FIELD(rowMarks);
COPY_SCALAR_FIELD(epqParam);
+ COPY_NODE_FIELD(mergeActPlan);
return newnode;
}
@@ -2274,6 +2275,11 @@ _copyQuery(Query *from)
COPY_NODE_FIELD(setOperations);
COPY_NODE_FIELD(constraintDeps);
+ COPY_SCALAR_FIELD(isMergeAction);
+ COPY_SCALAR_FIELD(replaced);
+ /*merge action list*/
+ COPY_NODE_FIELD(mergeActQry);
+
return newnode;
}
@@ -2344,6 +2350,59 @@ _copySelectStmt(SelectStmt *from)
return newnode;
}
+static MergeStmt *
+_copyMergeStmt(MergeStmt *from)
+{
+ MergeStmt *newnode = makeNode(MergeStmt);
+
+ COPY_NODE_FIELD(relation);
+ COPY_NODE_FIELD(source);
+ COPY_NODE_FIELD(matchCondition);
+ COPY_NODE_FIELD(actions);
+
+ return newnode;
+
+}
+
+static MergeConditionAction *
+_copyMergeConditionAction(MergeConditionAction *from)
+{
+ MergeConditionAction *newnode = makeNode(MergeConditionAction);
+
+ COPY_SCALAR_FIELD(match);
+ COPY_NODE_FIELD(condition);
+ COPY_NODE_FIELD(action);
+
+ return newnode;
+}
+
+static MergeUpdate *
+_copyMergeUpdate(MergeUpdate *from)
+{
+ MergeUpdate *newNode = (MergeUpdate *)_copyUpdateStmt((UpdateStmt *) from);
+ newNode->type = T_MergeUpdate;
+
+ return newNode;
+}
+
+static MergeInsert *
+_copyMergeInsert(MergeInsert *from)
+{
+ MergeInsert *newNode = (MergeInsert *)_copyInsertStmt((InsertStmt *) from);
+ newNode->type = T_MergeInsert;
+
+ return newNode;
+}
+
+static MergeDelete *
+_copyMergeDelete(MergeDelete *from)
+{
+ MergeDelete *newNode = (MergeDelete *)_copyDeleteStmt((DeleteStmt *) from);
+ newNode->type = T_MergeDelete;
+
+ return newNode;
+}
+
static SetOperationStmt *
_copySetOperationStmt(SetOperationStmt *from)
{
@@ -3905,6 +3964,21 @@ copyObject(void *from)
case T_SelectStmt:
retval = _copySelectStmt(from);
break;
+ case T_MergeStmt:
+ retval = _copyMergeStmt(from);
+ break;
+ case T_MergeConditionAction:
+ retval = _copyMergeConditionAction(from);
+ break;
+ case T_MergeUpdate:
+ retval = _copyMergeUpdate(from);
+ break;
+ case T_MergeInsert:
+ retval = _copyMergeInsert(from);
+ break;
+ case T_MergeDelete:
+ retval = _copyMergeDelete(from);
+ break;
case T_SetOperationStmt:
retval = _copySetOperationStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 667057b..f726a48 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -879,6 +879,9 @@ _equalQuery(Query *a, Query *b)
COMPARE_NODE_FIELD(setOperations);
COMPARE_NODE_FIELD(constraintDeps);
+ COMPARE_SCALAR_FIELD(isMergeAction);
+ COMPARE_SCALAR_FIELD(replaced);
+ COMPARE_NODE_FIELD(mergeActQry);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 04a6647..c88d422 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -331,6 +331,7 @@ _outModifyTable(StringInfo str, ModifyTable *node)
WRITE_NODE_FIELD(returningLists);
WRITE_NODE_FIELD(rowMarks);
WRITE_INT_FIELD(epqParam);
+ WRITE_NODE_FIELD(mergeActPlan);
}
static void
@@ -2021,6 +2022,53 @@ _outQuery(StringInfo str, Query *node)
WRITE_NODE_FIELD(rowMarks);
WRITE_NODE_FIELD(setOperations);
WRITE_NODE_FIELD(constraintDeps);
+ WRITE_BOOL_FIELD(isMergeAction);
+ WRITE_BOOL_FIELD(matched);
+ WRITE_BOOL_FIELD(replaced);
+ WRITE_NODE_FIELD(mergeActQry);
+}
+
+static void
+_outMergeConditionAction(StringInfo str, MergeConditionAction *node)
+{
+ WRITE_NODE_TYPE("MERGECONDITIONACTION");
+
+ WRITE_BOOL_FIELD(match);
+
+ WRITE_NODE_FIELD(condition);
+ WRITE_NODE_FIELD(action);
+}
+
+static void
+_outMergeStmt(StringInfo str, MergeStmt *node)
+{
+ WRITE_NODE_TYPE("MERGESTMT");
+
+ WRITE_NODE_FIELD(relation);
+ WRITE_NODE_FIELD(source);
+ WRITE_NODE_FIELD(matchCondition);
+ WRITE_NODE_FIELD(actions);
+}
+
+static void
+_outMergeAction(StringInfo str, MergeAction*node)
+{
+ _outPlanInfo(str, (Plan *)node);
+ WRITE_BOOL_FIELD(replaced);
+ WRITE_ENUM_FIELD(operation, CmdType);
+ WRITE_BOOL_FIELD(matched);
+ WRITE_NODE_FIELD(flattenedqual);
+}
+
+static void
+_outDeleteStmt(StringInfo str, DeleteStmt *node)
+{
+ WRITE_NODE_TYPE("DELETESTMT");
+
+ WRITE_NODE_FIELD(relation);
+ WRITE_NODE_FIELD(usingClause);
+ WRITE_NODE_FIELD(whereClause);
+ WRITE_NODE_FIELD(returningList);
}
static void
@@ -2906,6 +2954,18 @@ _outNode(StringInfo str, void *obj)
case T_XmlSerialize:
_outXmlSerialize(str, obj);
break;
+ case T_MergeAction:
+ _outMergeAction(str, obj);
+ break;
+ case T_MergeStmt:
+ _outMergeStmt(str, obj);
+ break;
+ case T_MergeConditionAction:
+ _outMergeConditionAction(str,obj);
+ break;
+ case T_DeleteStmt:
+ _outDeleteStmt(str,obj);
+ break;
default:
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 0a2edcb..a8581a0 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -219,6 +219,10 @@ _readQuery(void)
READ_NODE_FIELD(rowMarks);
READ_NODE_FIELD(setOperations);
READ_NODE_FIELD(constraintDeps);
+ READ_BOOL_FIELD(isMergeAction);
+ READ_BOOL_FIELD(matched);
+ READ_BOOL_FIELD(replaced);
+ READ_NODE_FIELD(mergeActQry);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 3950ab4..d7b3f96 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -102,7 +102,12 @@ static void get_column_info_for_window(PlannerInfo *root, WindowClause *wc,
int *ordNumCols,
AttrNumber **ordColIdx,
Oid **ordOperators);
-
+static ModifyTable *merge_action_planner(PlannerGlobal *glob,
+ Query *parse,
+ Plan *top_plan);
+static void merge_action_list_planner(PlannerGlobal *glob,
+ Query *parse,
+ ModifyTable *mainplan);
/*****************************************************************************
*
@@ -565,6 +570,11 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
returningLists,
rowMarks,
SS_assign_special_param(root));
+
+ /*do a simple plan for each actions in the merge command.
+ *put them in mergeActPlan list;
+ */
+ merge_action_list_planner(glob, parse, (ModifyTable *)plan);
}
}
@@ -584,6 +594,138 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
return plan;
}
+static void
+merge_action_list_planner(PlannerGlobal *glob, Query *parse, ModifyTable *mainplan)
+{
+ ListCell *l;
+
+ /*this is a function for MERGE command only*/
+ if(parse->commandType != CMD_MERGE ||
+ mainplan->operation != CMD_MERGE)
+ return;
+
+ /*if the merge actions are already there, no need to do it again*/
+ if(mainplan->mergeActPlan != NIL)
+ return;
+
+ /*plan each action query*/
+ foreach(l, parse->mergeActQry)
+ {
+ Plan *actplan = (Plan *)merge_action_planner(glob,
+ (Query *)lfirst(l),
+ (Plan *)linitial(mainplan->plans)
+ );
+
+ mainplan->mergeActPlan = lappend(mainplan->mergeActPlan, actplan);
+ }
+
+ return;
+}
+
+/*create plan for a single merge action*/
+static ModifyTable *
+merge_action_planner(PlannerGlobal *glob, Query *parse,
+ Plan *top_plan)
+{
+ PlannerInfo *root;
+ MergeAction *actplan;
+ ModifyTable *result;
+
+ List *returningLists;
+ List *rowMarks;
+
+ /*
+ * no having clause in a merge action
+ */
+ Assert(parse->havingQual == NULL);
+
+
+ /* Create a PlannerInfo data structure for this subquery */
+ root = makeNode(PlannerInfo);
+ root->parse = parse;
+ root->glob = glob;
+ root->query_level = 1;
+ root->parent_root = NULL;
+ root->planner_cxt = CurrentMemoryContext;
+ root->init_plans = NIL;
+ root->cte_plan_ids = NIL;
+ root->eq_classes = NIL;
+ root->append_rel_list = NIL;
+ root->hasPseudoConstantQuals = false;
+ root->hasRecursion = false;
+ root->wt_param_id = -1;
+ root->non_recursive_plan = NULL;
+
+
+ /*
+ * Create the action plan node
+ */
+ actplan = makeNode(MergeAction);
+ actplan->operation = parse->commandType;
+ actplan->replaced = parse->replaced;
+ actplan->matched = parse->matched;
+
+ /*
+ * Do expression preprocessing on targetlist and quals.
+ */
+ parse->targetList = (List *)
+ preprocess_expression(root, (Node *) parse->targetList,
+ EXPRKIND_TARGET);
+
+ preprocess_qual_conditions(root, (Node *) parse->jointree);
+
+
+ /*
+ * we need a flat qual for explaining
+ */
+ actplan->flattenedqual = (List *)flatten_join_alias_vars(root, parse->jointree->quals);
+
+ /*copy the cost from the top_plan*/
+ actplan->plan.startup_cost = top_plan->startup_cost;
+ actplan->plan.total_cost = top_plan->total_cost;
+ actplan->plan.plan_rows = top_plan->plan_rows;
+ actplan->plan.plan_width = top_plan->plan_width;
+
+ /*
+ * prepare the result
+ */
+ if(parse->targetList)
+ actplan->plan.targetlist = preprocess_targetlist(root,parse->targetList);
+
+ actplan->plan.qual = (List *)parse->jointree->quals;
+ push_up_merge_action_vars(actplan, parse);
+
+ if (parse->returningList)
+ {
+ List *rlist;
+
+ Assert(parse->resultRelation);
+ rlist = set_returning_clause_references(root->glob,
+ parse->returningList,
+ &actplan->plan,
+ parse->resultRelation);
+ returningLists = list_make1(rlist);
+ }
+ else
+ returningLists = NIL;
+
+
+ if (parse->rowMarks)
+ rowMarks = NIL;
+ else
+ rowMarks = root->rowMarks;
+
+
+ result = make_modifytable(parse->commandType,
+ list_make1_int(parse->resultRelation),
+ list_make1(actplan),
+ returningLists,
+ rowMarks,
+ SS_assign_special_param(root));
+
+ return result;
+}
+
/*
* preprocess_expression
* Do subquery_planner's preprocessing work for an expression,
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index 59d3518..b4514b8 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -78,13 +78,23 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
result_relation, range_table);
/*
- * for "update" and "delete" queries, add ctid of the result relation into
- * the target list so that the ctid will propagate through execution and
- * ExecutePlan() will be able to identify the right tuple to replace or
- * delete. This extra field is marked "junk" so that it is not stored
+ * for "update" , "delete" and "merge" queries, add ctid of the result
+ * relation into the target list so that the ctid will propagate through
+ * execution and ExecutePlan() will be able to identify the right tuple
+ * to replace or delete.
+ * This extra field is marked "junk" so that it is not stored
* back into the tuple.
+ *
+ * BUT, if the query node is a merge action,
+ * we don't need to expend the ctid attribute in tlist.
+ * The tlist of the merge top level plan already contains
+ * a "ctid" junk attr of the target relation.
*/
- if (command_type == CMD_UPDATE || command_type == CMD_DELETE)
+
+ if(!parse->isMergeAction &&
+ (command_type == CMD_UPDATE ||
+ command_type == CMD_DELETE ||
+ command_type == CMD_MERGE))
{
TargetEntry *tle;
Var *var;
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 92c2208..ffff863 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -67,6 +67,16 @@ typedef struct
bool inserted_sublink; /* have we inserted a SubLink? */
} flatten_join_alias_vars_context;
+typedef struct
+{
+ int varno_source;
+ int varno_target;
+ int varno_join;
+
+ int offset_source;
+ int offset_target;
+} push_up_merge_action_vars_context;
+
static bool pull_varnos_walker(Node *node,
pull_varnos_context *context);
static bool pull_varattnos_walker(Node *node, Bitmapset **varattnos);
@@ -83,6 +93,8 @@ static bool pull_var_clause_walker(Node *node,
static Node *flatten_join_alias_vars_mutator(Node *node,
flatten_join_alias_vars_context *context);
static Relids alias_relid_set(PlannerInfo *root, Relids relids);
+static bool push_up_merge_action_vars_walker(Node *node,
+ push_up_merge_action_vars_context *context);
/*
@@ -677,6 +689,91 @@ pull_var_clause_walker(Node *node, pull_var_clause_context *context)
(void *) context);
}
+/*
+* When prepare for the MERGE command, we have made a
+* left join between the Source table and target table as the
+* main plan.
+*
+* In this case, the range table contains ONLY THREE range table entries:
+* 1. the source table, which may be a subquery or a plain table
+* 2. the entry of the targe table, which is a plain table
+* 3. join expression with the sourse table and target table as its parameters.
+*
+* Each merge action of the command has its own query and
+* plan nodes as well. And, the vars in its target list and qual
+* expressions may refers to the attribute in any one of the above 3
+* range table entries.
+*
+* However, since the result tuple slots of merge actions are
+* projected from the returned tuple of the join, we need to
+* mapping the vars of source table and target table to their
+* corresponding attributes in the third range table entry.
+*
+* This function does the opposit of the flatten_join_alias_vars()
+* function. It walks through the target list and qual of a
+* MergeAction plan, changes the vars' varno and varattno to the
+* corresponding position in the upper level join RTE.
+*/
+void
+push_up_merge_action_vars(MergeAction *actplan, Query *actqry)
+{
+ push_up_merge_action_vars_context context;
+ RangeTblEntry *source_rte = rt_fetch(1,actqry->rtable);
+
+
+ /*
+ * We are supposed to do a more careful assingment
+ * of the values in context
+ * But lets take a shortcut for simple.
+ */
+ context.varno_source = 1;
+ context.varno_target = 2;
+ context.varno_join = 3;
+
+ context.offset_source = 0;
+
+
+ context.offset_target = list_length(source_rte->eref->colnames);
+
+ push_up_merge_action_vars_walker((Node *)actplan->plan.targetlist, &context);
+
+ push_up_merge_action_vars_walker((Node *)actplan->plan.qual, &context);
+
+}
+
+static bool
+push_up_merge_action_vars_walker(Node *node,
+ push_up_merge_action_vars_context *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *)node;
+
+ if(var->varno == context->varno_source)
+ {
+ var->varno = context->varno_join;
+ var->varattno += context->offset_source;
+ return false;
+ }
+ else if(var->varno == context->varno_target)
+ {
+ var->varno = context->varno_join;
+ var->varattno += context->offset_target;
+ return false;
+ }
+ else if(var->varno == context->varno_join)
+ return false;
+ else
+ elog(ERROR, "the vars in merge action tlist of qual should only belongs to the source table or targe table");
+
+
+ }
+
+ return expression_tree_walker(node, push_up_merge_action_vars_walker,
+ (void *) context);
+}
/*
* flatten_join_alias_vars
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6b99a10..2bc4b84 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -47,6 +47,7 @@ static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt);
static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static List *transformInsertRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos);
+static Query *transformMergeStmt(ParseState *pstate, MergeStmt *stmt);
static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt);
static Query *transformValuesClause(ParseState *pstate, SelectStmt *stmt);
static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
@@ -164,17 +165,24 @@ transformStmt(ParseState *pstate, Node *parseTree)
* Optimizable statements
*/
case T_InsertStmt:
+ case T_MergeInsert:
result = transformInsertStmt(pstate, (InsertStmt *) parseTree);
break;
case T_DeleteStmt:
+ case T_MergeDelete:
result = transformDeleteStmt(pstate, (DeleteStmt *) parseTree);
break;
case T_UpdateStmt:
+ case T_MergeUpdate:
result = transformUpdateStmt(pstate, (UpdateStmt *) parseTree);
break;
+ case T_MergeStmt:
+ result = transformMergeStmt(pstate, (MergeStmt *)parseTree);
+ break;
+
case T_SelectStmt:
{
SelectStmt *n = (SelectStmt *) parseTree;
@@ -245,6 +253,7 @@ analyze_requires_snapshot(Node *parseTree)
case T_DeleteStmt:
case T_UpdateStmt:
case T_SelectStmt:
+ case T_MergeStmt:
result = true;
break;
@@ -282,22 +291,28 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qry->commandType = CMD_DELETE;
- /* set up range table with just the result rel */
- qry->resultRelation = setTargetTable(pstate, stmt->relation,
- interpretInhOption(stmt->relation->inhOpt),
- true,
- ACL_DELETE);
-
qry->distinctClause = NIL;
/*
- * The USING clause is non-standard SQL syntax, and is equivalent in
- * functionality to the FROM list that can be specified for UPDATE. The
- * USING keyword is used rather than FROM because FROM is already a
- * keyword in the DELETE syntax.
- */
- transformFromClause(pstate, stmt->usingClause);
-
+ * The input stmt could be a MergeDelete node.
+ * In this case, we don't need the process on range table.
+ */
+ if(IsA(stmt, DeleteStmt))
+ {
+ /* set up range table with just the result rel */
+ qry->resultRelation = setTargetTable(pstate, stmt->relation,
+ interpretInhOption(stmt->relation->inhOpt),
+ true,
+ ACL_DELETE);
+ /*
+ * The USING clause is non-standard SQL syntax, and is equivalent in
+ * functionality to the FROM list that can be specified for UPDATE. The
+ * USING keyword is used rather than FROM because FROM is already a
+ * keyword in the DELETE syntax.
+ */
+ transformFromClause(pstate, stmt->usingClause);
+ }
+
qual = transformWhereClause(pstate, stmt->whereClause, "WHERE");
qry->returningList = transformReturningList(pstate, stmt->returningList);
@@ -347,6 +362,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
* VALUES list, or general SELECT input. We special-case VALUES, both for
* efficiency and so we can handle DEFAULT specifications.
*/
+
+ /*a MergeInsert statment is always a VALUE clause*/
isGeneralSelect = (selectStmt && selectStmt->valuesLists == NIL);
/*
@@ -382,7 +399,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
* mentioned in the SELECT part. Note that the target table is not added
* to the joinlist or namespace.
*/
- qry->resultRelation = setTargetTable(pstate, stmt->relation,
+ if(IsA(stmt,InsertStmt))/*for MergeInsert, no need to do this*/
+ qry->resultRelation = setTargetTable(pstate, stmt->relation,
false, false, ACL_INSERT);
/* Validate stmt->cols list, or build default list if no list given */
@@ -1730,16 +1748,19 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qry->commandType = CMD_UPDATE;
pstate->p_is_update = true;
- qry->resultRelation = setTargetTable(pstate, stmt->relation,
- interpretInhOption(stmt->relation->inhOpt),
- true,
- ACL_UPDATE);
+ if(IsA(stmt, UpdateStmt))/*for MergeUpdate, no need to do this*/
+ {
+ qry->resultRelation = setTargetTable(pstate, stmt->relation,
+ interpretInhOption(stmt->relation->inhOpt),
+ true,
+ ACL_UPDATE);
- /*
- * the FROM clause is non-standard SQL syntax. We used to be able to do
- * this with REPLACE in POSTQUEL so we keep the feature.
- */
- transformFromClause(pstate, stmt->fromClause);
+ /*
+ * the FROM clause is non-standard SQL syntax. We used to be able to do
+ * this with REPLACE in POSTQUEL so we keep the feature.
+ */
+ transformFromClause(pstate, stmt->fromClause);
+ }
qry->targetList = transformTargetList(pstate, stmt->targetList);
@@ -2241,3 +2262,389 @@ applyLockingClause(Query *qry, Index rtindex,
rc->pushedDown = pushedDown;
qry->rowMarks = lappend(qry->rowMarks, rc);
}
+
+/*
+transform an action of merge command into a query.
+No change of the pstate range table is allowed in this function.
+*/
+static Query *
+transformMergeActions(ParseState *pstate, MergeStmt *stmt, MergeConditionAction *condact)
+{
+ Query *actqry;
+
+ /*
+ * firstly, we need to make sure that DELETE and UPDATE
+ * actions are only taken in MATCHED condition,
+ * and INSERTs are only takend when not MATCHED
+ */
+
+ switch(condact->action->type)
+ {
+ case T_MergeDelete:/*a delete action*/
+ {
+ MergeDelete *deleteact = (MergeDelete *)(condact->action);
+ Assert(IsA(deleteact,MergeDelete));
+
+ if(!condact->match)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("The DELETE action in MERGE command is not allowed when NOT MATCHED")));
+
+ /*put new right code to the result relaion.
+ This line chages the RTE in range table directly*/
+ pstate->p_target_rangetblentry->requiredPerms |= ACL_DELETE;
+
+ deleteact->relation = stmt->relation;
+ deleteact->usingClause = stmt->source;
+ deleteact->whereClause = condact->condition;;
+
+ /*parse the action query*/
+ actqry = transformStmt(pstate, (Node *)deleteact);
+
+ if(!IsA(actqry, Query) ||
+ actqry->commandType != CMD_DELETE ||
+ actqry->utilityStmt != NULL)
+ elog(ERROR, "improper DELETE action in merge stmt");
+
+ actqry->isMergeAction = true;
+ actqry->matched = condact->match;
+
+ return actqry;
+ }
+ break;
+ case T_MergeUpdate:/*an update action*/
+ {
+ MergeUpdate *updateact = (MergeUpdate *)(condact->action);
+ Assert(IsA(updateact,MergeUpdate));
+
+
+ if(!condact->match)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("The UPDATE action in MERGE command is not allowed when NOT MATCHED")));
+
+ pstate->p_target_rangetblentry->requiredPerms |= ACL_UPDATE;
+
+
+ /*the "targetlist" of the updateact is filled in the parser */
+ updateact->relation = stmt->relation;
+ updateact->fromClause = stmt->source;
+ updateact->whereClause = condact->condition;
+
+ /*parse the action query*/
+ actqry = transformStmt(pstate, (Node *)updateact);
+
+ if(!IsA(actqry, Query) ||
+ actqry->commandType != CMD_UPDATE||
+ actqry->utilityStmt != NULL)
+ elog(ERROR, "improper UPDATE action in merge stmt");
+
+ actqry->isMergeAction = true;
+ actqry->matched = condact->match;
+
+ return actqry;
+ }
+ break;
+ case T_MergeInsert:/*an insert action*/
+ {
+ MergeInsert *insertact = (MergeInsert *)(condact->action);
+ Assert(IsA(insertact,MergeInsert));
+
+ if(condact->match)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("The INSERT action in MERGE command is not allowed when MATCHED")));
+
+
+ pstate->p_target_rangetblentry->requiredPerms |= ACL_INSERT;
+
+ /*the "cols" and "selectStmt" of the insertact is filled in the parser */
+ insertact->relation = stmt->relation;
+
+ /*
+ the merge insert action has a strange feature.
+ In an ordinary INSERT, the VALUES list can only
+ contains constants and DEFAULT. (am I right??)
+ But in the INSERT action of MERGE command,
+ the VALUES list can have expressions with
+ variables(attributes of the targe and source tables).
+ Besides, in the ordinary INSERT, a VALUES list can
+ never be followed by a WHERE clause.
+ But in MERGE INSERT action, there are matching conditions.
+
+ Thus, the output qry of this function is an INSERT
+ query in the style of "INSERT...VALUES...",
+ except that we have other range tables and a WHERE clause.
+ Note that it is also different from the "INSERT ... SELECT..."
+ query, in which the whole SELECT is a subquery.
+ (We don't have subquery here).
+
+ We construct this novel query structure in order
+ to keep consitency with other merge action types
+ (DELETE, UPDATE). In this way, all the merge action
+ queries are in fact share the very same Range Table,
+ They only differs in their target lists and join trees
+ */
+
+
+ /*parse the action query, this will call
+ transformInsertStmt() which analyzes the VALUES list.*/
+ actqry = transformStmt(pstate, (Node *)insertact);
+
+ /*do the WHERE clause here, Since the
+ transformInsertStmt() function only analyzes
+ the VALUES list but not the WHERE clause*/
+ actqry->jointree = makeFromExpr(pstate->p_joinlist,
+ transformWhereClause(pstate,
+ condact->condition,
+ "WHERE"));
+
+ if(!IsA(actqry, Query) ||
+ actqry->commandType != CMD_INSERT||
+ actqry->utilityStmt != NULL)
+ elog(ERROR, "improper INSERT action in merge stmt");
+
+ actqry->isMergeAction = true;
+ actqry->matched = condact->match;
+
+ return actqry;
+ }
+ break;
+ case T_MergeDoNothing:
+ {
+ MergeDoNothing *nothingact = (MergeDoNothing *)(condact->action);
+
+ Assert(IsA(nothingact,MergeDoNothing));
+
+ actqry = makeNode(Query);
+
+ actqry->jointree = makeFromExpr(pstate->p_joinlist,
+ transformWhereClause(pstate,
+ condact->condition,
+ "WHERE"));
+
+ actqry->rtable = pstate->p_rtable;
+
+ actqry->commandType = CMD_DONOTHING;
+ actqry->isMergeAction = true;
+ actqry->matched = condact->match;
+
+ return actqry;
+ }
+ break;
+ case T_MergeError:
+ {
+ MergeError *erract = (MergeError *)(condact->action);
+ Assert(IsA(erract,MergeError));
+
+ actqry = makeNode(Query);
+
+ actqry->jointree = makeFromExpr(pstate->p_joinlist,
+ transformWhereClause(pstate,
+ condact->condition,
+ "WHERE"));
+
+ actqry->rtable = pstate->p_rtable;
+
+ actqry->commandType = CMD_RAISEERR;
+ actqry->isMergeAction = true;
+ actqry->matched = condact->match;
+
+ return actqry;
+ }
+ break;
+ default:
+ elog(ERROR, "unknown MERGE action type %d", condact->action->type);
+ break;
+
+ }
+
+ /*never comes here*/
+ return NULL;
+}
+
+static Query *
+transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
+{
+ Query *qry;
+
+ ColumnRef *starRef;
+ ResTarget *starResTarget;
+ ListCell *act;
+ ListCell *l;
+ JoinExpr *joinexp;
+ int rtindex;
+
+ /*The source list has only one element*/
+ if(list_length(stmt->source) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("now we only accept merge command with only ONE source table")));
+
+ /*now, do the real tranformation of the merge command. */
+ qry = makeNode(Query);
+ qry->commandType = CMD_MERGE;
+
+ /*
+ What we are doing here is to create a query like
+ "SELECT * FROM <source_rel> LEFT JOIN <target_rel> ON <match_condition>;"
+
+ Note:
+ 1. we set the "match condition" as the join qualification.
+ The left join will scan both the matched and non-matched tuples.
+
+ 2. a normal SELECT query has no "target relation".
+ But here we need to set the targe relation in query,
+ like the UPDATE/DELETE/INSERT queries.
+ So this is a left join SELECT with a "target table" in its range table.
+
+ 3. We don't have a specific ACL level for Merge, here we just use
+ ACL_SELECT.
+ But we will add other ACL levels when handle each merge actions.
+ */
+
+
+ /*before analyze the FROM clause, we need to set the target table.
+ We cannot call setTargetTable() function directly.
+ We only need the lock target relation, without adding it to Range table.
+ */
+ setTargetTableLock(pstate, stmt->relation);
+
+ /*create the FROM clause. Make the join expression first*/
+ joinexp = makeNode(JoinExpr);
+ joinexp->jointype = JOIN_LEFT;
+ joinexp->isNatural = FALSE;
+ /*source list has only one element*/
+ joinexp->larg = linitial(stmt->source);
+ joinexp->rarg = (Node *)stmt->relation;
+ /*match condtion*/
+ joinexp->quals = stmt->matchCondition;
+
+ /*transform the FROM clause. The target relation and
+ source relation will be add to Rtable here. */
+ transformFromClause(pstate, list_make1(joinexp));
+
+ /*the targetList of the main query is "*" */
+ starRef = makeNode(ColumnRef);
+ starRef->fields = list_make1(makeNode(A_Star));
+ starRef->location = 1;
+
+ starResTarget = makeNode(ResTarget);
+ starResTarget->name = NULL;
+ starResTarget->indirection = NIL;
+ starResTarget->val = (Node *)starRef;
+ starResTarget->location = 1;
+
+ qry->targetList = transformTargetList(pstate, list_make1(starResTarget));
+
+ /*we don't need the WHERE clause here. Set it null. */
+ qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
+
+ /*now , we find out the RTE for the target relation,
+ and do some unfinished jobs*/
+ rtindex = 1;
+ foreach(l, pstate->p_rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *)lfirst(l);
+ if(rte->relid == pstate->p_target_relation->rd_id)
+ {
+ /*find the RTE*/
+ pstate->p_target_rangetblentry = rte;
+ rte->requiredPerms = ACL_SELECT;
+ rte->inh = false;
+ qry->resultRelation = rtindex;
+ break;
+ }
+ rtindex++;
+ }
+
+ if(pstate->p_target_rangetblentry == NULL)
+ elog(ERROR, "cannot find the RTE for target table");
+
+
+ qry->rtable = pstate->p_rtable;
+
+ qry->hasSubLinks = pstate->p_hasSubLinks;
+
+ /*
+ * Top-level aggregates are simply disallowed in MERGE
+ */
+ if (pstate->p_hasAggs)
+ ereport(ERROR,
+ (errcode(ERRCODE_GROUPING_ERROR),
+ errmsg("cannot use aggregate function in top level of MERGE"),
+ parser_errposition(pstate,
+ locate_agg_of_level((Node *) qry, 0))));
+ if (pstate->p_hasWindowFuncs)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use window function in MERGE"),
+ parser_errposition(pstate,
+ locate_windowfunc((Node *) qry))));
+
+#if 0
+ /*
+ the main query is done. Ready for tansform actions.
+
+ Firstly, we check the last action of the action list.
+ If it is not a DO NOTING action, we need to generate
+ an INSERT DEFAULT VALUES action and append it to action list.
+ */
+ lastaction = (MergeConditionAction *)llast(stmt->actions);
+
+ if(lastaction->action == NULL)
+ {
+ /*
+ we have a do nothing action here,
+ What we need to do is just delete it from action list
+ */
+ stmt->actions = list_truncate(stmt->actions,
+ list_length(stmt->actions) - 1);
+ }
+ else
+ {
+ /*
+ The last action is no the DO NOTHING action,
+ we need to generate an INSERT action.
+ */
+ lastaction = makeNode(MergeConditionAction);
+
+ lastaction->condition = NULL;
+ lastaction->match = NOT;
+ lastaction->action = makeNode(MergeInsert);
+
+ /*nothing need to be filled into the node*/
+
+ stmt->actions = lappend(stmt->actions, lastaction);
+ }
+#endif
+
+ /*
+ For each actions ,we transform it to a seperate query.
+ the action queries shares the exactly same
+ range table with the main query.
+
+ In other words, in the extra condtions of the sub actions,
+ we don't allow involvement of new tables
+ */
+ qry->mergeActQry = NIL;
+
+ foreach(act,stmt->actions)
+ {
+ MergeConditionAction *mca = (MergeConditionAction *)lfirst(act);
+ Query *actqry;
+
+ /*transform the act (and its condition) as a single query. */
+ actqry = transformMergeActions(pstate, stmt, mca);
+
+ /*since we don't invoke setTargetTable() in transformMergeActions(),
+ we need to set actqry->resultRelation here
+ */
+ actqry->resultRelation = qry->resultRelation;
+
+ /*put it into the list*/
+ qry->mergeActQry = lappend(qry->mergeActQry, actqry);
+ }
+
+ return qry;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index aab7789..ebe39d9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -212,6 +212,10 @@ static TypeName *TableFuncTypeName(List *columns);
DropOwnedStmt ReassignOwnedStmt
AlterTSConfigurationStmt AlterTSDictionaryStmt
+%type <node> MergeStmt opt_and_condition merge_condition_action merge_action
+%type <boolean> opt_not
+%type <list> merge_condition_action_list
+
%type <node> select_no_parens select_with_parens select_clause
simple_select values_clause
@@ -505,6 +509,8 @@ static TypeName *TableFuncTypeName(List *columns);
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
+ MATCHED MERGE RAISE ERROR_P
+
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
@@ -726,6 +732,7 @@ stmt :
| ListenStmt
| LoadStmt
| LockStmt
+ | MergeStmt
| NotifyStmt
| PrepareStmt
| ReassignOwnedStmt
@@ -6986,6 +6993,7 @@ ExplainableStmt:
| InsertStmt
| UpdateStmt
| DeleteStmt
+ | MergeStmt
| DeclareCursorStmt
| CreateAsStmt
| ExecuteStmt /* by default all are $$=$1 */
@@ -7331,6 +7339,114 @@ set_target_list:
/*****************************************************************************
*
* QUERY:
+ * MERGE STATEMENT
+ *
+ *****************************************************************************/
+
+
+MergeStmt:
+ MERGE INTO relation_expr_opt_alias
+ USING table_ref
+ ON a_expr
+ merge_condition_action_list
+ {
+ MergeStmt *m = makeNode(MergeStmt);
+
+ m->relation = $3;
+
+ /*although we have only one USING table,
+ we still make it a list, maybe in future
+ we will allow mutliple USING tables.*/
+ m->matchCondition = $7;
+ m->source = list_make1($5);
+ m->actions = $8;
+
+ $$ = (Node *)m;
+ }
+ ;
+
+merge_condition_action_list:
+ merge_condition_action
+ { $$ = list_make1($1); }
+ | merge_condition_action_list merge_condition_action
+ { $$ = lappend($1,$2); }
+ ;
+
+merge_condition_action:
+ WHEN opt_not MATCHED opt_and_condition THEN merge_action
+ {
+ MergeConditionAction *m = makeNode(MergeConditionAction);
+
+ m->match = $2;
+ m->condition = $4;
+ m->action = $6;
+
+ $$ = (Node *)m;
+ }
+ ;
+
+
+opt_and_condition:
+ AND a_expr {$$ = $2;}
+ | /*EMPTY*/ {$$ = NULL;}
+ ;
+
+opt_not:
+ NOT {$$ = false;}
+ | /*EMPTY*/ {$$ = true;}
+ ;
+
+merge_action:
+ DELETE_P
+ {
+ $$ = (Node *)makeNode(MergeDelete);
+ }
+ | UPDATE SET set_clause_list
+ {
+ UpdateStmt *n = makeNode(MergeUpdate);
+ n->targetList = $3;
+ $$ = (Node *)n;
+ }
+ | INSERT values_clause
+ {
+ InsertStmt *n = makeNode(MergeInsert);
+ n->cols = NIL;
+ n->selectStmt = $2;
+
+ $$ = (Node *)n;
+ }
+
+ | INSERT '(' insert_column_list ')' values_clause
+ {
+ InsertStmt *n = makeNode(MergeInsert);
+ n->cols = $3;
+ n->selectStmt = $5;
+
+ $$ = (Node *)n;
+ }
+ | INSERT DEFAULT VALUES
+ {
+ InsertStmt *n = makeNode(MergeInsert);
+ n->cols = NIL;
+ n->selectStmt = NULL;
+
+ $$ = (Node *)n;
+ }
+ | DO NOTHING
+ {
+ $$ = (Node *)makeNode(MergeDoNothing);
+ }
+ | RAISE ERROR_P
+ {
+ $$ = (Node *)makeNode(MergeError);
+ }
+ ;
+
+
+
+/*****************************************************************************
+ *
+ * QUERY:
* CURSOR STATEMENTS
*
*****************************************************************************/
@@ -10952,6 +11068,7 @@ unreserved_keyword:
| ENCODING
| ENCRYPTED
| ENUM_P
+ | ERROR_P
| ESCAPE
| EXCLUDE
| EXCLUDING
@@ -11005,7 +11122,9 @@ unreserved_keyword:
| LOGIN_P
| MAPPING
| MATCH
+ | MATCHED
| MAXVALUE
+ | MERGE
| MINUTE_P
| MINVALUE
| MODE
@@ -11048,6 +11167,7 @@ unreserved_keyword:
| PROCEDURAL
| PROCEDURE
| QUOTE
+ | RAISE
| RANGE
| READ
| REASSIGN
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 16ca583..8f2ec6b 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -214,6 +214,25 @@ setTargetTable(ParseState *pstate, RangeVar *relation,
return rtindex;
}
+void
+setTargetTableLock(ParseState *pstate, RangeVar *relation)
+{
+
+ /* Close old target; this could only happen for multi-action rules */
+ if (pstate->p_target_relation != NULL)
+ heap_close(pstate->p_target_relation, NoLock);
+
+ /*
+ * 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);
+}
+
/*
* Simplify InhOption (yes/no/default) into boolean yes/no.
*
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 25b44dd..3ee0428 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1836,6 +1836,41 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
return rewritten;
}
+/*if the merge action type has already been processed by rewriter*/
+#define insert_rewrite (1<<0)
+#define delete_rewrite (1<<1)
+#define update_rewrite (1<<2)
+
+/*if the merge action type is fully replace by rules.*/
+#define insert_instead (1<<3)
+#define delete_instead (1<<4)
+#define update_instead (1<<5)
+
+#define merge_action_already_rewrite(acttype, flag) \
+ ((acttype == CMD_INSERT && (flag & insert_rewrite)) || \
+ (acttype == CMD_UPDATE && (flag & update_rewrite)) || \
+ (acttype == CMD_DELETE && (flag & delete_rewrite)))
+
+#define set_action_rewrite(acttype, flag) \
+ if(acttype == CMD_INSERT) \
+ {flag |= insert_rewrite;}\
+ else if(acttype == CMD_UPDATE) \
+ {flag |= update_rewrite;}\
+ else if(acttype == CMD_DELETE) \
+ {flag |= delete_rewrite;}
+
+#define merge_action_instead(acttype, flag) \
+ ((acttype == CMD_INSERT && (flag & insert_instead)) || \
+ (acttype == CMD_UPDATE && (flag & update_instead)) || \
+ (acttype == CMD_DELETE && (flag & delete_instead)))
+
+#define set_action_instead(acttype, flag)\
+ if(acttype == CMD_INSERT) \
+ {flag |= insert_instead;}\
+ else if(acttype == CMD_UPDATE) \
+ {flag |= update_instead;}\
+ else if(acttype == CMD_DELETE) \
+ {flag |= delete_instead;}
/*
* QueryRewrite -
@@ -1861,7 +1896,151 @@ QueryRewrite(Query *parsetree)
*
* Apply all non-SELECT rules possibly getting 0 or many queries
*/
- querylist = RewriteQuery(parsetree, NIL);
+ if(parsetree->commandType == CMD_MERGE)
+ {
+ /*
+ *for merge query, we have a set of action queries (not subquery).
+ *each of these action queries should be applied to RewriteQuery().
+ */
+ ListCell *l;
+
+ int flag = 0;
+
+ List *pre_qry = NIL;
+ List *post_qry = NIL;
+
+
+ querylist = NIL;
+
+
+ /*rewrite the merge action queries one by one.*/
+ foreach(l, parsetree->mergeActQry)
+ {
+ List *queryList4action = NIL;
+ Query *actionqry;
+ Query *q;
+
+
+ actionqry = lfirst(l);
+
+ /*
+ * no rewriting for DO NOTHING or ERROR
+ */
+ if(actionqry->commandType == CMD_DONOTHING ||
+ actionqry->commandType == CMD_RAISEERR)
+ continue;
+
+
+ /*
+ *if this kind of actions are fully replaced by rules,
+ *we mark it as "replaced"
+ */
+ if(merge_action_instead(actionqry->commandType, flag))
+ {
+ /*
+ *Still need to call RewriteQuery(),
+ *since we need the process on target list and so on.
+ *BUT, the returned list is discarded
+ */
+ RewriteQuery(actionqry, NIL);
+ actionqry->replaced = true;
+ continue;
+ }
+
+
+ /*if this kind of actions are already processed by rewriter, skip it.*/
+ if(merge_action_already_rewrite(actionqry->commandType, flag))
+ {
+ RewriteQuery(actionqry, NIL);
+ continue;
+ }
+
+ /*ok this action has not been processed before, let's do it now.*/
+ queryList4action = RewriteQuery(actionqry, NIL);
+
+ /*this kind of actions has been processed, set the flag*/
+ set_action_rewrite(actionqry->commandType,flag);
+
+ /*if the returning list is nil, this merge action
+ is replaced by a do-nothing rule*/
+ if(queryList4action == NIL)
+ {
+ /*set the flag for other merge actions of the same type*/
+ set_action_instead(actionqry->commandType, flag);
+ actionqry->replaced = true;
+ continue;
+ }
+
+ /*
+ * if the rewriter return a non-NIL list, the merge action query
+ *could be one element in it.
+ *if so, it must be the head (for INSERT acton)
+ *or tail (for UPDATE/DELETE action).
+ */
+
+ /*test the list head*/
+ q = (Query *)linitial(queryList4action);
+ if(q->querySource == QSRC_ORIGINAL)
+ {
+ /*
+ *the merge action is the head, the remaining part
+ *of the list are the queries generated by rules
+ *we put them in the post_qry list.
+ */
+ if(querylist == NIL)
+ querylist = list_make1(parsetree);
+
+
+ queryList4action = list_delete_first(queryList4action);
+ post_qry = list_concat(post_qry,queryList4action);
+
+ continue;
+
+ }
+
+ /*test the list tail*/
+ q = (Query *)llast(queryList4action);
+ if(q->querySource == QSRC_ORIGINAL)
+ {
+ /*the merge action is the tail.
+ Put the rule queries in pre_qry list*/
+ if(querylist == NIL)
+ querylist = list_make1(parsetree);
+
+ queryList4action = list_truncate(queryList4action,list_length(queryList4action)-1);
+
+ pre_qry = list_concat(pre_qry,queryList4action);
+ continue;
+
+ }
+
+ /*here, the merge action query is not in the rewriten query list,
+ *which means the action is replaced by INSTEAD rule(s).
+ *We need to mark it as "replaced".
+
+ For a INSERT action, we put the rule queries in the post list
+ otherwise, in the pre list
+ */
+ if(actionqry->commandType == CMD_INSERT)
+ post_qry = list_concat(post_qry,queryList4action);
+ else
+ pre_qry = list_concat(pre_qry,queryList4action);
+
+ set_action_instead(actionqry->commandType, flag);
+ actionqry->replaced = true;
+ }
+
+ /*finally, put the 3 lists into one.
+ *If all the merge actions are replaced by rules,
+ *the original merge query
+ *will not be involved in the querylist.
+ */
+ querylist = list_concat(pre_qry,querylist);
+ querylist = list_concat(querylist, post_qry);
+
+ }
+ else
+ querylist = RewriteQuery(parsetree, NIL);
/*
* Step 2
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 8ad4915..0dc3117 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -225,6 +225,10 @@ ProcessQuery(PlannedStmt *plan,
snprintf(completionTag, COMPLETION_TAG_BUFSIZE,
"DELETE %u", queryDesc->estate->es_processed);
break;
+ case CMD_MERGE:
+ snprintf(completionTag, COMPLETION_TAG_BUFSIZE,
+ "MERGE %u", queryDesc->estate->es_processed);
+ break;
default:
strcpy(completionTag, "???");
break;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 1815539..e0dc7c3 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -125,6 +125,7 @@ CommandIsReadOnly(Node *parsetree)
case CMD_UPDATE:
case CMD_INSERT:
case CMD_DELETE:
+ case CMD_MERGE:
return false;
default:
elog(WARNING, "unrecognized commandType: %d",
@@ -1405,6 +1406,10 @@ CreateCommandTag(Node *parsetree)
tag = "SELECT";
break;
+ case T_MergeStmt:
+ tag = "MERGE";
+ break;
+
/* utility statements --- same whether raw or cooked */
case T_TransactionStmt:
{
@@ -2242,6 +2247,7 @@ GetCommandLogLevel(Node *parsetree)
case T_InsertStmt:
case T_DeleteStmt:
case T_UpdateStmt:
+ case T_MergeStmt:
lev = LOGSTMT_MOD;
break;
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 267a08e..51d0f11 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -164,6 +164,8 @@ extern void ExecBSTruncateTriggers(EState *estate,
ResultRelInfo *relinfo);
extern void ExecASTruncateTriggers(EState *estate,
ResultRelInfo *relinfo);
+extern void ExecBSMergeTriggers(ModifyTableState *mt_state);
+extern void ExecASMergeTriggers(ModifyTableState *mt_state);
extern void AfterTriggerBeginXact(void);
extern void AfterTriggerBeginQuery(void);
diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h
index 67ba3e8..422e3ce 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -16,6 +16,7 @@
#include "nodes/execnodes.h"
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags);
+extern MergeActionState *ExecInitMergeAction(MergeAction *node, EState *estate, int eflags);
extern TupleTableSlot *ExecModifyTable(ModifyTableState *node);
extern void ExecEndModifyTable(ModifyTableState *node);
extern void ExecReScanModifyTable(ModifyTableState *node);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 7442d2d..64e20bb 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1032,9 +1032,22 @@ typedef struct ModifyTableState
int mt_whichplan; /* which one is being executed (0..n-1) */
EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */
bool fireBSTriggers; /* do we need to fire stmt triggers? */
+ List *mergeActPstates; /*the list of the planstate of meger command actions.
+ NIL if this is not a merge command.
+ The elements if it are still ModifyTableState nodes*/
} ModifyTableState;
/* ----------------
+ * MergeActionState information
+ * ----------------
+ */
+typedef struct MergeActionState
+{
+ PlanState ps; /* its first field is NodeTag */
+ CmdType operation;
+} MergeActionState;
+
+/* ----------------
* AppendState information
*
* nplans how many plans are in the array
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index a5f5df5..a840349 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -44,6 +44,7 @@ typedef enum NodeTag
T_Plan = 100,
T_Result,
T_ModifyTable,
+ T_MergeAction,
T_Append,
T_RecursiveUnion,
T_BitmapAnd,
@@ -86,6 +87,7 @@ typedef enum NodeTag
T_PlanState = 200,
T_ResultState,
T_ModifyTableState,
+ T_MergeActionState,
T_AppendState,
T_RecursiveUnionState,
T_BitmapAndState,
@@ -347,6 +349,13 @@ typedef enum NodeTag
T_AlterUserMappingStmt,
T_DropUserMappingStmt,
T_AlterTableSpaceOptionsStmt,
+ T_MergeStmt,
+ T_MergeConditionAction,
+ T_MergeUpdate,
+ T_MergeDelete,
+ T_MergeInsert,
+ T_MergeDoNothing,
+ T_MergeError,
/*
* TAGS FOR PARSE TREE NODES (parsenodes.h)
@@ -511,6 +520,9 @@ typedef enum CmdType
CMD_UPDATE, /* update stmt */
CMD_INSERT, /* insert stmt */
CMD_DELETE,
+ CMD_MERGE, /*merge stmt*/
+ CMD_DONOTHING,
+ CMD_RAISEERR,
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 d31cf6c..ef5520f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -149,6 +149,13 @@ typedef struct Query
List *constraintDeps; /* a list of pg_constraint OIDs that the query
* depends on to be semantically valid */
+
+ /*the fileds for MERGE command*/
+ bool isMergeAction; /*if this query is a merge action. */
+ bool matched; /*this is a MATCHED action or NOT*/
+ bool replaced; /*is this merge action replaced by rules*/
+ List *mergeActQry; /* the list of all the merge actions.
+ * used only for merge query statment*/
} Query;
@@ -993,6 +1000,58 @@ typedef struct SelectStmt
/* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
+/*The structure for MERGE command statement*/
+typedef struct MergeStmt
+{
+ NodeTag type;
+ RangeVar *relation; /*targe relation for merge */
+
+ /* source relations for the merge.
+ *Currently, we only allwo single-source merge,
+ *so the length of this list should always be 1
+ */
+ List *source;
+ Node *matchCondition; /* qualifications of the merge*/
+
+ /*list of MergeConditionAction structure.
+ *It stores all the matched / not-matched
+ *conditions and the corresponding actions
+ *The elments of this list are MergeConditionAction
+ *nodes
+ */
+ List *actions;
+
+}MergeStmt;
+
+/* the structure for the actions of MERGE command.
+* Holds info of the clauses like
+* "WHEN MATCHED AND ... THEN UPDATE/DELETE/INSERT"
+*/
+typedef struct MergeConditionAction
+{
+ NodeTag type;
+ bool match; /*match or not match*/
+ Node *condition;/*the AND condition for this action*/
+ Node *action; /*the actions: delete , insert or update*/
+}MergeConditionAction;
+
+/*
+* The merge action nodes are in fact the
+* ordinary nodes of UPDATE,DELETE and INSERT
+*/
+typedef UpdateStmt MergeUpdate;
+typedef DeleteStmt MergeDelete;
+typedef InsertStmt MergeInsert;
+
+typedef struct MergeDoNothing
+{
+ NodeTag type;
+}MergeDoNothing;
+
+typedef struct MergeError
+{
+ NodeTag type;
+}MergeError;
/* ----------------------
* Set Operation node for post-analysis query trees
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 037bc0b..a020051 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -169,9 +169,25 @@ typedef struct ModifyTable
List *returningLists; /* per-target-table RETURNING tlists */
List *rowMarks; /* PlanRowMarks (non-locking only) */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
+ List *mergeActPlan; /*the plans for merge actions,
+ which are also ModifyTable nodes*/
} ModifyTable;
/* ----------------
+ * MergeAction node -
+ * The plan node for the actions of MERGE command
+ * ----------------
+ */
+typedef struct MergeAction
+{
+ Plan plan;
+ bool replaced; /*if this action is replaced by INSTEAD rules*/
+ CmdType operation;/* INSERT, UPDATE, or DELETE */
+ bool matched;
+ List *flattenedqual; /*the flattened qual expression of action*/
+}MergeAction;
+
+/* ----------------
* Append node -
* Generate the concatenation of the results of sub-plans.
* ----------------
diff --git a/src/include/optimizer/var.h b/src/include/optimizer/var.h
index b0e04a0..4d6c9e8 100644
--- a/src/include/optimizer/var.h
+++ b/src/include/optimizer/var.h
@@ -15,6 +15,7 @@
#define VAR_H
#include "nodes/relation.h"
+#include "nodes/plannodes.h"
typedef enum
{
@@ -32,5 +33,5 @@ extern int locate_var_of_relation(Node *node, int relid, int levelsup);
extern int find_minimum_var_level(Node *node);
extern List *pull_var_clause(Node *node, PVCPlaceHolderBehavior behavior);
extern Node *flatten_join_alias_vars(PlannerInfo *root, Node *node);
-
+extern void push_up_merge_action_vars(MergeAction * actplan,Query * actqry);
#endif /* VAR_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 974bb7a..208c3e4 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -141,6 +141,7 @@ PG_KEYWORD("encoding", ENCODING, UNRESERVED_KEYWORD)
PG_KEYWORD("encrypted", ENCRYPTED, UNRESERVED_KEYWORD)
PG_KEYWORD("end", END_P, RESERVED_KEYWORD)
PG_KEYWORD("enum", ENUM_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("error", ERROR_P, UNRESERVED_KEYWORD)
PG_KEYWORD("escape", ESCAPE, UNRESERVED_KEYWORD)
PG_KEYWORD("except", EXCEPT, RESERVED_KEYWORD)
PG_KEYWORD("exclude", EXCLUDE, UNRESERVED_KEYWORD)
@@ -229,7 +230,9 @@ PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD)
PG_KEYWORD("login", LOGIN_P, UNRESERVED_KEYWORD)
PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD)
PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD)
+PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD)
PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD)
+PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD)
PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD)
PG_KEYWORD("mode", MODE, UNRESERVED_KEYWORD)
@@ -296,6 +299,7 @@ PG_KEYWORD("privileges", PRIVILEGES, UNRESERVED_KEYWORD)
PG_KEYWORD("procedural", PROCEDURAL, UNRESERVED_KEYWORD)
PG_KEYWORD("procedure", PROCEDURE, UNRESERVED_KEYWORD)
PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD)
+PG_KEYWORD("raise", RAISE, UNRESERVED_KEYWORD)
PG_KEYWORD("range", RANGE, UNRESERVED_KEYWORD)
PG_KEYWORD("read", READ, UNRESERVED_KEYWORD)
PG_KEYWORD("real", REAL, COL_NAME_KEYWORD)
diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h
index f3d3ee9..b54f530 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -19,6 +19,7 @@
extern void transformFromClause(ParseState *pstate, List *frmList);
extern int setTargetTable(ParseState *pstate, RangeVar *relation,
bool inh, bool alsoSource, AclMode requiredPerms);
+extern void setTargetTableLock(ParseState *pstate, RangeVar *relation);
extern bool interpretInhOption(InhOption inhOpt);
extern bool interpretOidsOption(List *defList);
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 0000000..0e30194
--- /dev/null
+++ b/src/test/regress/expected/merge.out
@@ -0,0 +1,260 @@
+--
+-- MERGE
+--
+CREATE TABLE target (id integer, balance integer);
+CREATE TABLE source (id integer, balance integer);
+INSERT INTO target VALUES (1, 10);
+INSERT INTO target VALUES (2, 20);
+INSERT INTO target VALUES (3, 30);
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+--
+-- initial tests
+--
+-- empty source means 0 rows touched
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.balance
+;
+-- insert some source rows to work from
+INSERT INTO source VALUES (2, 5);
+INSERT INTO source VALUES (3, 20);
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source;
+ id | balance
+----+---------
+ 2 | 5
+ 3 | 20
+ 4 | 40
+(3 rows)
+
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.balance
+;
+NOTICE: one tuple is ERROR
+ROLLBACK;
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+NOTICE: one tuple is ERROR
+NOTICE: one tuple is ERROR
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+-- now the classic UPSERT
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+ 2 | 25
+ 3 | 50
+ 4 | 40
+(4 rows)
+
+ROLLBACK;
+--
+-- Non-standard functionality
+--
+-- do a simple equivalent of a DELETE join
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ DELETE
+;
+NOTICE: one tuple is ERROR
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+(1 row)
+
+ROLLBACK;
+-- now the classic UPSERT, with a DELETE
+-- the Standard doesn't allow the DELETE clause for some reason,
+-- though other implementations do
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED AND s.balance > 10 THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN MATCHED THEN
+ DELETE
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+ 3 | 50
+ 4 | 40
+(3 rows)
+
+ROLLBACK;
+-- Prepare the test data to generate multiple matching rows for a single target
+INSERT INTO source VALUES (3, 5);
+SELECT * FROM source ORDER BY id, balance;
+ id | balance
+----+---------
+ 2 | 5
+ 3 | 5
+ 3 | 20
+ 4 | 40
+(4 rows)
+
+-- we now have a duplicate key in source, so when we join to
+-- target we will generate 2 matching rows, not one.
+-- According to standard this command should fail.
+-- But it suceeds in PostgreSQL implementation by simply ignoring the second
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED AND s.balance > 10 THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+NOTICE: one tuple is ERROR
+NOTICE: one tuple is ERROR
+ROLLBACK;
+-- Now lets prepare the test data to generate 2 non-matching rows
+DELETE FROM source WHERE id = 3 AND balance = 5;
+INSERT INTO source VALUES (4, 5);
+SELECT * FROM source;
+ id | balance
+----+---------
+ 2 | 5
+ 3 | 20
+ 4 | 40
+ 4 | 5
+(4 rows)
+
+-- This next SQL statement
+-- suceeds according to standard (yes, it is inconsistent)
+-- suceeds in PostgreSQL implementation, though could easily fail if
+-- there was an appropriate unique constraint
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+NOTICE: one tuple is ERROR
+NOTICE: one tuple is ERROR
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+ 4 | 40
+ 4 | 5
+(5 rows)
+
+ROLLBACK;
+-- This next SQL statement works, but since there is no WHEN clause that
+-- applies to non-matching rows, SQL standard requires us to generate
+-- rows with DEFAULT VALUES for all columns, which is why we support the
+-- syntax DO NOTHING (similar to the way Rules work) in addition
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED AND s.balance > 100 THEN
+ INSERT VALUES (s.id, s.balance)
+;
+NOTICE: one tuple is ERROR
+NOTICE: one tuple is ERROR
+NOTICE: one tuple is ERROR
+NOTICE: one tuple is ERROR
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+ROLLBACK;
+-- This next SQL statement suceeds, but does nothing since there are
+-- only non-matching rows that do not activate a WHEN clause, so we
+-- provide syntax to just ignore them, rather than allowing data quality
+-- problems
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED AND s.balance > 100 THEN
+ INSERT VALUES (s.id, s.balance)
+WHEN NOT MATCHED THEN
+ DO NOTHING
+;
+NOTICE: one tuple is ERROR
+NOTICE: one tuple is ERROR
+SELECT * FROM target;
+ id | balance
+----+---------
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+ROLLBACK;
+--
+-- Weirdness
+--
+-- MERGE statement containing WHEN clauses that are never executable
+-- NOT an error under the standard
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED AND s.balance > 0 THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN MATCHED THEN
+ DELETE
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+WHEN NOT MATCHED THEN /* never executed because of order of WHEN clauses */
+ INSERT VALUES (s.id, s.balance + 10)
+WHEN MATCHED THEN /* never executed because of order of WHEN clauses */
+ UPDATE SET balance = t.balance + s.balance
+;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 191d1fe..2551b2a 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -91,7 +91,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml merge
# run stats by itself because its delay may be insufficient under heavy load
test: stats
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 80a9881..e7d7fae 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -123,4 +123,5 @@ test: returning
test: largeobject
test: with
test: xml
+test: merge
test: stats
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 0000000..522868a
--- /dev/null
+++ b/src/test/regress/sql/merge.sql
@@ -0,0 +1,178 @@
+--
+-- MERGE
+--
+CREATE TABLE target (id integer, balance integer);
+CREATE TABLE source (id integer, balance integer);
+INSERT INTO target VALUES (1, 10);
+INSERT INTO target VALUES (2, 20);
+INSERT INTO target VALUES (3, 30);
+SELECT * FROM target;
+
+--
+-- initial tests
+--
+-- empty source means 0 rows touched
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.balance
+;
+-- insert some source rows to work from
+INSERT INTO source VALUES (2, 5);
+INSERT INTO source VALUES (3, 20);
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source;
+
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.balance
+;
+
+ROLLBACK;
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+SELECT * FROM target;
+
+ROLLBACK;
+-- now the classic UPSERT
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+SELECT * FROM target;
+
+ROLLBACK;
+--
+-- Non-standard functionality
+--
+-- do a simple equivalent of a DELETE join
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED THEN
+ DELETE
+;
+SELECT * FROM target;
+
+ROLLBACK;
+-- now the classic UPSERT, with a DELETE
+-- the Standard doesn't allow the DELETE clause for some reason,
+-- though other implementations do
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED AND s.balance > 10 THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN MATCHED THEN
+ DELETE
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+SELECT * FROM target;
+
+ROLLBACK;
+
+-- Prepare the test data to generate multiple matching rows for a single target
+INSERT INTO source VALUES (3, 5);
+SELECT * FROM source ORDER BY id, balance;
+
+-- we now have a duplicate key in source, so when we join to
+-- target we will generate 2 matching rows, not one.
+-- According to standard this command should fail.
+-- But it suceeds in PostgreSQL implementation by simply ignoring the second
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED AND s.balance > 10 THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+ROLLBACK;
+-- Now lets prepare the test data to generate 2 non-matching rows
+DELETE FROM source WHERE id = 3 AND balance = 5;
+INSERT INTO source VALUES (4, 5);
+SELECT * FROM source;
+
+-- This next SQL statement
+-- suceeds according to standard (yes, it is inconsistent)
+-- suceeds in PostgreSQL implementation, though could easily fail if
+-- there was an appropriate unique constraint
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+;
+SELECT * FROM target;
+
+ROLLBACK;
+-- This next SQL statement works, but since there is no WHEN clause that
+-- applies to non-matching rows, SQL standard requires us to generate
+-- rows with DEFAULT VALUES for all columns, which is why we support the
+-- syntax DO NOTHING (similar to the way Rules work) in addition
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED AND s.balance > 100 THEN
+ INSERT VALUES (s.id, s.balance)
+;
+SELECT * FROM target;
+
+ROLLBACK;
+-- This next SQL statement suceeds, but does nothing since there are
+-- only non-matching rows that do not activate a WHEN clause, so we
+-- provide syntax to just ignore them, rather than allowing data quality
+-- problems
+BEGIN;
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN NOT MATCHED AND s.balance > 100 THEN
+ INSERT VALUES (s.id, s.balance)
+WHEN NOT MATCHED THEN
+ DO NOTHING
+;
+SELECT * FROM target;
+
+ROLLBACK;
+--
+-- Weirdness
+--
+-- MERGE statement containing WHEN clauses that are never executable
+-- NOT an error under the standard
+MERGE into target t
+USING (select * from source) AS s
+ON t.id = s.id
+WHEN MATCHED AND s.balance > 0 THEN
+ UPDATE SET balance = t.balance + s.balance
+WHEN MATCHED THEN
+ DELETE
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.id, s.balance)
+WHEN NOT MATCHED THEN /* never executed because of order of WHEN clauses */
+ INSERT VALUES (s.id, s.balance + 10)
+WHEN MATCHED THEN /* never executed because of order of WHEN clauses */
+ UPDATE SET balance = t.balance + s.balance
+;