new patch of MERGE (merge_204) & a question about duplicated ctid
Dear Greg,
I have updated the MERGE patch for two main problems.
1. Support of system attributes in MERGE action expressions.
In old version of MERGE, I use the top join as the only RTE in var name
space, during the transformation process in parser. It contains all the
common attributes of source and target table but not the system attributes,
such as oid.
Thus, if user specified system attributes in MERGE actions, error pops.
In current version, the var name space is forced to be the target table RTE
and source table RTE. So system attributes will be correctly transformed.
2. fix the problem of dropped column.
In planner, we need to map the Var nodes in MERGE actions to its
corresponding TE in the main plan's TL. I used to do this under the
assumption that all the TE are ordered by their attribute no (the value of
filed "Var.varattno"). However, this is not the case when the table contain
dropped attributes. The dropped attribute will take one attribute number but
not appear in TL.
Now a new mapping algorithm is forged, which can avoid this bug.
Please help me to review this new patch. Thank you!
PS:
In the new pgsql 9.1, I find that the junk attribute ctid is added twice,
for UPDATE and DELETE commands.
In planner, the function preprocess_targetlist() will invoke a sub
function expand_targetlist() which will add missed TE for INSERT and UPDATE
commands. And for UPDATE and DELETE, it will also create a TE of ctid (a
junk attr) which is used in executor. This is the process in old pgsql
versions.
However, in pgsql 9.1, while the above is kept. A new function in rewriter,
that is rewriteTargetListUD() does the same thing. So for a plain
UPDATE/DELTE command, it will have two ctid junk attr in its final TL.
Is there any particular reason for this duplicated ctid??
This will not cause much problem, normally. However, in MERGE command, the
TL of MERGE actions should contain no junk attributes. So, I add blocking
codes in these two parts, to avoid the change of TL of MERGE actions. I
don't know whether this will cause any problem.
Regards,
Yours Boxan
Attachments:
On Sat, Dec 04, 2010 at 09:27:52PM +0800, Boxuan Zhai wrote:
Dear Greg,
I have updated the MERGE patch for two main problems.
Please attach the actual patch :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Boxuan Zhai wrote:
I have updated the MERGE patch for two main problems.
The patch inside the .tar.gz file you attached isn't right; that
extracts to a tiny file of junk characters.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
---------- Forwarded message ----------
From: Boxuan Zhai <bxzhai2010@gmail.com>
Date: Mon, Dec 6, 2010 at 9:17 PM
Subject: Re: new patch of MERGE (merge_204) & a question about duplicated
ctid
To: Greg Smith <greg@2ndquadrant.com>
On Mon, Dec 6, 2010 at 2:12 AM, Greg Smith <greg@2ndquadrant.com> wrote:
Boxuan Zhai wrote:
I have updated the MERGE patch for two main problems.
The patch inside the .tar.gz file you attached isn't right; that extracts
to a tiny file of junk characters.
Sorry for that. The original file is correct in my machine, but the gz file
is broken.
I send the original file directly this time.
Sorry again.
Regards,
Show quoted text
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Attachments:
merge_204_2010DEC06.patchapplication/octet-stream; name=merge_204_2010DEC06.patchDownload
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index a352a43..d24df5e 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -118,6 +118,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..7c73623
--- /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 5968db1..4b681bb 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -340,6 +340,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 13de002..aae806b 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -146,6 +146,7 @@
&listen;
&load;
&lock;
+ &merge;
&move;
¬ify;
&prepare;
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a5e44c0..a5ce7c2 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -81,6 +81,8 @@ static void show_sort_keys_common(PlanState *planstate,
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);
@@ -644,6 +646,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case CMD_DELETE:
pname = operation = "Delete";
break;
+ case CMD_MERGE:
+ pname = operation = "Merge";
+ break;
default:
pname = "???";
break;
@@ -1207,6 +1212,11 @@ ExplainNode(PlanState *planstate, List *ancestors,
if (innerPlanState(planstate))
ExplainNode(innerPlanState(planstate), ancestors,
"Inner", NULL, es);
+
+ if (IsA(plan, ModifyTable) &&
+ ((ModifyTable *)plan)->operation == CMD_MERGE)
+ ExplainMergeActions((ModifyTableState *) planstate,
+ ancestors, es);
/* special child plans */
switch (nodeTag(plan))
@@ -1547,6 +1557,89 @@ explain_get_index_name(Oid indexId)
return result;
}
+static void
+ExplainMergeActions(ModifyTableState *mt_planstate, List *ancestors,
+ ExplainState *es)
+{
+ ListCell *l;
+ int actno = 1;
+ StringInfo buf = makeStringInfo();
+ StringInfo acttitle = makeStringInfo();
+ MergeActionSet *actset;
+
+ if (mt_planstate->operation != CMD_MERGE ||
+ mt_planstate->mt_mergeActPstates == NULL)
+ return;
+
+ actset = mt_planstate->mt_mergeActPstates[0];
+
+ foreach(l, actset->actions)
+ {
+ ModifyTableState *mt_state = (ModifyTableState *) lfirst(l);
+ MergeActionState *act_pstate = (MergeActionState *) mt_state->mt_plans[0];
+ MergeAction *act_plan = (MergeAction *) act_pstate->ps.plan;
+
+ /*prepare the title*/
+ resetStringInfo(acttitle);
+ appendStringInfo(acttitle, "Action %d", actno);
+
+ /*prepare the string for printing*/
+ resetStringInfo(buf);
+ 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;
+ default:
+ elog(ERROR, "unknown merge action");
+ }
+
+ if (act_plan->matched)
+ appendStringInfoString(buf, "Matched ");
+ else
+ appendStringInfoString(buf, "Not Mactched ");
+
+ if (act_plan->flattenedqual)
+ appendStringInfoString(buf, "And ");
+
+ /*print the action type*/
+ ExplainPropertyText(acttitle->data, buf->data, es);
+
+ /*print the action qual*/
+ show_qual(act_plan->flattenedqual, "Qual",
+ &act_pstate->ps, ancestors, true, es);
+
+ /*print the target list of action*/
+ if (es->verbose &&
+ (act_plan->operation == CMD_INSERT ||
+ act_plan->operation == CMD_UPDATE))
+ {
+ List *orignialtlist;
+
+ orignialtlist = act_plan->plan.targetlist;
+ act_plan->plan.targetlist = act_plan->flattenedtlist;
+ show_plan_tlist((PlanState *) act_pstate, ancestors, es);
+ act_plan->plan.targetlist = orignialtlist;
+ }
+
+ if (act_pstate->ps.subPlan)
+ ExplainSubPlans(act_pstate->ps.subPlan, ancestors, "SubPlan", es);
+
+ actno++;
+ }
+
+ ExplainPropertyText("MainPlan", "", es);
+}
+
/*
* Show the target of a Scan node
*/
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index d69fdcf..0bd0b68 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2443,6 +2443,87 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
false, NULL, NULL, NIL, NULL);
}
+void
+ExecBSMergeTriggers(ModifyTableState *mt_state)
+{
+ ListCell *l;
+ MergeActionSet *actset;
+ bool doUpdateTriggers = false;
+ bool doInsertTriggers = false;
+ bool doDeleteTriggers = false;
+
+ /* Scan the actions to see what kind of statements there is */
+ actset = mt_state->mt_mergeActPstates[0];
+ foreach(l, actset->actions)
+ {
+ ModifyTableState *actmtstate;
+ MergeActionState *actPstate;
+ MergeAction *actplan;
+
+ actmtstate = (ModifyTableState *) lfirst(l);
+ actPstate = (MergeActionState *) actmtstate->mt_plans[0];
+ actplan = (MergeAction *) actPstate->ps.plan;
+
+ if (actplan->operation == CMD_UPDATE)
+ doUpdateTriggers = true;
+ else if (actplan->operation == CMD_INSERT)
+ doInsertTriggers = true;
+ else if (actplan->operation == CMD_DELETE)
+ doDeleteTriggers = true;
+ }
+
+ /* And 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);
+}
+
+void
+ExecASMergeTriggers(ModifyTableState *mt_state)
+{
+ ListCell *l;
+ MergeActionSet *actset;
+ bool doUpdateTriggers = false;
+ bool doInsertTriggers = false;
+ bool doDeleteTriggers = false;
+
+ /* Scan the actions to see what kind of statements there is */
+ actset = mt_state->mt_mergeActPstates[0];
+ foreach(l, actset->actions)
+ {
+ ModifyTableState *actmtstate;
+ MergeActionState *actPstate;
+ MergeAction *actplan;
+
+ actmtstate = (ModifyTableState *)lfirst(l);
+ actPstate = (MergeActionState *)actmtstate->mt_plans[0];
+ actplan = (MergeAction *)actPstate->ps.plan;
+
+ if(actplan->operation == CMD_UPDATE)
+ doUpdateTriggers = true;
+ else if(actplan->operation == CMD_INSERT)
+ doInsertTriggers = true;
+ else if(actplan->operation == CMD_DELETE)
+ doDeleteTriggers = true;
+ }
+
+ /* And 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);
+}
static HeapTuple
GetTupleForTrigger(EState *estate,
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 69f3a28..8637fa8 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 edd175e..2be0491 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -154,6 +154,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 541adaf..e13916e 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -704,6 +704,106 @@ lreplace:;
return NULL;
}
+static TupleTableSlot *
+ExecMerge(ItemPointer tupleid,
+ HeapTupleHeader oldtuple,
+ TupleTableSlot *slot,
+ TupleTableSlot *planSlot,
+ MergeActionSet *actset,
+ EState *estate)
+{
+
+ TupleTableSlot *actslot = NULL;
+ ListCell *each;
+
+ /*
+ * Try the merge actions one by one until we have a match.
+ */
+ foreach(each, actset->actions)
+ {
+ ModifyTableState *mt_pstate;
+ MergeActionState *action_pstate;
+ ExprContext *econtext;
+ bool matched;
+
+ mt_pstate = (ModifyTableState *) lfirst(each);
+ Assert(IsA(mt_pstate, ModifyTableState));
+
+ /*
+ * mt_pstate is supposed to have only ONE mt_plans,
+ * which is a MergeActionState
+ */
+ 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;
+
+ /*
+ * Check that additional quals match, if any.
+ */
+ 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, we have a match. Perform the action */
+
+ /* First project any RETURNING result tuple slot, if needed */
+ if (action_pstate->operation == CMD_INSERT ||
+ action_pstate->operation == CMD_UPDATE)
+ actslot = ExecProcessReturning(action_pstate->ps.ps_ProjInfo,
+ planSlot, planSlot);
+
+ switch (action_pstate->operation)
+ {
+ case CMD_INSERT:
+ return ExecInsert(actslot, planSlot, estate);
+
+ case CMD_UPDATE:
+ return ExecUpdate(tupleid,
+ oldtuple,
+ actslot,
+ planSlot,
+ &mt_pstate->mt_epqstate,
+ estate);
+
+ case CMD_DELETE:
+ return ExecDelete(tupleid,
+ oldtuple,
+ planSlot,
+ &mt_pstate->mt_epqstate,
+ estate);
+
+ case CMD_DONOTHING:
+ return NULL;
+
+ default:
+ elog(ERROR, "unknown merge action type for execute");
+ break;
+ }
+ }
+
+ /*
+ * No matching action found. Perform the default action, which is
+ * DO NOTHING.
+ */
+ return NULL;
+}
/*
* Process BEFORE EACH STATEMENT triggers
@@ -725,6 +825,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;
@@ -751,6 +854,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;
@@ -777,6 +883,7 @@ ExecModifyTable(ModifyTableState *node)
ItemPointer tupleid = NULL;
ItemPointerData tuple_ctid;
HeapTupleHeader oldtuple = NULL;
+ MergeActionSet *mergeActSet = NULL;
/*
* On first call, fire BEFORE STATEMENT triggers before proceeding.
@@ -798,6 +905,8 @@ ExecModifyTable(ModifyTableState *node)
/* Preload local variables */
subplanstate = node->mt_plans[node->mt_whichplan];
junkfilter = estate->es_result_relation_info->ri_junkFilter;
+ if(node->mt_mergeActPstates)
+ mergeActSet = node->mt_mergeActPstates[node->mt_whichplan];
/*
* Fetch rows from subplan(s), and execute the required table modification
@@ -824,6 +933,8 @@ ExecModifyTable(ModifyTableState *node)
estate->es_result_relation_info++;
subplanstate = node->mt_plans[node->mt_whichplan];
junkfilter = estate->es_result_relation_info->ri_junkFilter;
+ if(node->mt_mergeActPstates)
+ mergeActSet = node->mt_mergeActPstates[node->mt_whichplan];
EvalPlanQualSetPlan(&node->mt_epqstate, subplanstate->plan);
continue;
}
@@ -839,7 +950,7 @@ ExecModifyTable(ModifyTableState *node)
/*
* extract the 'ctid' or 'wholerow' junk attribute.
*/
- if (operation == CMD_UPDATE || operation == CMD_DELETE)
+ if (operation == CMD_UPDATE || operation == CMD_DELETE || operation == CMD_MERGE)
{
Datum datum;
bool isNull;
@@ -849,13 +960,24 @@ ExecModifyTable(ModifyTableState *node)
datum = ExecGetJunkAttribute(slot,
junkfilter->jf_junkAttNo,
&isNull);
- /* shouldn't ever get a null result... */
if (isNull)
- elog(ERROR, "ctid is NULL");
+ {
+ /*
+ * Shouldn't ever get a null result for UPDATE or DELETE.
+ * MERGE gets 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 ctid!! */
- tupleid = &tuple_ctid;
+ tupleid = (ItemPointer) DatumGetPointer(datum);
+ tuple_ctid = *tupleid; /* be sure we don't free the ctid!! */
+ tupleid = &tuple_ctid;
+ }
}
else
{
@@ -890,6 +1012,10 @@ ExecModifyTable(ModifyTableState *node)
slot = ExecDelete(tupleid, oldtuple, planSlot,
&node->mt_epqstate, estate);
break;
+ case CMD_MERGE:
+ slot = ExecMerge(tupleid, oldtuple, slot, planSlot,
+ mergeActSet, estate);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -917,6 +1043,69 @@ 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);
+
+ /*
+ * XXX: do we need a check for the plan output here ?
+ * (by calling the ExecCheckPlanOutput() function
+ */
+
+ return result;
+}
+
/* ----------------------------------------------------------------
* ExecInitModifyTable
* ----------------------------------------------------------------
@@ -932,6 +1121,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)));
@@ -972,6 +1162,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 junkfilter.
+ * junkfilter 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++;
@@ -1101,7 +1301,11 @@ 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:
break;
default:
elog(ERROR, "unknown operation");
@@ -1124,9 +1328,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
resultRelInfo->ri_RelationDesc->rd_att->tdhasoid,
ExecInitExtraTupleSlot(estate));
- if (operation == CMD_UPDATE || operation == CMD_DELETE)
+ if (operation == CMD_UPDATE || operation == CMD_DELETE ||
+ operation == CMD_MERGE)
{
- /* For UPDATE/DELETE, find the appropriate junk attr now */
+ /* For UPDATE/DELETE/MERGE, find the appropriate junk attr now */
if (resultRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_RELATION)
{
j->jf_junkAttNo = ExecFindJunkAttribute(j, "ctid");
@@ -1161,6 +1366,36 @@ 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.
+ *Each action in each action set should be init by the ExecInitNode.
+ *The returned planstates will take the place of the original plan nodes.
+ *These new action set will be put in the mt_mergeActPstates array.
+ */
+ if(node->operation == CMD_MERGE)
+ {
+ /*we have one action set for each result relation (main plan)*/
+ mtstate->mt_mergeActPstates =
+ (MergeActionSet **) palloc0(sizeof(MergeActionSet*) * nplans);
+
+ estate->es_result_relation_info = estate->es_result_relations;
+ i = 0;
+ foreach(l, node->mergeActPlan)
+ {
+ ListCell *e;
+ MergeActionSet *actset = (MergeActionSet *) lfirst(l);
+
+ foreach(e, actset->actions)
+ {
+ lfirst(e) = ExecInitNode((Plan *)lfirst(e), estate, 0);
+ }
+ mtstate->mt_mergeActPstates[i] = actset;
+ estate->es_result_relation_info++;
+ i++;
+ }
+ estate->es_result_relation_info = NULL;
+ }
+
return mtstate;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 0e0b4dc..346b8c8 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -177,6 +177,42 @@ _copyModifyTable(ModifyTable *from)
COPY_NODE_FIELD(returningLists);
COPY_NODE_FIELD(rowMarks);
COPY_SCALAR_FIELD(epqParam);
+ COPY_NODE_FIELD(mergeActPlan);
+
+ return newnode;
+}
+
+/*
+ * _copyMergeAction
+ */
+static MergeAction *
+_copyMergeAction(MergeAction *from)
+{
+ MergeAction *newnode = makeNode(MergeAction);
+
+ /*
+ * copy node superclass fields
+ */
+ CopyPlanFields((Plan *) from, (Plan *) newnode);
+
+ COPY_SCALAR_FIELD(operation);
+ COPY_SCALAR_FIELD(matched);
+ COPY_NODE_FIELD(flattenedqual);
+ COPY_NODE_FIELD(flattenedtlist);
+
+ return newnode;
+}
+
+/*
+ * _copyMergeActionSet
+ */
+static MergeActionSet *
+_copyMergeActionSet(MergeActionSet *from)
+{
+ MergeActionSet *newnode = makeNode(MergeActionSet);
+
+ COPY_SCALAR_FIELD(result_relation);
+ COPY_NODE_FIELD(actions);
return newnode;
}
@@ -2317,6 +2353,10 @@ _copyQuery(Query *from)
COPY_NODE_FIELD(rowMarks);
COPY_NODE_FIELD(setOperations);
COPY_NODE_FIELD(constraintDeps);
+ COPY_SCALAR_FIELD(isMergeAction);
+ COPY_SCALAR_FIELD(matched);
+ COPY_SCALAR_FIELD(sourceAttrNo);
+ COPY_NODE_FIELD(mergeActQry);
return newnode;
}
@@ -2326,6 +2366,7 @@ _copyInsertStmt(InsertStmt *from)
{
InsertStmt *newnode = makeNode(InsertStmt);
+ COPY_SCALAR_FIELD(isMergeAction);
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(selectStmt);
@@ -2340,6 +2381,7 @@ _copyDeleteStmt(DeleteStmt *from)
{
DeleteStmt *newnode = makeNode(DeleteStmt);
+ COPY_SCALAR_FIELD(isMergeAction);
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
@@ -2354,6 +2396,7 @@ _copyUpdateStmt(UpdateStmt *from)
{
UpdateStmt *newnode = makeNode(UpdateStmt);
+ COPY_SCALAR_FIELD(isMergeAction);
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(targetList);
COPY_NODE_FIELD(whereClause);
@@ -2391,6 +2434,47 @@ _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 MergeDoNothing *
+_copyMergeDoNothing(MergeDoNothing *from)
+{
+ MergeDoNothing *newnode = makeNode(MergeDoNothing);
+
+ return newnode;
+}
+
+static MergeError*
+_copyMergeError(MergeError *from)
+{
+ MergeError *newnode = makeNode(MergeError);
+
+ return newnode;
+}
+
static SetOperationStmt *
_copySetOperationStmt(SetOperationStmt *from)
{
@@ -3682,6 +3766,12 @@ copyObject(void *from)
case T_ModifyTable:
retval = _copyModifyTable(from);
break;
+ case T_MergeAction:
+ retval = _copyMergeAction(from);
+ break;
+ case T_MergeActionSet:
+ retval = _copyMergeActionSet(from);
+ break;
case T_Append:
retval = _copyAppend(from);
break;
@@ -3988,6 +4078,18 @@ 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_MergeDoNothing:
+ retval = _copyMergeDoNothing(from);
+ break;
+ case T_MergeError:
+ retval = _copyMergeError(from);
+ break;
case T_SetOperationStmt:
retval = _copySetOperationStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 2d2b8c7..2a8acde 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -890,6 +890,10 @@ _equalQuery(Query *a, Query *b)
COMPARE_NODE_FIELD(rowMarks);
COMPARE_NODE_FIELD(setOperations);
COMPARE_NODE_FIELD(constraintDeps);
+ COMPARE_SCALAR_FIELD(isMergeAction);
+ COMPARE_SCALAR_FIELD(matched);
+ COMPARE_SCALAR_FIELD(sourceAttrNo);
+ COMPARE_NODE_FIELD(mergeActQry);
return true;
}
@@ -897,6 +901,7 @@ _equalQuery(Query *a, Query *b)
static bool
_equalInsertStmt(InsertStmt *a, InsertStmt *b)
{
+ COMPARE_SCALAR_FIELD(isMergeAction);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(selectStmt);
@@ -909,6 +914,7 @@ _equalInsertStmt(InsertStmt *a, InsertStmt *b)
static bool
_equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
{
+ COMPARE_SCALAR_FIELD(isMergeAction);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
@@ -921,6 +927,7 @@ _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
static bool
_equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
{
+ COMPARE_SCALAR_FIELD(isMergeAction);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(targetList);
COMPARE_NODE_FIELD(whereClause);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index afbfcca..8ca5b04 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -332,6 +332,7 @@ _outModifyTable(StringInfo str, ModifyTable *node)
WRITE_NODE_FIELD(returningLists);
WRITE_NODE_FIELD(rowMarks);
WRITE_INT_FIELD(epqParam);
+ WRITE_NODE_FIELD(mergeActPlan);
}
static void
@@ -2085,6 +2086,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_INT_FIELD(sourceAttrNo);
+ 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)
+{
+ WRITE_NODE_TYPE("MERGEACTION");
+
+ _outPlanInfo(str, (Plan *)node);
+
+ WRITE_ENUM_FIELD(operation, CmdType);
+ WRITE_BOOL_FIELD(matched);
+ WRITE_NODE_FIELD(flattenedqual);
+ WRITE_NODE_FIELD(flattenedtlist);
+}
+
+static void
+_outMergeActionSet(StringInfo str, MergeActionSet *node)
+{
+ WRITE_NODE_TYPE("MERGEACTIONSET");
+
+ WRITE_INT_FIELD(result_relation);
+ WRITE_NODE_FIELD(actions);
}
static void
@@ -2983,6 +3031,18 @@ _outNode(StringInfo str, void *obj)
case T_XmlSerialize:
_outXmlSerialize(str, obj);
break;
+ case T_MergeAction:
+ _outMergeAction(str, obj);
+ break;
+ case T_MergeActionSet:
+ _outMergeActionSet(str, obj);
+ break;
+ case T_MergeStmt:
+ _outMergeStmt(str, obj);
+ break;
+ case T_MergeConditionAction:
+ _outMergeConditionAction(str,obj);
+ break;
default:
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 2166a5d..71e892f 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_INT_FIELD(sourceAttrNo);
+ READ_NODE_FIELD(mergeActQry);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 41ad512..86650f3 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -4090,7 +4090,7 @@ make_result(PlannerInfo *root,
ModifyTable *
make_modifytable(CmdType operation, List *resultRelations,
List *subplans, List *returningLists,
- List *rowMarks, int epqParam)
+ List *rowMarks, List *mergeActPlans, int epqParam)
{
ModifyTable *node = makeNode(ModifyTable);
Plan *plan = &node->plan;
@@ -4143,6 +4143,8 @@ make_modifytable(CmdType operation, List *resultRelations,
node->returningLists = returningLists;
node->rowMarks = rowMarks;
node->epqParam = epqParam;
+ if (operation == CMD_MERGE)
+ node->mergeActPlan = mergeActPlans;
return node;
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index a1e5900..c942f1d 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -103,7 +103,8 @@ static void get_column_info_for_window(PlannerInfo *root, WindowClause *wc,
int *ordNumCols,
AttrNumber **ordColIdx,
Oid **ordOperators);
-
+static ModifyTable *merge_action_planner(PlannerInfo *root, Plan *mainPlan);
+static MergeActionSet *merge_action_set_planner(PlannerInfo *root, Plan *mainPlan);
/*****************************************************************************
*
@@ -141,7 +142,6 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
ListCell *lp,
*lrt,
*lrm;
-
/* Cursor options may come from caller or from DECLARE CURSOR stmt */
if (parse->utilityStmt &&
IsA(parse->utilityStmt, DeclareCursorStmt))
@@ -462,6 +462,27 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
}
/*
+ * for MERGE command, we need to preprocess
+ * the expressions for merge actions too.
+ */
+ if(parse->commandType == CMD_MERGE)
+ {
+ ListCell *e;
+
+ foreach(e, parse->mergeActQry)
+ {
+ Query *actqry = (Query *)lfirst(e);
+
+ actqry->targetList = (List *) preprocess_expression(root,
+ (Node *) actqry->targetList,
+ EXPRKIND_TARGET);
+ actqry->jointree->quals = preprocess_expression(root,
+ (Node *) actqry->jointree->quals,
+ EXPRKIND_QUAL);
+ }
+ }
+
+ /*
* In some cases we may want to transfer a HAVING clause into WHERE. We
* cannot do so if the HAVING clause contains aggregates (obviously) or
* volatile functions (since a HAVING clause is supposed to be executed
@@ -538,6 +559,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
{
List *returningLists;
List *rowMarks;
+ List *mergeAction;
/*
* Deal with the RETURNING clause if any. It's convenient to pass
@@ -569,12 +591,19 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
else
rowMarks = root->rowMarks;
+ /*if here is a MERGE command, we need to plan the actions too*/
+ if(parse->commandType == CMD_MERGE)
+ mergeAction = list_make1(merge_action_set_planner(root, plan));
+ else
+ mergeAction = NIL;
+
plan = (Plan *) make_modifytable(parse->commandType,
copyObject(root->resultRelations),
list_make1(plan),
returningLists,
rowMarks,
- SS_assign_special_param(root));
+ mergeAction,
+ SS_assign_special_param(root));
}
}
@@ -595,6 +624,130 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
}
/*
+to generate the merge action set for the main plan.
+Call this function after the grouping_planner()
+
+Only works for MERGE command
+*/
+static MergeActionSet *
+merge_action_set_planner(PlannerInfo *root, Plan *mainPlan)
+{
+ MergeActionSet *result;
+ Query *mainQry = root->parse;
+ ListCell *l;
+ PlannerInfo subroot;
+
+ /*for non-merge command, no need to plan the merge actions*/
+ if(mainQry->commandType != CMD_MERGE ||
+ mainQry->mergeActQry == NIL)
+ return NULL;
+
+ /*do a copy of the root info*/
+ memcpy(&subroot, root, sizeof(PlannerInfo));
+
+ /*create the result node*/
+ result = makeNode(MergeActionSet);
+ result->result_relation = mainQry->resultRelation;
+ result->actions = NIL;
+
+ /*plan the actions one by one*/
+ foreach(l, mainQry->mergeActQry)
+ {
+ ModifyTable *actplan;
+
+ /*put the action query into the subroot*/
+ subroot.parse = (Query *) lfirst(l);
+ actplan = merge_action_planner(&subroot, mainPlan);
+ result->actions = lappend(result->actions, actplan);
+ }
+ return result;
+}
+
+/* create plan for a single merge action */
+static ModifyTable *
+merge_action_planner(PlannerInfo *root, Plan *mainPlan)
+{
+ Query *parse = root->parse;
+ MergeAction *actplan;
+ ModifyTable *result;
+
+ List *returningLists;
+ List *rowMarks;
+
+ /*
+ * no having clause in a merge action
+ */
+ Assert(parse->havingQual == NULL);
+
+ /*
+ * Create the action plan node
+ */
+ actplan = makeNode(MergeAction);
+ actplan->operation = parse->commandType;
+ actplan->matched = parse->matched;
+
+ /* copy the cost from the top_plan */
+ actplan->plan.startup_cost = mainPlan->startup_cost;
+ actplan->plan.total_cost = mainPlan->total_cost;
+ actplan->plan.plan_rows = mainPlan->plan_rows;
+ actplan->plan.plan_width = mainPlan->plan_width;
+
+ /*
+ * Here, the quals expressions are flattened, which is accepted
+ * by deparse functions in EXPLAIN.
+ * But, these expressions will be processed by push_up_vars
+ * latterly and become not flat again.
+ * So, we need to keep a copy of current quals for explaining.
+ */
+ actplan->flattenedqual = (List *) copyObject(parse->jointree->quals);
+ actplan->plan.qual = (List *)parse->jointree->quals;
+
+ /* prepare the target list */
+ if (parse->targetList)
+ {
+ actplan->plan.targetlist = preprocess_targetlist(root,
+ parse->targetList);
+ /*the target list should also be copied for EXPLAIN*/
+ actplan->flattenedtlist = (List *) copyObject(actplan->plan.targetlist);
+ }
+
+ /*
+ *In general situation, all the vars in target list and quals are flattened.
+ *But, we want them to point to the attributes of the top join plan, not to
+ *the subplans. So push them up again here.
+ */
+ push_up_merge_action_vars(actplan, parse, mainPlan->targetlist);
+
+ 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,
+ NIL,
+ SS_assign_special_param(root));
+ return result;
+}
+
+/*
* preprocess_expression
* Do subquery_planner's preprocessing work for an expression,
* which can be a targetlist, a WHERE clause (including JOIN/ON
@@ -739,6 +892,7 @@ inheritance_planner(PlannerInfo *root)
List *returningLists = NIL;
List *rtable = NIL;
List *rowMarks;
+ List *mergeActSets = NIL;
List *tlist;
PlannerInfo subroot;
ListCell *l;
@@ -766,7 +920,7 @@ inheritance_planner(PlannerInfo *root)
Assert(subroot.join_info_list == NIL);
/* and we haven't created PlaceHolderInfos, either */
Assert(subroot.placeholder_list == NIL);
-
+printf("each query for grouping plan \n\n\n%s\n\n\n", nodeToString(subroot.parse));
/* Generate plan */
subplan = grouping_planner(&subroot, 0.0 /* retrieve all tuples */ );
@@ -800,6 +954,66 @@ inheritance_planner(PlannerInfo *root)
appinfo->child_relid);
returningLists = lappend(returningLists, rlist);
}
+
+ /*
+ *For a merge command, we need to generate a set of action plans corresponding
+ *to current result relations.
+ *The adjust_appendrel_attrs() will not process the merge action list of
+ *the main query. And, We need to do this here for a MERGE command.
+ *In fact, no need to do a full query tree mutator on the action queries.
+ *only adjust the target list and quals.
+ */
+ if (parse->commandType == CMD_MERGE)
+ {
+ ListCell *e;
+ MergeActionSet *maset;
+
+ /*
+ *the parse in subroot now is a copy of the main query of current result relation
+ *Here we need to generate a copy of the action queries and shift their target table
+ *to current result relation
+ */
+ subroot.parse->mergeActQry = NIL;
+
+ foreach(e, parse->mergeActQry)
+ {
+ Query *actqry = (Query *)lfirst(e);
+ Query *newactqry = makeNode(Query);
+
+ /*copy most of the common fields from original query*/
+ *newactqry = *actqry;
+
+ /*reset the result relation to current child table*/
+ newactqry->resultRelation = subroot.parse->resultRelation;
+
+ /*make the range table to be consistent with current main query*/
+ newactqry->rtable = subroot.parse->rtable;
+
+ /*adjust the target list*/
+ newactqry->targetList = (List *) adjust_appendrel_attrs(
+ (Node *) actqry->targetList,
+ appinfo);
+ newactqry->targetList = adjust_inherited_tlist(newactqry->targetList,
+ appinfo);
+ /*and qual*/
+ newactqry->jointree = makeNode(FromExpr);
+ newactqry->jointree->fromlist = subroot.parse->jointree->fromlist;
+ newactqry->jointree->quals = adjust_appendrel_attrs(
+ actqry->jointree->quals,
+ appinfo);
+
+ /*put this new action query in to the action list of current main query*/
+ subroot.parse->mergeActQry = lappend(subroot.parse->mergeActQry, newactqry);
+ }
+
+ /*
+ * now we have a complete query (main query + action queries) that has been
+ *shifted to current result relation. Plan these actions here.
+ */
+ maset = merge_action_set_planner(&subroot, subplan);
+ Assert(maset != NULL);
+ mergeActSets = lappend(mergeActSets, maset);
+ }
}
root->resultRelations = resultRelations;
@@ -852,6 +1066,7 @@ inheritance_planner(PlannerInfo *root)
subplans,
returningLists,
rowMarks,
+ mergeActSets,
SS_assign_special_param(root));
}
@@ -993,7 +1208,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
if (parse->groupClause)
preprocess_groupclause(root);
numGroupCols = list_length(parse->groupClause);
-
/* Preprocess targetlist */
tlist = preprocess_targetlist(root, tlist);
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index bc8b770..d6fec59 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -78,6 +78,76 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
result_relation, range_table);
/*
+ for MERGE command, we also need to expend the target list of the main query.
+ Note that, the target list of main query is the combination of attrs
+ from Source table and target table. We only want to expend the part
+ of target table.
+
+ We do this in an aggressive way:
+ 1. Truncate the old target list, keep only the entries for source table
+ 2. expend the target list of result relation from an NIL list.
+ 3. Append this new list at the end of old target list.
+ */
+ if (command_type == CMD_MERGE)
+ {
+ ListCell *l;
+ List *TLforResultRelatoin = NIL;
+ int new_resno;
+
+ Assert(parse->sourceAttrNo > 0);
+
+ tlist = list_truncate(tlist, parse->sourceAttrNo);
+
+ TLforResultRelatoin = expand_targetlist(TLforResultRelatoin, command_type,
+ result_relation, range_table);
+ new_resno = parse->sourceAttrNo + 1;
+ foreach(l, TLforResultRelatoin)
+ {
+ TargetEntry *te = (TargetEntry *)lfirst(l);
+ te->resno = new_resno++;
+ }
+
+ tlist = list_concat(tlist, TLforResultRelatoin);
+ }
+
+ /*
+ * 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 expand the ctid attribute in tlist.
+ * The tlist of the merge top level plan already contains
+ * a "ctid" junk attr of the target relation.
+ */
+ if (!parse->isMergeAction &&
+ (command_type == CMD_UPDATE ||
+ command_type == CMD_DELETE ||
+ command_type == CMD_MERGE))
+ {
+ TargetEntry *tle;
+ Var *var;
+
+ var = makeVar(result_relation, SelfItemPointerAttributeNumber,
+ TIDOID, -1, 0);
+ tle = makeTargetEntry((Expr *) var,
+ list_length(tlist) + 1,
+ pstrdup("ctid"),
+ true);
+ /*
+ * For an UPDATE, expand_targetlist already created a fresh tlist. For
+ * DELETE, better do a listCopy so that we don't destructively modify
+ * the original tlist (is this really necessary?).
+ */
+ if (command_type == CMD_DELETE)
+ tlist = list_copy(tlist);
+
+ tlist = lappend(tlist, tle);
+ }
+
+ /*
* Add necessary junk columns for rowmarked rels. These values are needed
* for locking of rels selected FOR UPDATE/SHARE, and to do EvalPlanQual
* rechecking. While we are at it, store these junk attnos in the
@@ -303,6 +373,7 @@ expand_targetlist(List *tlist, int command_type,
}
break;
case CMD_UPDATE:
+ case CMD_MERGE:
if (!att_tup->attisdropped)
{
new_expr = (Node *) makeVar(result_relation,
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 4686578..74e733a 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -101,9 +101,6 @@ static Bitmapset *translate_col_privs(const Bitmapset *parent_privs,
static Node *adjust_appendrel_attrs_mutator(Node *node,
AppendRelInfo *context);
static Relids adjust_relid_set(Relids relids, Index oldrelid, Index newrelid);
-static List *adjust_inherited_tlist(List *tlist,
- AppendRelInfo *context);
-
/*
* plan_set_operations
@@ -1742,8 +1739,9 @@ adjust_relid_set(Relids relids, Index oldrelid, Index newrelid)
* scribble on.
*
* Note that this is not needed for INSERT because INSERT isn't inheritable.
+ * But the INSERT actions in MERGE need this function
*/
-static List *
+List *
adjust_inherited_tlist(List *tlist, AppendRelInfo *context)
{
bool changed_it = false;
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index b26decc..b45d07a 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -67,6 +67,14 @@ 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_top;
+ List *mainplanTL;
+} 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 +91,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 +687,85 @@ 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 target table, which is a plain table
+ * 3. join expression with the source 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 opposite 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, List *mainplanTL)
+{
+ push_up_merge_action_vars_context context;
+
+ context.varno_source = Merge_SourceTableRTindex;
+ context.varno_target = actqry->resultRelation;
+ context.varno_top = 0;
+ context.mainplanTL = mainplanTL;
+
+ 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;
+ ListCell *TargetEntry_in_mainplan;
+
+ /*try to find a match var in the main plan TL*/
+ foreach(TargetEntry_in_mainplan, context->mainplanTL)
+ {
+ TargetEntry *te = lfirst(TargetEntry_in_mainplan);
+ Var *mainvar;
+
+ /*the main plan target list contains (almost) only plain Var nodes as its expressions*/
+ if(IsA(te->expr, Var))
+ mainvar = (Var *)(te->expr);
+ else
+ continue;
+
+ if(mainvar->varno == var->varno && mainvar->varattno == var->varattno)
+ {
+ var->varno = context->varno_top;
+ var->varattno = te->resno;
+ return false;
+ }
+ }
+
+ /*mactching var is not found*/
+ elog(ERROR, "the vars in merge action tlist of qual should only belongs to the source table or target 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 3d84f61..90ec4c9 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -48,6 +48,7 @@ static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static List *transformInsertRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos);
static int count_rowexpr_columns(ParseState *pstate, Node *expr);
+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);
@@ -176,6 +177,10 @@ transformStmt(ParseState *pstate, Node *parseTree)
result = transformUpdateStmt(pstate, (UpdateStmt *) parseTree);
break;
+ case T_MergeStmt:
+ result = transformMergeStmt(pstate, (MergeStmt *)parseTree);
+ break;
+
case T_SelectStmt:
{
SelectStmt *n = (SelectStmt *) parseTree;
@@ -246,6 +251,7 @@ analyze_requires_snapshot(Node *parseTree)
case T_DeleteStmt:
case T_UpdateStmt:
case T_SelectStmt:
+ case T_MergeStmt:
result = true;
break;
@@ -299,12 +305,24 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
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.
+ * The input stmt could be a MergeDelete node.
+ * In this case, we don't need the process on range table.
*/
- transformFromClause(pstate, stmt->usingClause);
+ if (!stmt->isMergeAction)
+ {
+ /* 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");
@@ -369,6 +387,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
* VALUES clause. If we have any of those, treat it as a general SELECT;
* so it will work, but you can't use DEFAULT items together with those.
*/
+
+ /* a MergeInsert statement is always a VALUES clause*/
isGeneralSelect = (selectStmt && (selectStmt->valuesLists == NIL ||
selectStmt->sortClause != NIL ||
selectStmt->limitOffset != NULL ||
@@ -407,7 +427,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 (!stmt->isMergeAction) /* 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 */
@@ -1805,16 +1826,18 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qry->cteList = transformWithClause(pstate, stmt->withClause);
}
- 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);
+ if (!stmt->isMergeAction) /* 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);
+ }
qry->targetList = transformTargetList(pstate, stmt->targetList);
@@ -2316,3 +2339,405 @@ 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;
+
+ /*
+ * First, we need to make sure that DELETE and UPDATE
+ * actions are only taken in MATCHED condition,
+ * and INSERTs are only taken when not MATCHED
+ */
+ switch(condact->action->type)
+ {
+ case T_DeleteStmt:/*a delete action*/
+ {
+ DeleteStmt *deleteact = (DeleteStmt *) condact->action;
+ Assert(deleteact->isMergeAction);
+
+ 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 relation.
+ This line chages the RTE in range table directly*/
+ pstate->p_target_rangetblentry->requiredPerms |= ACL_DELETE;
+
+ deleteact->relation = stmt->relation;
+ deleteact->usingClause = list_make1(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_UpdateStmt:/*an update action*/
+ {
+ UpdateStmt *updateact = (UpdateStmt *) condact->action;
+ Assert(updateact->isMergeAction);
+
+ 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 = list_make1(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_InsertStmt:/*an insert action*/
+ {
+ InsertStmt *insertact = (InsertStmt *) condact->action;
+ Assert(insertact->isMergeAction);
+
+ 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 target 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 consistency 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;
+
+ 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;
+ default:
+ elog(ERROR, "unknown MERGE action type %d", condact->action->type);
+ break;
+ }
+
+ /*never comes here*/
+ return NULL;
+}
+
+static bool
+VarIsInResultList(Var *var, List *result)
+{
+ ListCell *each;
+
+ if(var == NULL || result == NIL)
+ return false;
+
+ foreach(each, result)
+ {
+ TargetEntry *resentry = (TargetEntry *)lfirst(each);
+ Var *entryVar = (Var *)(resentry->expr);
+
+ Assert(IsA(entryVar, Var));
+
+ if(entryVar->varattno == var->varattno &&
+ entryVar->varno == var->varno)
+ return true;
+ }
+
+ return false;
+}
+
+static List *
+collectSysAttrVarInMergeActions(List *mergeQrys)
+{
+ List *result = NIL;
+ ListCell *eachActQry;
+
+ if(mergeQrys == NIL)
+ return NIL;
+
+ foreach(eachActQry, mergeQrys)
+ {
+ Query *actqry = (Query *)lfirst(eachActQry);
+ List *varsInQry = NIL;
+ ListCell *eachVar;
+
+ Assert(IsA(actqry, Query));
+
+ /*we are going to check the target list and quals for each query*/
+ varsInQry = list_concat(
+ pull_var_clause((Node *)(actqry->targetList), PVC_RECURSE_PLACEHOLDERS),
+ pull_var_clause((Node *)(actqry->jointree->quals), PVC_RECURSE_PLACEHOLDERS));
+
+ foreach(eachVar, varsInQry)
+ {
+ Var *qryvar = (Var *)lfirst(eachVar);
+ Assert(IsA(qryvar, Var));
+
+ /*
+ *if we get an SysAttr Var, try to add it into the result list.
+ *Avoid adding one Var repeatedly.
+ */
+ if(qryvar->varattno < 0 && !VarIsInResultList(qryvar, result))
+ result = lappend(result,
+ makeTargetEntry(copyObject(qryvar), 0, NULL, false));
+ }
+ }
+ return result;
+}
+
+/*
+ * 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 target 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.
+ */
+static Query *
+transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
+{
+ Query *qry;
+
+ ColumnRef *starRef;
+ ResTarget *starResTarget;
+ ListCell *act;
+ ListCell *l;
+ JoinExpr *joinexp;
+ RangeTblEntry *resultRTE = NULL;
+ List *sysAttrVars = NIL;
+ int resno;
+
+ /*firstly, create the output node structure*/
+ qry = makeNode(Query);
+ qry->commandType = CMD_MERGE;
+
+ /*
+ * Before transforming the FROM clause, acquire write lock on the
+ * target relation. We don't want to add it to the range table yet,
+ * so we use setTargetTableLock() instead of setTargetTable().
+ */
+ setTargetTableLock(pstate, stmt->relation);
+
+ /*
+ * Make the join expression on source table and target table,
+ * as the only element in FROM list
+ */
+ joinexp = makeNode(JoinExpr);
+ joinexp->jointype = JOIN_LEFT;
+ joinexp->isNatural = FALSE;
+ joinexp->larg = stmt->source;
+ joinexp->rarg = (Node *)stmt->relation;
+ joinexp->quals = stmt->matchCondition;
+
+ /*
+ * transform the FROM clause. The target relation and
+ * source relation will be added to the range table here.
+ *
+ *After transformation of the FromCluase, the range table of
+ *MERGE command main query will contain only 3 RTE.
+ *RTE 1 is the source table, RTE 2 is the target table, RTE 3 is the left join
+ *between source and target table.
+ */
+ transformFromClause(pstate, list_make1(joinexp));
+
+ /*
+ *Here we want to set the var name space to contain only the source and target table.
+ *Thus, in later process for target list and quals of MERGE actions, all the vars will point
+ *to these two tables.
+ */
+ pstate->p_varnamespace = list_make2(rt_fetch(Merge_SourceTableRTindex, pstate->p_rtable),
+ rt_fetch(Merge_TargetTableRTindex, pstate->p_rtable));
+
+ /*Finally, set RTE 2 as the result relation of query*/
+ qry->resultRelation = Merge_TargetTableRTindex;
+ resultRTE = rt_fetch(qry->resultRelation, pstate->p_rtable);
+ if(resultRTE->relid != pstate->p_target_relation->rd_id)
+ elog(ERROR, "The target relation entry should be the second one in range table");
+
+ resultRTE->requiredPerms = ACL_SELECT;
+ resultRTE->inh = interpretInhOption(stmt->relation->inhOpt);
+ pstate->p_target_rangetblentry = resultRTE;
+ qry->rtable = pstate->p_rtable;
+
+ /*The FROM clause is done. Since we don't need a WHERE clause here, we set it null. */
+ qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
+
+ /*
+ * For each MERGE action, transform it to a separate query.
+ * The action queries share the range table with the main query.
+ *
+ * In other words, in the extra conditions 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);
+
+ /*
+ * We don't invoke setTargetTable() in transformMergeActions(),
+ * (to avoid adding the same table into RT over and over again)
+ * So, we need to set actqry->resultRelation here.
+ */
+ actqry->resultRelation = qry->resultRelation;
+
+ /*record the source attr number in each action query, for latter use*/
+ actqry->sourceAttrNo = qry->sourceAttrNo;
+
+ /* put it into the list */
+ qry->mergeActQry = lappend(qry->mergeActQry, actqry);
+ }
+
+ /*
+ * set the sublink mark after the MERGE action transformation.
+ * Thus, the sublink in actions will be counted in.
+ */
+ qry->hasSubLinks = pstate->p_hasSubLinks;
+
+ /*The last step is to process the target list for main query, which 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 also need to scan all the vars in all MERGE actions.
+ *If any system attributes (usually, the oids) are reffered in Vars,
+ *We need to add them into the main target list too.
+ */
+ sysAttrVars = collectSysAttrVarInMergeActions(qry->mergeActQry);
+ qry->targetList = list_concat(sysAttrVars, qry->targetList);
+
+ /*
+ *Since new TE may be added into TL, the resno should be reset.
+ *In the same time, we also can find out the number of attributes in TL that are
+ *not common attributes of the target table.
+ */
+ qry->sourceAttrNo = 0;
+ resno = 1;
+ foreach(l, qry->targetList)
+ {
+ TargetEntry *te = (TargetEntry *)lfirst(l);
+ Var *tevar = (Var *)(te->expr);
+ Assert(IsA(tevar, Var));
+
+ te->resno = resno++;
+ if(tevar->varno != Merge_TargetTableRTindex ||
+ tevar->varattno < 0)
+ qry->sourceAttrNo++;
+ }
+
+ return qry;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1c17be8..0a7fd72 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -212,6 +212,11 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
DeallocateStmt PrepareStmt ExecuteStmt
DropOwnedStmt ReassignOwnedStmt
AlterTSConfigurationStmt AlterTSDictionaryStmt
+ MergeStmt
+
+%type <node> 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
@@ -483,7 +488,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
- EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT
+ EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ERROR_P ESCAPE EXCEPT
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
@@ -506,7 +511,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP
LOCATION LOCK_P LOGIN_P
- MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
+ MAPPING MATCH MATCHED MAXVALUE MERGE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
@@ -521,7 +526,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
QUOTE
- RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
+ RAISE RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
@@ -731,6 +736,7 @@ stmt :
| ListenStmt
| LoadStmt
| LockStmt
+ | MergeStmt
| NotifyStmt
| PrepareStmt
| ReassignOwnedStmt
@@ -7177,6 +7183,7 @@ ExplainableStmt:
| InsertStmt
| UpdateStmt
| DeleteStmt
+ | MergeStmt
| DeclareCursorStmt
| CreateAsStmt
| ExecuteStmt /* by default all are $$=$1 */
@@ -7524,6 +7531,112 @@ 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;
+ m->source = $5;
+ m->matchCondition = $7;
+ 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
+ {
+ DeleteStmt *n = makeNode(DeleteStmt);
+ n->isMergeAction = true;
+ $$ = (Node *) n;
+ }
+ | UPDATE SET set_clause_list
+ {
+ UpdateStmt *n = makeNode(UpdateStmt);
+ n->targetList = $3;
+ n->isMergeAction = true;
+
+ $$ = (Node *) n;
+ }
+ | INSERT values_clause
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = NIL;
+ n->selectStmt = $2;
+ n->isMergeAction = true;
+
+ $$ = (Node *) n;
+ }
+
+ | INSERT '(' insert_column_list ')' values_clause
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = $3;
+ n->selectStmt = $5;
+ n->isMergeAction = true;
+
+ $$ = (Node *) n;
+ }
+ | INSERT DEFAULT VALUES
+ {
+ InsertStmt *n = makeNode(InsertStmt);
+ n->cols = NIL;
+ n->selectStmt = NULL;
+ n->isMergeAction = true;
+
+ $$ = (Node *) n;
+ }
+ | DO NOTHING
+ {
+ $$ = (Node *) makeNode(MergeDoNothing);
+ }
+ ;
+
+
+
+/*****************************************************************************
+ *
+ * QUERY:
* CURSOR STATEMENTS
*
*****************************************************************************/
@@ -11156,6 +11269,7 @@ unreserved_keyword:
| ENCODING
| ENCRYPTED
| ENUM_P
+ | ERROR_P
| ESCAPE
| EXCLUDE
| EXCLUDING
@@ -11210,7 +11324,9 @@ unreserved_keyword:
| LOGIN_P
| MAPPING
| MATCH
+ | MATCHED
| MAXVALUE
+ | MERGE
| MINUTE_P
| MINVALUE
| MODE
@@ -11254,6 +11370,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 898c55e..2db6f85 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 a332611..24a7771 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1118,6 +1118,9 @@ rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte,
const char *attrname;
TargetEntry *tle;
+ if(parsetree->isMergeAction)
+ return;
+
if (target_relation->rd_rel->relkind == RELKIND_RELATION)
{
/*
@@ -2000,6 +2003,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 -
@@ -2025,7 +2063,148 @@ 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, we have a set of action queries (not subquery).
+ * each of these action queries should rewritten with 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 action*/
+ if (actionqry->commandType == CMD_DONOTHING)
+ continue;
+
+ /*
+ * if this kind of actions are fully replaced by rules,
+ * we change it into a DO NOTHING action
+ */
+ 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->commandType = CMD_DONOTHING;
+ actionqry->targetList = NIL;
+ 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 empty, 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->commandType = CMD_DONOTHING;
+ actionqry->targetList = NIL;
+ 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 action)
+ * 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 rewritten query list,
+ * which means the action is replaced by INSTEAD rule(s).
+ * We need to change it into do noting action.
+ *
+ * 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->commandType = CMD_DONOTHING;
+ actionqry->targetList = NIL;
+ }
+
+ /*
+ * 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/postgres.c b/src/backend/tcop/postgres.c
index ff2e9bd..849e5d4 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -625,7 +625,6 @@ pg_analyze_and_rewrite(Node *parsetree, const char *query_string,
ResetUsage();
query = parse_analyze(parsetree, query_string, paramTypes, numParams);
-
if (log_parser_stats)
ShowUsage("PARSE ANALYSIS STATISTICS");
@@ -984,9 +983,7 @@ exec_simple_query(const char *query_string)
querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
NULL, 0);
-
plantree_list = pg_plan_queries(querytree_list, 0, NULL);
-
/* Done with the snapshot used for parsing/planning */
if (snapshot_set)
PopActiveSnapshot();
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 8eb02da..52ed285 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 2300e88..04b55b3 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -126,6 +126,7 @@ CommandIsReadOnly(Node *parsetree)
case CMD_UPDATE:
case CMD_INSERT:
case CMD_DELETE:
+ case CMD_MERGE:
return false;
default:
elog(WARNING, "unrecognized commandType: %d",
@@ -1423,6 +1424,10 @@ CreateCommandTag(Node *parsetree)
tag = "SELECT";
break;
+ case T_MergeStmt:
+ tag = "MERGE";
+ break;
+
/* utility statements --- same whether raw or cooked */
case T_TransactionStmt:
{
@@ -2272,6 +2277,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 30dc314..a41a169 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -178,6 +178,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 a8a28a4..96b46f0 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 89f8e20..b9b3bb1 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1031,9 +1031,23 @@ 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? */
+ MergeActionSet **mt_mergeActPstates; /*the list of the planstate of merge command actions.
+ NULL if this is not a merge command.
+ The elements if it are still MergeActionSet nodes.
+ But the action list in these nodes are ModifyTableState */
} 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 bc96ebf..bbb14cb 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -44,6 +44,8 @@ typedef enum NodeTag
T_Plan = 100,
T_Result,
T_ModifyTable,
+ T_MergeAction,
+ T_MergeActionSet,
T_Append,
T_MergeAppend,
T_RecursiveUnion,
@@ -87,6 +89,7 @@ typedef enum NodeTag
T_PlanState = 200,
T_ResultState,
T_ModifyTableState,
+ T_MergeActionState,
T_AppendState,
T_MergeAppendState,
T_RecursiveUnionState,
@@ -352,6 +355,10 @@ typedef enum NodeTag
T_AlterUserMappingStmt,
T_DropUserMappingStmt,
T_AlterTableSpaceOptionsStmt,
+ T_MergeStmt,
+ T_MergeConditionAction,
+ T_MergeDoNothing,
+ T_MergeError,
T_SecLabelStmt,
/*
@@ -517,6 +524,8 @@ typedef enum CmdType
CMD_UPDATE, /* update stmt */
CMD_INSERT, /* insert stmt */
CMD_DELETE,
+ CMD_MERGE, /* merge stmt */
+ CMD_DONOTHING, /*DO NOTHING action in MERGE command*/
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 8b34b76..9466a2f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -149,8 +149,29 @@ typedef struct Query
List *constraintDeps; /* a list of pg_constraint OIDs that the query
* depends on to be semantically valid */
+
+ /* fields for MERGE command */
+ bool isMergeAction; /* if this query is a merge action. */
+ bool matched; /* this is a MATCHED action or NOT */
+ int sourceAttrNo; /*the number of attributes in source table*/
+ List *mergeActQry; /* the list of all the merge actions.
+ * used only for merge query statement */
} Query;
+/*
+* In MERGE command, the initial MERGE query has only three range table entry
+*The first one is the source table; The second one is the target table; and The
+*third one is the left join entry of them.
+*
+*During the whole process of a MEREGE command, the rt_index for the source table
+*entry and the join table entry will never change. We set them as constant here.
+*
+*NOTE: the target table RT index may change, if the children tables are invovled by inheritance.
+*So, don't use the constant after rewriter.
+*/
+#define Merge_SourceTableRTindex 1
+#define Merge_TargetTableRTindex 2
+#define Merge_TopJoinTableRTindex 3
/****************************************************************************
* Supporting data structures for Parse Trees
@@ -900,6 +921,7 @@ typedef struct CommonTableExpr
typedef struct InsertStmt
{
NodeTag type;
+ bool isMergeAction; /*if this is a merge insert action*/
RangeVar *relation; /* relation to insert into */
List *cols; /* optional: names of the target columns */
Node *selectStmt; /* the source SELECT/VALUES, or NULL */
@@ -914,6 +936,7 @@ typedef struct InsertStmt
typedef struct DeleteStmt
{
NodeTag type;
+ bool isMergeAction; /*if this is a merge delete action*/
RangeVar *relation; /* relation to delete from */
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
@@ -928,6 +951,7 @@ typedef struct DeleteStmt
typedef struct UpdateStmt
{
NodeTag type;
+ bool isMergeAction; /*if this is a merge delete action*/
RangeVar *relation; /* relation to update */
List *targetList; /* the target list (of ResTarget) */
Node *whereClause; /* qualifications */
@@ -1004,6 +1028,54 @@ typedef struct SelectStmt
/* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
+/* ----------------------
+ * Merge Statement
+ * ----------------------
+ */
+typedef struct MergeStmt
+{
+ NodeTag type;
+ RangeVar *relation; /* target relation for merge */
+
+ /*
+ * source relations for the merge.
+ * Currently, we only allow single-source merge,
+ * so the length of this list should always be 1.
+ */
+ Node *source;
+ Node *matchCondition; /* qualifications of the merge */
+
+ /* list of MergeConditionAction structure.
+ * It stores all the matched / not-matched
+ * conditions and the corresponding actions
+ * The elements 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; /* WHEN MATCHED or WHEN NOT MATCHED? */
+ Node *condition; /* the AND condition for this action */
+ Node *action; /* the actions: delete, insert or update */
+} MergeConditionAction;
+
+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 fec4ace..c4022e3 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -169,9 +169,38 @@ 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 MergeActionSet nodes.
+ * one set for one target relation */
} ModifyTable;
/* ----------------
+ * MergeActionSet node -
+ * The node contains all actions of MERGE command for one specific target relation
+ * ----------------
+ */
+typedef struct MergeActionSet
+{
+ NodeTag type;
+ int result_relation;
+ List *actions;
+}MergeActionSet;
+
+/* ----------------
+ * MergeAction node -
+ * The plan node for the actions of MERGE command
+ * ----------------
+ */
+typedef struct MergeAction
+{
+ Plan plan;
+ CmdType operation; /* INSERT, UPDATE, DELETE, DO_NOTHING or RAISE_ERR */
+ bool matched; /* is this a MATCHED or NOT MATCHED rule? */
+ List *flattenedqual; /* the flattened qual expression of action */
+ List *flattenedtlist;/*the fattened target list*/
+} MergeAction;
+
+/* ----------------
* Append node -
* Generate the concatenation of the results of sub-plans.
* ----------------
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 919449b..dcefbf1 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -80,7 +80,7 @@ extern Result *make_result(PlannerInfo *root, List *tlist,
Node *resconstantqual, Plan *subplan);
extern ModifyTable *make_modifytable(CmdType operation, List *resultRelations,
List *subplans, List *returningLists,
- List *rowMarks, int epqParam);
+ List *rowMarks, List *mergeActPlans, int epqParam);
extern bool is_projection_capable_plan(Plan *plan);
/*
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index 9f8e379..7af6d35 100644
--- a/src/include/optimizer/prep.h
+++ b/src/include/optimizer/prep.h
@@ -54,4 +54,6 @@ extern void expand_inherited_tables(PlannerInfo *root);
extern Node *adjust_appendrel_attrs(Node *node, AppendRelInfo *appinfo);
+extern List *adjust_inherited_tlist(List * tlist, AppendRelInfo * context);
+
#endif /* PREP_H */
diff --git a/src/include/optimizer/var.h b/src/include/optimizer/var.h
index 8fd9977..c99feb5 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,6 @@ 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, List *mainplanTL);
#endif /* VAR_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 2c44cf7..d777aaf 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -142,6 +142,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)
@@ -231,7 +232,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)
@@ -298,6 +301,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 0e8a80e..831421b 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..7ee155a
--- /dev/null
+++ b/src/test/regress/expected/merge.out
@@ -0,0 +1,202 @@
+-- MERGE --
+--
+-- create basic tables for test, and insert some source rows to work from
+--
+--The Stock table, which records the amount of each item on hand.
+CREATE TABLE Stock(item_id int UNIQUE, balance int);
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "stock_item_id_key" for table "stock"
+INSERT INTO Stock VALUES (10, 2200);
+INSERT INTO Stock VALUES (20, 1900);
+SELECT * FROM Stock;
+ item_id | balance
+---------+---------
+ 10 | 2200
+ 20 | 1900
+(2 rows)
+
+--The Buy table, which records the amount we bought today for each item.
+CREATE TABLE Buy(item_id int, volume int);
+INSERT INTO Buy values(10, 1000);
+INSERT INTO Buy values(30, 300);
+SELECT * FROM Buy;
+ item_id | volume
+---------+--------
+ 10 | 1000
+ 30 | 300
+(2 rows)
+
+--The Sale table, which records the amount we sold today for each item.
+CREATE TABLE Sale(item_id int, volume int);
+INSERT INTO Sale VALUES (10, 2200);
+INSERT INTO Sale VALUES (20, 1000);
+SELECT * FROM Sale;
+ item_id | volume
+---------+--------
+ 10 | 2200
+ 20 | 1000
+(2 rows)
+
+--
+-- initial queries
+--
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 10 | 3200
+ 20 | 1900
+(2 rows)
+
+ROLLBACK;
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume)
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 10 | 2200
+ 20 | 1900
+ 30 | 300
+(3 rows)
+
+ROLLBACK;
+-- now the classic UPSERT
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
+ WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume)
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 10 | 3200
+ 20 | 1900
+ 30 | 300
+(3 rows)
+
+ROLLBACK;
+--
+-- Non-standard functionality
+--
+-- a MERGE with DELETE action, which is not allowed in Standard.
+-- Extra qualifications are allowed in each WHEN clause.
+BEGIN;
+MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
+ WHEN MATCHED AND balance - volume > 0 THEN UPDATE SET balance = balance - volume
+ WHEN MATCHED THEN DELETE;
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 20 | 900
+(1 row)
+
+ROLLBACK;
+-- The DO NOTHING action is another extension for MERGE.
+-- rows specified by DO NOTHING are ignored
+BEGIN;
+MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
+ WHEN MATCHED AND balance - volume > 0 THEN DO NOTHING
+ WHEN MATCHED THEN DELETE
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 20 | 1900
+(1 row)
+
+ROLLBACK;
+-- DO NOTHING is the default action for non-matching rows that do not
+-- activate any WHEN clause. They are just ignored
+BEGIN;
+MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
+ WHEN MATCHED AND balance - volume > 100000 THEN UPDATE SET balance = balance - volume
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 10 | 2200
+ 20 | 1900
+(2 rows)
+
+ROLLBACK;
+-- Prepare the test data to generate multiple matching rows for a single target
+INSERT INTO Buy values(10, 400);
+SELECT * FROM Buy ORDER BY item_id;
+ item_id | volume
+---------+--------
+ 10 | 1000
+ 10 | 400
+ 30 | 300
+(3 rows)
+
+-- we now have a duplicate key in Buy, so when we join to
+-- Stock we will generate 2 matching rows, not one.
+-- According to standard this command should fail.
+-- But it succeeds in PostgreSQL implementation by simply ignoring the second
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 10 | 3200
+ 20 | 1900
+(2 rows)
+
+ROLLBACK;
+--
+-- More complicated query
+--
+-- The source table of MERGE could be a SELECT clause
+BEGIN;
+MERGE INTO Stock USING
+ (SELECT Buy.item_id, (Buy.volume - Sale.volume) as v
+ FROM Buy, Sale
+ WHERE Buy.item_id = Sale.item_id)
+ AS BS
+ ON Stock.item_id = BS.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + BS.v
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 10 | 1000
+ 20 | 1900
+(2 rows)
+
+ROLLBACK;
+-- Subplan/sublinks can be used in MERGE actions
+-- Create a table for sublink.
+CREATE TABLE Extra (item_id int, volume int);
+INSERT INTO Extra VALUES (10, 20);
+INSERT INTO Extra VALUES (10, -7);
+INSERT INTO Extra VALUES (20, 16);
+INSERT INTO Extra VALUES (20, 5);
+INSERT INTO Extra VALUES (30, 9);
+-- The following query sum-up the volumes in Extra table and upinsert the Stock.
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET
+ balance = balance + Buy.volume +
+ (SELECT sum(volume) FROM Extra WHERE Extra.item_id = Buy.item_id)
+ WHEN NOT MATCHED THEN INSERT VALUES
+ (Buy.item_id, Buy.volume +
+ (SELECT sum(volume) FROM Extra WHERE Extra.item_id = Buy.item_id))
+;
+SELECT * FROM Stock ORDER BY item_id;
+ item_id | balance
+---------+---------
+ 10 | 3213
+ 20 | 1900
+ 30 | 309
+(3 rows)
+
+ROLLBACK;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 3b99e86..9c2d298 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 b348f0e..fde54d3 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -124,4 +124,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..5cdb5db
--- /dev/null
+++ b/src/test/regress/sql/merge.sql
@@ -0,0 +1,135 @@
+-- MERGE --
+
+--
+-- create basic tables for test, and insert some source rows to work from
+--
+--The Stock table, which records the amount of each item on hand.
+CREATE TABLE Stock(item_id int UNIQUE, balance int);
+INSERT INTO Stock VALUES (10, 2200);
+INSERT INTO Stock VALUES (20, 1900);
+SELECT * FROM Stock;
+
+--The Buy table, which records the amount we bought today for each item.
+CREATE TABLE Buy(item_id int, volume int);
+INSERT INTO Buy values(10, 1000);
+INSERT INTO Buy values(30, 300);
+SELECT * FROM Buy;
+
+--The Sale table, which records the amount we sold today for each item.
+CREATE TABLE Sale(item_id int, volume int);
+INSERT INTO Sale VALUES (10, 2200);
+INSERT INTO Sale VALUES (20, 1000);
+SELECT * FROM Sale;
+
+--
+-- initial queries
+--
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume)
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+-- now the classic UPSERT
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
+ WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume)
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+--
+-- Non-standard functionality
+--
+-- a MERGE with DELETE action, which is not allowed in Standard.
+-- Extra qualifications are allowed in each WHEN clause.
+BEGIN;
+MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
+ WHEN MATCHED AND balance - volume > 0 THEN UPDATE SET balance = balance - volume
+ WHEN MATCHED THEN DELETE;
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+-- The DO NOTHING action is another extension for MERGE.
+-- rows specified by DO NOTHING are ignored
+BEGIN;
+MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
+ WHEN MATCHED AND balance - volume > 0 THEN DO NOTHING
+ WHEN MATCHED THEN DELETE
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+-- DO NOTHING is the default action for non-matching rows that do not
+-- activate any WHEN clause. They are just ignored
+BEGIN;
+MERGE INTO Stock USING Sale ON Stock.item_id = Sale.item_id
+ WHEN MATCHED AND balance - volume > 100000 THEN UPDATE SET balance = balance - volume
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+-- Prepare the test data to generate multiple matching rows for a single target
+INSERT INTO Buy values(10, 400);
+SELECT * FROM Buy ORDER BY item_id;
+
+-- we now have a duplicate key in Buy, so when we join to
+-- Stock we will generate 2 matching rows, not one.
+-- According to standard this command should fail.
+-- But it succeeds in PostgreSQL implementation by simply ignoring the second
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+--
+-- More complicated query
+--
+-- The source table of MERGE could be a SELECT clause
+BEGIN;
+MERGE INTO Stock USING
+ (SELECT Buy.item_id, (Buy.volume - Sale.volume) as v
+ FROM Buy, Sale
+ WHERE Buy.item_id = Sale.item_id)
+ AS BS
+ ON Stock.item_id = BS.item_id
+ WHEN MATCHED THEN UPDATE SET balance = balance + BS.v
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
+
+-- Subplan/sublinks can be used in MERGE actions
+-- Create a table for sublink.
+CREATE TABLE Extra (item_id int, volume int);
+INSERT INTO Extra VALUES (10, 20);
+INSERT INTO Extra VALUES (10, -7);
+INSERT INTO Extra VALUES (20, 16);
+INSERT INTO Extra VALUES (20, 5);
+INSERT INTO Extra VALUES (30, 9);
+
+-- The following query sum-up the volumes in Extra table and upinsert the Stock.
+BEGIN;
+MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
+ WHEN MATCHED THEN UPDATE SET
+ balance = balance + Buy.volume +
+ (SELECT sum(volume) FROM Extra WHERE Extra.item_id = Buy.item_id)
+ WHEN NOT MATCHED THEN INSERT VALUES
+ (Buy.item_id, Buy.volume +
+ (SELECT sum(volume) FROM Extra WHERE Extra.item_id = Buy.item_id))
+;
+SELECT * FROM Stock ORDER BY item_id;
+ROLLBACK;
Import Notes
Reply to msg id not found: AANLkTimX63UWgQxUU+3d_nRh7w6KKNuVbUK_QBp1vCi@mail.gmail.com
On Mon, December 6, 2010 14:17, Boxuan Zhai wrote:
I send the original file directly this time.
I get some whitespace-warnings, followed by error:
$ git apply /home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:481: trailing
whitespace.
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:482: trailing
whitespace.
if (IsA(plan, ModifyTable) &&
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:550: trailing
whitespace.
/*print the action qual*/
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:556: trailing
whitespace.
(act_plan->operation == CMD_INSERT ||
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:560: trailing
whitespace.
error: patch failed: src/backend/optimizer/plan/planner.c:739
error: src/backend/optimizer/plan/planner.c: patch does not apply
Erik Rijkers
Erik Rijkers wrote:
I get some whitespace-warnings, followed by error:
$ git apply /home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:481: trailing
whitespace./home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:482: trailing
whitespace.
if (IsA(plan, ModifyTable) &&
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:550: trailing
whitespace.
/*print the action qual*/
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:556: trailing
whitespace.
(act_plan->operation == CMD_INSERT ||
/home/rijkers/download/pgpatches/0091/merge/20101206/merge_204_2010DEC06.patch:560: trailing
whitespace.error: patch failed: src/backend/optimizer/plan/planner.c:739
error: src/backend/optimizer/plan/planner.c: patch does not appl
Maybe I'm doing something wrong, but I've never had good luck with git
apply. I took this patch and applied it the 12/15 copy of HEAD I had
checked out (trying to minimize drift in there since the patch was
created) using:
patch -p 1 < merge_204_2010DEC06.patch
There was one trivial conflict it produced
src/backend/optimizer/plan/planner.c.rej for, and that fix was
straightforward to apply by hand.
The result is now sitting as the merge204 branch in my github repo:
https://github.com/greg2ndQuadrant/postgres/tree/merge204 if you did
want to try this out.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
I did some basic testing of the latest update here, but quickly hit a
problem that wasn't in the previous version. Attached is the standalone
test script that used to work, but now fails like this:
psql:simple.sql:12: ERROR: the vars in merge action tlist of qual
should only belongs to the source table or target table
This test case is intended to implement the common UPSERT situation that
is one of the main requests that MERGE is intended to satisfy, using
this syntax:
MERGE INTO Stock t
USING (VALUES(10,100)) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
;
If you can suggest an alternate way to express this that works with the
new patch, I might switch to that and retry. I was never 100% sure this
was the right way to write this, and I don't have another database with
MERGE support here to try against. (Aside: if someone else does, I'd
be really curious to see if the attached test case works or not on
another database system. I think we need to include compatibility
testing with other MERGE implementations into the test mix here soon.)
Regardless, this failure suggests that you need to add this sort of test
to the regression test set. We need to have an example of an UPSERT
using constant data in there to make sure this continues to work in the
future.
This is a good week for me in terms of having time for PostgreSQL
hacking, so if you can suggest something here or update the patch I'll
try it soon afterwards.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Attachments:
On 2010-12-29 2:14 PM, Greg Smith wrote:
MERGE INTO Stock t
USING (VALUES(10,100)) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
;If you can suggest an alternate way to express this that works with the
new patch, I might switch to that and retry. I was never 100% sure this
was the right way to write this, and I don't have another database with
MERGE support here to try against.
As far as I can tell, this should work. I played around with the patch
and the problem seems to be the VALUES:
INTO Stock t
USING (SELECT 30, 2000) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
;
MERGE 1
Regards,
Marko Tiikkaja
Marko Tiikkaja wrote:
As far as I can tell, this should work. I played around with the
patch and the problem seems to be the VALUES:INTO Stock t
USING (SELECT 30, 2000) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
;
MERGE 1
Good catch...while I think the VALUES syntax should work, that is a
useful workaround so I could keep testing. I rewrote like this
(original syntax commented out):
MERGE INTO Stock t
-- USING (VALUES(10,100)) AS s(item_id,balance)
USING (SELECT 10,100) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
;
And that got me back again to concurrent testing.
Moving onto next two problems...the basic MERGE feature seems to have
stepped backwards a bit too. I'm now seeing these quite often:
ERROR: duplicate key value violates unique constraint
"pgbench_accounts_pkey"
DETAIL: Key (aid)=(176641) already exists.
STATEMENT: MERGE INTO pgbench_accounts t USING (SELECT 176641,1+(176641
/ 1000000)::integer,168,'') AS s(aid,bid,balance,filler) ON s.aid=t.aid
WHEN MATCHED THEN UPDATE SET abalance=abalance + s.balance WHEN NOT
MATCHED THEN INSERT VALUES(s.aid,s.bid,s.balance,s.filler);
On my concurrent pgbench test, which had been working before. Possibly
causing that, the following assertion is tripping:
TRAP: FailedAssertion("!(epqstate->origslot != ((void *)0))", File:
"execMain.c", Line: 1762)
That's coming from the following code:
void
EvalPlanQualFetchRowMarks(EPQState *epqstate)
{
ListCell *l;
Assert(epqstate->origslot != NULL);
foreach(l, epqstate->rowMarks)
Stepping back to summarize...here's a list of issues I know about with
the current v204 code:
1) VALUE syntax doesn't work anymore
2) Assertion failure in EvalPlanQualFetchRowMarks
3) Duplicate key bug (possibly a direct result of #3)
4) Attempts to use MERGE in a fuction spit back "ERROR: <table> is not
a known fuction"
5) The ctid junk attr handling needs to be reviewed more carefully,
based on author request.
I've attached the current revisions of all my testing code in hopes that
Boxuan might try and replicate these (this makes it simple to replicate
#1 through #3), and therefore confirm whether changes made do better.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
On 2010-12-30 4:39 AM +0200, Greg Smith wrote:
And that got me back again to concurrent testing.
Moving onto next two problems...the basic MERGE feature seems to have
stepped backwards a bit too. I'm now seeing these quite often:ERROR: duplicate key value violates unique constraint
"pgbench_accounts_pkey"
DETAIL: Key (aid)=(176641) already exists.
STATEMENT: MERGE INTO pgbench_accounts t USING (SELECT 176641,1+(176641
/ 1000000)::integer,168,'') AS s(aid,bid,balance,filler) ON s.aid=t.aid
WHEN MATCHED THEN UPDATE SET abalance=abalance + s.balance WHEN NOT
MATCHED THEN INSERT VALUES(s.aid,s.bid,s.balance,s.filler);On my concurrent pgbench test, which had been working before.
I have no idea why it worked in the past, but the patch was never
designed to work for UPSERT. This has been discussed in the past and
some people thought that that's not a huge deal.
Regards,
Marko Tiikkaja
On Wed, Dec 29, 2010 at 9:45 PM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
I have no idea why it worked in the past, but the patch was never designed
to work for UPSERT. This has been discussed in the past and some people
thought that that's not a huge deal.
I think it's expected to fail in some *concurrent* UPSERT cases. It
should work if it's the only game in town.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Marko Tiikkaja wrote:
I have no idea why it worked in the past, but the patch was never
designed to work for UPSERT. This has been discussed in the past and
some people thought that that's not a huge deal.
It takes an excessively large lock when doing UPSERT, which means its
performance under a heavy concurrent load can't be good. The idea is
that if the syntax and general implementation issues can get sorted out,
fixing the locking can be a separate performance improvement to be
implemented later. Using MERGE for UPSERT is the #1 use case for this
feature by a gigantic margin. If that doesn't do what's expected, the
whole implementation doesn't provide the community anything really worth
talking about. That's why I keep hammering on this particular area in
all my testing.
One of the reflexive "I can't switch to PostgreSQL easily" stopping
points for MySQL users is "I can't convert my ON DUPLICATE KEY UPDATE
code". Every other use for MERGE is a helpful side-effect of adding the
implementation in my mind, but not the primary driver of why this is
important. My hints in this direction before didn't get adopted, so I'm
saying it outright now: this patch must have an UPSERT implementation
in its regression tests. And the first thing I'm going to do every time
a new rev comes in is try and break it with the pgbench test I
attached. If Boxuan can start doing that as part of his own testing, I
think development here might start moving forward faster. I don't care
so much about the rate at which concurrent UPSERT-style MERGE happens,
so long as it doesn't crash. But that's where this has been stuck at
for a while now.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
On 2010-12-30 9:02 AM +0200, Greg Smith wrote:
Marko Tiikkaja wrote:
I have no idea why it worked in the past, but the patch was never
designed to work for UPSERT. This has been discussed in the past and
some people thought that that's not a huge deal.It takes an excessively large lock when doing UPSERT, which means its
performance under a heavy concurrent load can't be good. The idea is
that if the syntax and general implementation issues can get sorted out,
fixing the locking can be a separate performance improvement to be
implemented later. Using MERGE for UPSERT is the #1 use case for this
feature by a gigantic margin. If that doesn't do what's expected, the
whole implementation doesn't provide the community anything really worth
talking about. That's why I keep hammering on this particular area in
all my testing.
I'm confused. Are you saying that the patch is supposed to lock the
table against concurrent INSERT/UPDATE/DELETE/MERGE? Because I don't
see it in the patch, and the symptoms you're having are a clear
indication of the fact that it's not happening. I also seem to recall
that people thought locking the table would be excessive.
Regards,
Marko Tiikkaja
On 12/30/2010 02:02 AM, Greg Smith wrote:
Marko Tiikkaja wrote:
I have no idea why it worked in the past, but the patch was never
designed to work for UPSERT. This has been discussed in the past and
some people thought that that's not a huge deal.It takes an excessively large lock when doing UPSERT, which means its
performance under a heavy concurrent load can't be good. The idea is
that if the syntax and general implementation issues can get sorted
out, fixing the locking can be a separate performance improvement to
be implemented later. Using MERGE for UPSERT is the #1 use case for
this feature by a gigantic margin. If that doesn't do what's
expected, the whole implementation doesn't provide the community
anything really worth talking about. That's why I keep hammering on
this particular area in all my testing.One of the reflexive "I can't switch to PostgreSQL easily" stopping
points for MySQL users is "I can't convert my ON DUPLICATE KEY UPDATE
code". Every other use for MERGE is a helpful side-effect of adding
the implementation in my mind, but not the primary driver of why this
is important. My hints in this direction before didn't get adopted,
so I'm saying it outright now: this patch must have an UPSERT
implementation in its regression tests. And the first thing I'm going
to do every time a new rev comes in is try and break it with the
pgbench test I attached. If Boxuan can start doing that as part of
his own testing, I think development here might start moving forward
faster. I don't care so much about the rate at which concurrent
UPSERT-style MERGE happens, so long as it doesn't crash. But that's
where this has been stuck at for a while now.
I strongly agree. It *is* a huge deal.
cheers
andrew
Marko Tiikkaja wrote:
I'm confused. Are you saying that the patch is supposed to lock the
table against concurrent INSERT/UPDATE/DELETE/MERGE? Because I don't
see it in the patch, and the symptoms you're having are a clear
indication of the fact that it's not happening. I also seem to recall
that people thought locking the table would be excessive.
That's exactly what it should be doing. I thought I'd seen just that in
one of the versions of this patch, but maybe that's a mistaken memory on
my part. In advance of the planned but not available yet ability to
lock individual index key values, locking the whole table is the only
possible implementation that can work correctly here I'm aware of. In
earlier versions, I think this code was running into issues before it
even got to there. If you're right that things like the duplicate key
error in the current version are caused exclusively by not locking
enough, that may be the next necessary step here.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
On Mon, 2011-01-03 at 01:53 -0500, Greg Smith wrote:
In advance of the planned but not available yet ability to
lock individual index key values, locking the whole table is the only
possible implementation that can work correctly here I'm aware of.
This was discussed here
http://archives.postgresql.org/pgsql-hackers/2010-10/msg01903.php
with suggested resolutions for this release here
http://archives.postgresql.org/pgsql-hackers/2010-10/msg01907.php
In summary, that means we can either
1. Lock the table for ShareRowExclusiveLock
2. throw a SERIALIZABLE error, if we come up against a row that cannot
be neither MATCHED nor NON MATCHED.
3. Bounce the patch to 9.2, commit early and then work on a full
concurrency solution before commit. The solution strawman is something
like EvalPlanQual with a new snapshot for each re-checked row, emulating
the pattern of snapshots/rechecks that would happen in a PL/pgSQL
version of an UPSERT.
Either way, we're saying that MERGE will not support concurrent
operations safely, in this release.
Given the continued lack of test cases for this patch, and the possible
embarrassment over not doing concurrent actions, do we think (3) is the
right road?
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On 03.01.2011 11:37, Simon Riggs wrote:
On Mon, 2011-01-03 at 01:53 -0500, Greg Smith wrote:
In advance of the planned but not available yet ability to
lock individual index key values, locking the whole table is the only
possible implementation that can work correctly here I'm aware of.This was discussed here
http://archives.postgresql.org/pgsql-hackers/2010-10/msg01903.php
with suggested resolutions for this release here
http://archives.postgresql.org/pgsql-hackers/2010-10/msg01907.phpIn summary, that means we can either
1. Lock the table for ShareRowExclusiveLock
2. throw a SERIALIZABLE error, if we come up against a row that cannot
be neither MATCHED nor NON MATCHED.3. Bounce the patch to 9.2, commit early and then work on a full
concurrency solution before commit. The solution strawman is something
like EvalPlanQual with a new snapshot for each re-checked row, emulating
the pattern of snapshots/rechecks that would happen in a PL/pgSQL
version of an UPSERT.Either way, we're saying that MERGE will not support concurrent
operations safely, in this release.Given the continued lack of test cases for this patch, and the possible
embarrassment over not doing concurrent actions, do we think (3) is the
right road?
This patch has never tried to implement concurrency-safe upsert. It
implements the MERGE command as specified by the SQL standard, nothing
more, nothing less. Let's not move the goalposts. Googling around, at
least MS SQL Server's MERGE command is the same
(http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx).
There is nothing embarrassing about it, we just have to document it clearly.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Mon, 2011-01-03 at 15:12 +0200, Heikki Linnakangas wrote:
This patch has never tried to implement concurrency-safe upsert. It
implements the MERGE command as specified by the SQL standard, nothing
more, nothing less. Let's not move the goalposts. Googling around, at
least MS SQL Server's MERGE command is the same
(http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx).
There is nothing embarrassing about it, we just have to document it clearly.
That article says that SQLServer supplies a locking hint that completely
removes the issue. Because they use locking, they are able to update in
place, so there is no need for them to use snapshots.
Our version won't allow a workaround yet, just for the record.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On Mon, Jan 3, 2011 at 8:35 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2011-01-03 at 15:12 +0200, Heikki Linnakangas wrote:
This patch has never tried to implement concurrency-safe upsert. It
implements the MERGE command as specified by the SQL standard, nothing
more, nothing less. Let's not move the goalposts. Googling around, at
least MS SQL Server's MERGE command is the same
(http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx).
There is nothing embarrassing about it, we just have to document it clearly.That article says that SQLServer supplies a locking hint that completely
removes the issue. Because they use locking, they are able to update in
place, so there is no need for them to use snapshots.Our version won't allow a workaround yet, just for the record.
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature. But I have to admit that the
discussion we've had thus far gives me very little confidence that
this code is anywhere close to bug-free. So I think we're going to
end up punting it to 9.2 not so much because it's not concurrency-safe
as because it doesn't work.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.
I'm still trying to figure out the problem with having the table-level
lock, unless we really think people will be doing concurrent MERGE's
where they won't overlap..? I'm also a bit nervous about if the result
of concurrent MERGE's would actually be correct if we're not taking a
bigger lock than row-level (I assume we're taking row-level locks as it
goes through..).
In general, I also thought/expected to have some kind of UPSERT type
capability with our initial MERGE support, even if it requires a big
lock and won't operate concurrently, etc.
But I have to admit that the
discussion we've had thus far gives me very little confidence that
this code is anywhere close to bug-free. So I think we're going to
end up punting it to 9.2 not so much because it's not concurrency-safe
as because it doesn't work.
That's certainly a concern. :/
Stephen
On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.I'm still trying to figure out the problem with having the table-level
lock, unless we really think people will be doing concurrent MERGE's
where they won't overlap..? I'm also a bit nervous about if the result
of concurrent MERGE's would actually be correct if we're not taking a
bigger lock than row-level (I assume we're taking row-level locks as it
goes through..).In general, I also thought/expected to have some kind of UPSERT type
capability with our initial MERGE support, even if it requires a big
lock and won't operate concurrently, etc.
You can of course LOCK TABLE as a work-around, if that's what you want.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Mon, Jan 3, 2011 at 10:58 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.I'm still trying to figure out the problem with having the table-level
lock, unless we really think people will be doing concurrent MERGE's
where they won't overlap..? I'm also a bit nervous about if the result
of concurrent MERGE's would actually be correct if we're not taking a
bigger lock than row-level (I assume we're taking row-level locks as it
goes through..).In general, I also thought/expected to have some kind of UPSERT type
capability with our initial MERGE support, even if it requires a big
lock and won't operate concurrently, etc.You can of course LOCK TABLE as a work-around, if that's what you want.
That work-around completely fails to solve the concurrency problem.
Just because you have a lock on the table doesn't mean that there
aren't already tuples in the table which are invisible to your
snapshot (for example because the inserting transactions haven't
committed yet).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 01/03/2011 10:58 AM, Heikki Linnakangas wrote:
In general, I also thought/expected to have some kind of UPSERT type
capability with our initial MERGE support, even if it requires a big
lock and won't operate concurrently, etc.You can of course LOCK TABLE as a work-around, if that's what you want.
I think we need to state this in large red letters in the docs, if
that's the requirement.
cheers
andrew
On 03.01.2011 18:02, Robert Haas wrote:
On Mon, Jan 3, 2011 at 10:58 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.I'm still trying to figure out the problem with having the table-level
lock, unless we really think people will be doing concurrent MERGE's
where they won't overlap..? I'm also a bit nervous about if the result
of concurrent MERGE's would actually be correct if we're not taking a
bigger lock than row-level (I assume we're taking row-level locks as it
goes through..).In general, I also thought/expected to have some kind of UPSERT type
capability with our initial MERGE support, even if it requires a big
lock and won't operate concurrently, etc.You can of course LOCK TABLE as a work-around, if that's what you want.
That work-around completely fails to solve the concurrency problem.
Just because you have a lock on the table doesn't mean that there
aren't already tuples in the table which are invisible to your
snapshot (for example because the inserting transactions haven't
committed yet).
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the table must commit
before the lock is granted. In serializable mode you get a serialization
error.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Mon, Jan 3, 2011 at 11:08 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
On 03.01.2011 18:02, Robert Haas wrote:
On Mon, Jan 3, 2011 at 10:58 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.I'm still trying to figure out the problem with having the table-level
lock, unless we really think people will be doing concurrent MERGE's
where they won't overlap..? I'm also a bit nervous about if the result
of concurrent MERGE's would actually be correct if we're not taking a
bigger lock than row-level (I assume we're taking row-level locks as it
goes through..).In general, I also thought/expected to have some kind of UPSERT type
capability with our initial MERGE support, even if it requires a big
lock and won't operate concurrently, etc.You can of course LOCK TABLE as a work-around, if that's what you want.
That work-around completely fails to solve the concurrency problem.
Just because you have a lock on the table doesn't mean that there
aren't already tuples in the table which are invisible to your
snapshot (for example because the inserting transactions haven't
committed yet).It works in read committed mode, because you acquire a new snapshot after
the LOCK TABLE, and anyone else who modified the table must commit before
the lock is granted.
Oh, I forgot we hold the ROW EXCLUSIVE lock until commit. That might
be OK, then.
In serializable mode you get a serialization error.
I don't think this part is true. You can certainly do this:
CREATE TABLE test (a int);
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM test;
<in another session, insert (1) into test>
LOCK TABLE test IN SHARE MODE; -- or just LOCK TABLE test, if you prefer
SELECT * FROM test; -- still ain't there
INSERT INTO test VALUES (1);
I don't see what would make MERGE immune to this.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the table must commit
before the lock is granted. In serializable mode you get a serialization
error.
If its not safe without this
LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE
then we should do that automatically, and document that.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On 03.01.2011 18:29, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the table must commit
before the lock is granted. In serializable mode you get a serialization
error.If its not safe without this
LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE
then we should do that automatically, and document that.
No we should not. The SQL standard doesn't require that, and it would
unnecessarily restrict concurrent updates on unrelated rows in the table.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Jan3, 2011, at 17:21 , Robert Haas wrote:
On Mon, Jan 3, 2011 at 11:08 AM, Heikki Linnakangas
In serializable mode you get a serialization error.
I don't think this part is true. You can certainly do this:
CREATE TABLE test (a int);
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM test;
<in another session, insert (1) into test>
LOCK TABLE test IN SHARE MODE; -- or just LOCK TABLE test, if you prefer
SELECT * FROM test; -- still ain't there
INSERT INTO test VALUES (1);
In SERIALIZABLE mode, you need to take any table-level locks before obtaining
a snapshot. There's even a warning about this in the docs somewhere IIRC...
best regards,
Florian Pflug
On Mon, 2011-01-03 at 18:35 +0200, Heikki Linnakangas wrote:
On 03.01.2011 18:29, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the table must commit
before the lock is granted. In serializable mode you get a serialization
error.If its not safe without this
LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE
then we should do that automatically, and document that.
No we should not. The SQL standard doesn't require that, and it would
unnecessarily restrict concurrent updates on unrelated rows in the table.
If we do that, then we definitely need a catch-all WHEN statement, so
that we can say
WHEN NOT MATCHED
INSERT
WHEN MATCHED
UPDATE
ELSE
{ INSERT into another table so we can try again in a minute
or RAISE error }
Otherwise we will silently drop rows. Throwing an error every time isn't
useful behaviour.
Of course, that then breaks the standard, just as all existing
implementations do.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On 03.01.2011 18:49, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:35 +0200, Heikki Linnakangas wrote:
On 03.01.2011 18:29, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the table must commit
before the lock is granted. In serializable mode you get a serialization
error.If its not safe without this
LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE
then we should do that automatically, and document that.
No we should not. The SQL standard doesn't require that, and it would
unnecessarily restrict concurrent updates on unrelated rows in the table.If we do that, then we definitely need a catch-all WHEN statement, so
that we can sayWHEN NOT MATCHED
INSERT
WHEN MATCHED
UPDATE
ELSE
{ INSERT into another table so we can try again in a minute
or RAISE error }Otherwise we will silently drop rows. Throwing an error every time isn't
useful behaviour.
An ELSE clause would be nice, but it's not related to the question at
hand. Only some serialization anomalities result in a row that matches
neither WHEN MATCHED nor WHEN NOT MATCHED. Others result in a duplicate
key exception, for example.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Mon, Jan 3, 2011 at 11:36 AM, Florian Pflug <fgp@phlo.org> wrote:
On Jan3, 2011, at 17:21 , Robert Haas wrote:
On Mon, Jan 3, 2011 at 11:08 AM, Heikki Linnakangas
In serializable mode you get a serialization error.
I don't think this part is true. You can certainly do this:
CREATE TABLE test (a int);
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM test;
<in another session, insert (1) into test>
LOCK TABLE test IN SHARE MODE; -- or just LOCK TABLE test, if you prefer
SELECT * FROM test; -- still ain't there
INSERT INTO test VALUES (1);In SERIALIZABLE mode, you need to take any table-level locks before obtaining
a snapshot. There's even a warning about this in the docs somewhere IIRC...
That should be safe, if people do it that way.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, 2011-01-03 at 19:01 +0200, Heikki Linnakangas wrote:
If we do that, then we definitely need a catch-all WHEN statement, so
that we can sayWHEN NOT MATCHED
INSERT
WHEN MATCHED
UPDATE
ELSE
{ INSERT into another table so we can try again in a minute
or RAISE error }Otherwise we will silently drop rows. Throwing an error every time isn't
useful behaviour.An ELSE clause would be nice, but it's not related to the question at
hand. Only some serialization anomalities result in a row that matches
neither WHEN MATCHED nor WHEN NOT MATCHED.
Concurrent UPDATEs, DELETEs, MERGE
Others result in a duplicate
key exception, for example.
Concurrent INSERTs, MERGE
So an ELSE clause is very relevant to handling anomalies in a useful
way.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On Mon, Jan 3, 2011 at 12:01 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
If we do that, then we definitely need a catch-all WHEN statement, so
that we can sayWHEN NOT MATCHED
INSERT
WHEN MATCHED
UPDATE
ELSE
{ INSERT into another table so we can try again in a minute
or RAISE error }Otherwise we will silently drop rows. Throwing an error every time isn't
useful behaviour.An ELSE clause would be nice, but it's not related to the question at hand.
Only some serialization anomalities result in a row that matches neither
WHEN MATCHED nor WHEN NOT MATCHED. Others result in a duplicate key
exception, for example.
I must be missing something. A source row is either matched or not
matched. ELSE doesn't exist because the writers of the spec thought
there might be some third matched-invisible-row case, but rather
because you might have written WHEN [NOT MATCHED] AND <some qual>, and
you might fall through a list of all such clauses.
I think we're focusing on the wrong problem here. MERGE creates some
syntax to let you do with SQL something that people are currently
doing with application-side logic. I've written the application-side
logic to do this kind of thing more times than I care to remember, and
yeah there are concurrency issues, but:
- sometimes there's only one writer, so it doesn't matter
- sometimes there can technically be more than one writer, but the
usage is so low that nothing actually breaks
- sometimes you know that a given writer will only operate on rows
customer_id = <some constant>; so you only need to prevent two
concurrent writers *for the same customer*, not any two concurrent
writers
- and sometimes you need a full table lock.
The third case, in particular, is quite common in my experience, and a
very good reason not to impose a full table lock. Users hate having
to do explicit locking (especially users whose names rhyme with Bevin
Bittner) but they hate *unnecessary* full-table locks even more. A
problem that you can fix by adding a LOCK TABLE statement is annoying;
a problem that you can fix only be removing an implicit lock table
operation that the system performs under the hood is a lot worse. In
the fourth case above, which IME is quite common, you could EITHER
take a full-table lock, if that performs OK, OR you could arrange to
take an advisory lock that protects the records for the particular
customer whose data you want to update. If we always take a
full-table lock, then the user loses the option to do something else.
The point we ought to be focusing on is that the patch doesn't work.
Unless someone is prepared to put in some time to fix THAT, the rest
of this discussion is academic.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> wrote:
Users hate having to do explicit locking (especially users whose
names rhyme with Bevin Bittner)
:-)
Before you decide to taunt me again, I guess I should point out a
few things here.
Should SSI and MERGE both make it into 9.1, there's every reason to
believe that running just about any DML, including MERGE, at
REPEATABLE READ would generate the same behavior which running at
REPEATABLE READ or SERIALIZABLE does now. If MERGE is susceptible
to such anomalies as testing for the presence of a row and then
trying to UPDATE it if found, only to update zero rows because it
was concurrently deleted, SERIALIZABLE would prevent that with a
serialization failure. I'd kind of expect that already with a
write-write conflict, but if that isn't happening, the SSI patch
should help. Well, help prevent anomalies -- if you want it to work
out how to continue without rolling back it won't help at all.
The fact that SSI introduces predicate locking may ultimately allow
MERGE to do something more clever in terms of UPSERT logic, but I
*REALLY* think it's too late in the release cycle to start looking
at that. Predicate locking for SSI was done exactly as was most
useful for SSI, on the basis (generally popular on this list) that
trying to generalize something with only one use case is doomed to
failure. Trying to bend it in an additional direction this late in
the release would pretty much ensure that neither MERGE nor SSI
could make it in.
On the other hand, if we put SSI in with predicate locking more or
less as it is, and put MERGE in with more primitive concurrency
control, I fully expect that someone could figure out how to tease
apart SSI and its predicate locking during the next release cycle,
so that the predicate locking was more generally useful.
-Kevin
On Mon, Jan 3, 2011 at 1:18 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
Robert Haas <robertmhaas@gmail.com> wrote:
Users hate having to do explicit locking (especially users whose
names rhyme with Bevin Bittner):-)
Before you decide to taunt me again, I guess I should point out a
few things here.
Sorry, that was intended as good-natured humor, not taunting. I think
that the work you are doing on the serializability stuff is *exactly*
the right fix for the concurrency issues associated with MERGE.
Coming up with a fix that is specific to MERGE doesn't impress me
much. I don't believe that hacking up MERGE will lead to anything
other than an ugly mess; it's just a syntax wrapper around an
operation that's fundamentally not too easy to make concurrent. SSI
will handle it, though, along with, well, all the other cases that are
worth worrying about. I don't have quite as much of an allergy to
explicit locking as you do, but I'm quite clear that it isn't nearly
as good as "it just works".
Should SSI and MERGE both make it into 9.1, [...]
So far the thread on large patches has lead to a status report from
most of the people working on large patches, and no volunteers to take
the lead on reviewing/committing any of them. Although I think both
of those patches are worthwhile, and although I intend to spend a
very, very large amount of time doing CF work in the next 43 days, I
don't foresee committing either of them, and I probably will not have
time for a detailed review of either one, either. I feel pretty bad
about that, but I just don't have any more bandwidth. :-(
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> wrote:
Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Before you decide to taunt me again, I guess I should point out a
few things here.Sorry, that was intended as good-natured humor, not taunting.
Oh, I took it that way. I guess my attempt at humor through an
oblique reference to a line from "Monty Python and the Holy Grail"
fell flat. :-/ I guess I should have said "before you taunt me a
second time" to make it more readily recognizable...
I think that the work you are doing on the serializability stuff
is *exactly* the right fix for the concurrency issues associated
with MERGE.
It's got a nice consistency with current behavior, with reads never
blocking or being blocked, but I can see why people would want a
MERGE which could dance around the concurrency problems and always
succeed with UPSERT behavior.
Various topics have come up which seem like they might benefit from
predicate locking. I don't know how many would need locks which
introduce blocking. I think it will actually be very easy to adapt
the predicate locking for such things as transactional cache
invalidation (which is what drew the interest of the MIT folks).
I'm not sure how much work it would be to adapt it to use for the
type of blocking locks which seem to be needed based on some of the
MERGE discussions I've read. It think it will be non-trivial but
possible.
-Kevin
On Mon, Jan 3, 2011 at 2:01 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
Robert Haas <robertmhaas@gmail.com> wrote:
Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Before you decide to taunt me again, I guess I should point out a
few things here.Sorry, that was intended as good-natured humor, not taunting.
Oh, I took it that way. I guess my attempt at humor through an
oblique reference to a line from "Monty Python and the Holy Grail"
fell flat. :-/ I guess I should have said "before you taunt me a
second time" to make it more readily recognizable...
Ah! I missed that. I have actually seen that movie, but it's been,
well... OK, I feel old now.
I think that the work you are doing on the serializability stuff
is *exactly* the right fix for the concurrency issues associated
with MERGE.It's got a nice consistency with current behavior, with reads never
blocking or being blocked, but I can see why people would want a
MERGE which could dance around the concurrency problems and always
succeed with UPSERT behavior.
I think the right thing to do about wanting UPSERT is to implement
UPSERT, though personally I prefer the name REPLACE from my long-ago
days as a MySQL user. It may be easier to solve a special case of the
concurrency problem than to solve it in its full generality (and
fixing MERGE is pretty close to "solving it in its full generality").
And even if it isn't, the MERGE syntax is insane if what you really
want to do is insert or update ONE record. If all we have is MERGE,
people will keep doing it with a PL/pgsql stored procedure or some
crummy application logic just so that they don't have to spend several
days trying to understand the syntax. Heck, I understand the syntax
(or I think I do) and I still think it's more trouble than its worth.
There is certainly a use case for an F-15 fighter jet but sometimes
what you really want is a model rocket and a small bottle of silver
paint.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
What I was trying to suggest upthread is that while there are other
possible ways around this problem, the only one that has any hope of
shipping with 9.1 is to do just that. So from my perspective, the rest
of the discussion about the right way to proceed is moot for now.
For some reason it didn't hit me until you said this that I could do the
locking manually in my test case, without even touching the server-side
code yet. Attached are a new pair of scripts where each pgbench UPDATE
statement executes an explicit LOCK TABLE. Here's the result of a
sample run here:
$ pgbench -f update-merge.sql -T 60 -c 16 -j 4 -s 2 pgbench
starting vacuum...end.
transaction type: Custom query
scaling factor: 2
query mode: simple
number of clients: 16
number of threads: 4
duration: 60 s
number of transactions actually processed: 84375
tps = 1405.953672 (including connections establishing)
tps = 1406.137456 (excluding connections establishing)
$ psql -c 'select count(*) as updated FROM pgbench_accounts WHERE NOT
abalance=0' -d pgbench
updated
---------
68897
(1 row)
$ psql -c 'select count(*) as inserted FROM pgbench_accounts WHERE aid >
100000' -d pgbench
inserted
----------
34497
(1 row)
No assertion crashes, no duplicate key failures. All the weird stuff I
was running into is gone, so decent evidence the worst of the problems
were all because the heavy lock I expecting just wasn't integrated into
the patch. Congratulations to Boxuan: for the first time this is
starting to act like a viable feature addition to me, just one with a
moderately long list of limitations and performance issues.
1400 TPS worth of UPSERT on my modest 8-core desktop (single drive with
cheating fsync) isn't uselessly slow. If I add "SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE;" just after the BEGIN;, I don't see any
serialization errors, and performance is exactly the same.
Run a straight UPDATE over only the existing range of keys, and I get
7000 TPS instead. So the locking etc. is reducing performance to 20% of
its normal rate, on this assertion+debug build. I can run this tomorrow
(err, later today I guess looking at the time) on a proper system with
BBWC and without asseritions to see if the magnitude of the difference
changes, but I don't think that's the main issue here.
Presuming the code quality issues and other little quirks I've
documented (and new ones yet to be discovered) can get resolved here,
and that's a sizeable open question, I could see shipping this with the
automatic heavy LOCK TABLE in there. Then simple UPSERT could work out
of the box via a straightforward MERGE. We'd need a big warning
disclaiming that concurrent performance is very limited in this first
release of the feature, but I don't know that this is at the
unacceptable level of slow for smaller web apps and such.
Until proper fine-grained concurrency is implemented, I think it would
be PR suicide to release a version of this without a full table lock
happening automatically though. The idea Robert advocated well, that it
would be possible for advanced users to use even this rough feature in a
smarter way to avoid conflicts and not suffer the full performance
penalty, is true. But if you consider the main purpose here to be
making it easier to get smaller MySQL apps and the like ported to
PostgreSQL (which is what I see as goal #1), putting that burden on the
user is just going to reinforce the old "PostgreSQL is so much harder
than MySQL" stereotype. I'd much prefer to see everyone have a slow but
simple to use UPSERT via MERGE available initially, rather than to worry
about optimizing for the advanced user in a way that makes life harder
for the newbies. The sort of people who must have optimal performance
already have trigger functions available to them, that they can write
and tweak for best performance.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Robert Haas wrote:
And even if it isn't, the MERGE syntax is insane if what you really
want to do is insert or update ONE record. If all we have is MERGE,
people will keep doing it with a PL/pgsql stored procedure or some
crummy application logic just so that they don't have to spend several
days trying to understand the syntax. Heck, I understand the syntax
(or I think I do) and I still think it's more trouble than its worth
I hoped that the manual would have a clear example of "this is how you
do UPSERT with MERGE", preferrably cross-linked to the existing "Example
39-2. Exceptions with UPDATE/INSERT" trigger implementation that's been
the reference implementation for this for a long time, so people can see
both alternatives. New users will cut and paste that example into their
code, and in the beginning neither know nor care how MERGE actually
works, so long as the example does what it claims. I would wager the
majority of PL/pgsql implementations of this requirement start the exact
same way. I don't think the learning curve there is really smaller,
it's just that you've just already been through it.
I've been purposefully ignoring the larger applications of MERGE in the
interest of keeping focus on a managable subset. But the more general
feature set is in fact enormously useful for some types of data
warehouse applications. Build REPLACE, and you built REPLACE. Build
MERGE that is REPLACE now and eventually full high-performance MERGE,
and you've done something with a much brighter future. I don't think
the concurrency hurdles here are unique to this feature either, as shown
by the regular overlap noted with the other serialization work.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Greg Smith wrote:
I could see shipping this with the automatic heavy LOCK TABLE in
there.
How would you handle or document behavior in REPEATABLE READ
isolation? The lock doesn't do much good unless you acquire it
before you get your snapshot, right?
-Kevin
Import Notes
Resolved by subject fallback
On Tue, Jan 04, 2011 at 04:44:32AM -0500, Greg Smith wrote:
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
Presuming the code quality issues and other little quirks I've
documented (and new ones yet to be discovered) can get resolved
here, and that's a sizeable open question, I could see shipping this
with the automatic heavy LOCK TABLE in there. Then simple UPSERT
could work out of the box via a straightforward MERGE.
How about implementing an UPSERT command as "take the lock, do the
merge?" That way, we'd have both the simplicity for the simpler cases
and a way to relax consistency guarantees for those who would like to
do so.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On 2011-01-04 6:27 PM, David Fetter wrote:
On Tue, Jan 04, 2011 at 04:44:32AM -0500, Greg Smith wrote:
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
Presuming the code quality issues and other little quirks I've
documented (and new ones yet to be discovered) can get resolved
here, and that's a sizeable open question, I could see shipping this
with the automatic heavy LOCK TABLE in there. Then simple UPSERT
could work out of the box via a straightforward MERGE.How about implementing an UPSERT command as "take the lock, do the
merge?" That way, we'd have both the simplicity for the simpler cases
and a way to relax consistency guarantees for those who would like to
do so.
That, unfortunately, won't work so well in REPEATABLE READ :-( But I,
too, am starting to think that we should have a separate, optimized
command to do UPSERT/INSERT .. IGNORE efficiently and correctly while
making MERGE's correctness the user's responsibility. Preferably with
huge warning signs on the documentation page.
Regards,
Marko Tiikkaja
On Tue, Jan 04, 2011 at 07:02:54PM +0200, Marko Tiikkaja wrote:
On 2011-01-04 6:27 PM, David Fetter wrote:
On Tue, Jan 04, 2011 at 04:44:32AM -0500, Greg Smith wrote:
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
Presuming the code quality issues and other little quirks I've
documented (and new ones yet to be discovered) can get resolved
here, and that's a sizeable open question, I could see shipping this
with the automatic heavy LOCK TABLE in there. Then simple UPSERT
could work out of the box via a straightforward MERGE.How about implementing an UPSERT command as "take the lock, do the
merge?" That way, we'd have both the simplicity for the simpler cases
and a way to relax consistency guarantees for those who would like to
do so.That, unfortunately, won't work so well in REPEATABLE READ :-(
There are caveats all over READ COMMITTED/REPEATABLE READ/SNAPSHOT.
The only really intuitively obvious behavior is SERIALIZABLE, which
we'll have available in 9.1. :)
But I, too, am starting to think that we should have a separate,
optimized command to do UPSERT/INSERT .. IGNORE efficiently and
correctly while making MERGE's correctness the user's
responsibility. Preferably with huge warning signs on the
documentation page.
+1 for the HUGE WARNING SIGNS :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Kevin Grittner wrote:
Greg Smith wrote:
I could see shipping this with the automatic heavy LOCK TABLE in
there.How would you handle or document behavior in REPEATABLE READ
isolation? The lock doesn't do much good unless you acquire it
before you get your snapshot, right?
Hand-wave and hope you offer a suggested implementation? I haven't
gotten to thinking about this part just yet--am still assimilating
toward a next move after the pleasant surprise that this is actually
working to some degree now. You're right that turning the high-level
idea of "just lock the table" actually has to be mapped into exact
snapshot mechanics and pitfalls before moving in that direction will get
very far. I'm probably not the right person to answer just exactly how
feasibile that is this week though.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
David Fetter wrote:
How about implementing an UPSERT command as "take the lock, do the
merge?" That way, we'd have both the simplicity for the simpler cases
and a way to relax consistency guarantees for those who would like to
do so.
Main argument against is that path leads to a permanent non-standard
wart to support forever, just to work around what should be a short-term
problem. And I'm not sure whether reducing the goals to only this
actually improves the ability to ship something in the near term too
much. Many of the hard problems people are bothered by don't go away,
it just makes deciding which side of the speed/complexity trade-off
you're more interested in becomes more obvious. What I've been
advocating is making that decision go away altogether by only worrying
about the simple to use and slow path for now, but that's a highly
debatable viewpoint I appreciate the resistence to, if it's possible to
do at all.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
On Tue, Jan 04, 2011 at 09:27:10PM -0500, Greg Smith wrote:
David Fetter wrote:
How about implementing an UPSERT command as "take the lock, do the
merge?" That way, we'd have both the simplicity for the simpler cases
and a way to relax consistency guarantees for those who would like to
do so.Main argument against is that path leads to a permanent non-standard
wart to support forever, just to work around what should be a
short-term problem. And I'm not sure whether reducing the goals to
only this actually improves the ability to ship something in the
near term too much.
I think I haven't communicated clearly what I'm suggesting, which is
that we ship with both an UPSERT and a MERGE, the former being ugly,
crude and simple, and the latter festooned with dire warnings about
isolation levels and locking.
If shipping with a "wart," as you term it, isn't acceptable, then I'd
advocate for going with just MERGE and documenting it inside and out,
including one or more clearly written UPSERT and/or REPLACE INTO
recipes.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter wrote:
I think I haven't communicated clearly what I'm suggesting, which is
that we ship with both an UPSERT and a MERGE, the former being ugly,
crude and simple, and the latter festooned with dire warnings about
isolation levels and locking.
I don't know that I completely agree with the idea that the UPSERT
version should always be crude and the MERGE one necessarily heavy from
a performance perspective. However, I do feel you raise a legitimate
point that once the hard stuff is done, and the locking issues around
SQL proper MERGE sorted, having an UPSERT/REPLACE synonym that maps to
it under the hood may be a useful way to provide a better user
experience. The way I see this, the right goal is to first provide the
full spec behavior with good performance, and get all that plumbing
right. There's nothing that says we can't provide an easier syntax than
the admittedly complicated MERGE one to the users though. (I am tempted
to make a joke about how you could probaby
So, as for this patch...there's about half a dozen significant open
issues with the current code, along with a smattering of smaller ones.
The problems remaining are deep enough that I think it would be
challenging to work through them for this CF under the best conditions.
And given that we haven't heard from Boxuan since early December, we're
definitely understaffed to tackle major revisions.
My hope was that we'd get an updated patch from him before the CF
deadline. Even without it, maybe get some more internal help here.
Given my focus on checkpoint issues, Simon on Sync Rep, and Dimitri
still on his Extensions push, that's second part isn't going to happen.
I am marking MERGE officially "Returned With Feedback" for 9.1. Lots of
progress made, much better community understanding of the unresolved
issues now than when we started, but not in shape to go into this
release. Since we still have some significant interest here in getting
this finished, I'll see if I can get put together a better game-plan for
how to get this actually done for 9.2 in time to discuss at release
planning time. The main thing that's become much more obvious to me
just recently is how the remaining issues left here relate to the true
serialization work, so worrying about that first is probably the right
order anyway.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us