From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: support for MERGE
Date: Sat, 13 Nov 2021 12:33:49 -0600
Message-ID: <20211113183349.GL17618@telsasoft.com> (raw)
In-Reply-To: <20201231134736.GA25392@alvherre.pgsql>
References: <20201231134736.GA25392@alvherre.pgsql>
On Thu, Dec 31, 2020 at 10:47:36AM -0300, Alvaro Herrera wrote:
> Here's a rebase of Simon/Pavan's MERGE patch to current sources. I
> cleaned up some minor things in it, but aside from rebasing, it's pretty
> much their work (even the commit message is Simon's).
>
> Adding to commitfest.
I reviewed the documentation to learn about the feature,
and fixed some typos.
+notpatch.
--
Justin
Attachments:0001-f-typos.notpatch (text/x-diff, 14.1K) download
From 2f3d2465a93ee6207b2afdab8787cf2eaa2c0bb4 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 13 Nov 2021 12:11:46 -0600
Subject: [PATCH] f!typos
---
doc/src/sgml/mvcc.sgml | 8 ++---
doc/src/sgml/ref/create_policy.sgml | 5 +--
doc/src/sgml/ref/insert.sgml | 2 +-
doc/src/sgml/ref/merge.sgml | 50 ++++++++++++++---------------
doc/src/sgml/trigger.sgml | 7 ++--
src/test/regress/expected/merge.out | 4 +--
src/test/regress/sql/merge.sql | 4 +--
7 files changed, 41 insertions(+), 39 deletions(-)
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgmlindex a1ae842341..7dd0a3811f 100644--- a/doc/src/sgml/mvcc.sgml+++ b/doc/src/sgml/mvcc.sgml@@ -441,16 +441,16 @@ COMMIT;
can specify several actions and they can be conditional, the
conditions for each action are re-evaluated on the updated version of
the row, starting from the first action, even if the action that had
- originally matched was later in the list of actions.+ originally matched appears later in the list of actions.
On the other hand, if the row is concurrently updated or deleted so
that the join condition fails, then <command>MERGE</command> will
- evaluate the conditions <literal>NOT MATCHED</literal> actions next,+ evaluate the condition's <literal>NOT MATCHED</literal> actions next,
and execute the first one that succeeds.
If <command>MERGE</command> attempts an <command>INSERT</command>
and a unique index is present and a duplicate row is concurrently
- inserted then a uniqueness violation is raised.+ inserted, then a uniqueness violation is raised.
<command>MERGE</command> does not attempt to avoid the
- <literal>ERROR</literal> by attempting an <command>UPDATE</command>.+ <literal>ERROR</literal> by executign an <command>UPDATE</command>.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgmlindex 3db3908b42..db312681f7 100644--- a/doc/src/sgml/ref/create_policy.sgml+++ b/doc/src/sgml/ref/create_policy.sgml@@ -96,10 +96,11 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
</para>
<para>
- No separate policy exists for <command>MERGE</command>. Instead policies+ No separate policy exists for <command>MERGE</command>. Instead, the policies
defined for <command>SELECT</command>, <command>INSERT</command>,
<command>UPDATE</command> and <command>DELETE</command> are applied
- while executing <command>MERGE</command>, depending on the actions that are activated.+ while executing <command>MERGE</command>, depending on the actions that are+ performed.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgmlindex 477de2689b..ad61d757af 100644--- a/doc/src/sgml/ref/insert.sgml+++ b/doc/src/sgml/ref/insert.sgml@@ -592,7 +592,7 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
<para>
You may also wish to consider using <command>MERGE</command>, since that
- allows mixed <command>INSERT</command>, <command>UPDATE</command> and+ allows mixing <command>INSERT</command>, <command>UPDATE</command> and
<command>DELETE</command> within a single statement.
See <xref linkend="sql-merge"/>.
</para>
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgmlindex 7e1de11114..f7d29da9d9 100644--- a/doc/src/sgml/ref/merge.sgml+++ b/doc/src/sgml/ref/merge.sgml@@ -73,10 +73,10 @@ DELETE
from <replaceable class="parameter">data_source</replaceable> to
<replaceable class="parameter">target_table_name</replaceable>
producing zero or more candidate change rows. For each candidate change
- row the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal>+ row, the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal>
is set just once, after which <literal>WHEN</literal> clauses are evaluated
- in the order specified. The first clause to match each candidate change- row is executed. No more than one <literal>WHEN</literal> clause is+ in the order specified. For each candidate change row, the first clause to+ the row is executed. No more than one <literal>WHEN</literal> clause is
executed for any candidate change row.
</para>
@@ -85,14 +85,14 @@ DELETE
regular <command>UPDATE</command>, <command>INSERT</command>, or
<command>DELETE</command> commands of the same names. The syntax of
those commands is different, notably that there is no <literal>WHERE</literal>
- clause and no tablename is specified. All actions refer to the+ clause and no table name is specified. All actions refer to the
<replaceable class="parameter">target_table_name</replaceable>,
though modifications to other tables may be made using triggers.
</para>
<para>
- When <literal>DO NOTHING</literal> action is specified, the source row is- skipped. Since actions are evaluated in the given order, <literal>DO+ When <literal>DO NOTHING</literal> is specified, the source row is+ skipped. Since actions are evaluated in their specified order, <literal>DO
NOTHING</literal> can be handy to skip non-interesting source rows before
more fine-grained handling.
</para>
@@ -179,7 +179,7 @@ DELETE
<listitem>
<para>
A substitute name for the data source. When an alias is
- provided, it completely hides whether table or query was specified.+ provided, it completely hides whatever table or query was specified.
</para>
</listitem>
</varlistentry>
@@ -203,7 +203,7 @@ DELETE
rows should appear in <replaceable class="parameter">join_condition</replaceable>.
<replaceable class="parameter">join_condition</replaceable> subexpressions that
only reference <replaceable class="parameter">target_table_name</replaceable>
- columns can only affect which action is taken, often in surprising ways.+ columns can affect which action is taken, often in surprising ways.
</para>
</warning>
</listitem>
@@ -227,7 +227,7 @@ DELETE
Conversely, if the <literal>WHEN</literal> clause specifies
<literal>WHEN NOT MATCHED</literal>
and the candidate change row does not match a row in the
- <replaceable class="parameter">target_table_name</replaceable>+ <replaceable class="parameter">target_table_name</replaceable>,
the <literal>WHEN</literal> clause is executed if the
<replaceable class="parameter">condition</replaceable> is
absent or it evaluates to <literal>true</literal>.
@@ -240,12 +240,12 @@ DELETE
<listitem>
<para>
An expression that returns a value of type <type>boolean</type>.
- If this expression for a <literal>WHEN</literal> clause- returns <literal>true</literal> then the action for that clause- clause is executed for that row.+ If the expression for a <literal>WHEN</literal> clause+ returns <literal>true</literal>, then the action for that clause+ is executed for that row.
The expression may not contain functions that possibly perform
writes to the database.
- <!-- FIXME This is poorly defined; expresions containing VOLATILE functions+ <!-- FIXME This is poorly defined; expressions containing VOLATILE functions
are not rejected. Maybe we should throw an error if they are found. -->
</para>
<para>
@@ -282,8 +282,8 @@ DELETE
is a partitioned table, each row is routed to the appropriate partition
and inserted into it.
If <replaceable class="parameter">target_table_name</replaceable>
- is a partition, an error will occur if one of the input rows violates- the partition constraint.+ is a partition, an error will occur if any input row violates the+ partition constraint.
</para>
<para>
Column names may not be specified more than once.
@@ -306,7 +306,7 @@ DELETE
Column names may not be specified more than once.
</para>
<para>
- A table name and <literal>WHERE</literal> clause are not allowed.+ Neither a table name nor a <literal>WHERE</literal> clause are allowed.
</para>
</listitem>
</varlistentry>
@@ -317,8 +317,8 @@ DELETE
<para>
Specifies a <literal>DELETE</literal> action that deletes the current row
of the <replaceable class="parameter">target_table_name</replaceable>.
- Do not include the tablename or any other clauses, as you would normally- do with an <xref linkend="sql-delete"/> command.+ Do not include the table name or any other clauses, as you would normally+ do with a <xref linkend="sql-delete"/> command.
</para>
</listitem>
</varlistentry>
@@ -331,8 +331,8 @@ DELETE
class="parameter">target_table_name</replaceable>. The column name
can be qualified with a subfield name or array subscript, if
needed. (Inserting into only some fields of a composite
- column leaves the other fields null.) When referencing a- column, do not include the table's name in the specification+ column leaves the other fields null.)+ Do not include the table's name in the specification
of a target column.
</para>
</listitem>
@@ -439,14 +439,14 @@ MERGE <replaceable class="parameter">total-count</replaceable>
<para>
Perform any <literal>BEFORE STATEMENT</literal> triggers for all
actions specified, whether or not their <literal>WHEN</literal>
- clauses are executed.+ clauses match.
</para>
</listitem>
<listitem>
<para>
Perform a join from source to target table.
The resulting query will be optimized normally and will produce
- a set of candidate change row. For each candidate change row,+ a set of candidate change rows. For each candidate change row,
<orderedlist>
<listitem>
<para>
@@ -472,7 +472,7 @@ MERGE <replaceable class="parameter">total-count</replaceable>
</listitem>
<listitem>
<para>
- Apply the action specified, invoking any check constraints on the+ Perform the specified action, invoking any check constraints on the
target table.
<!-- This seems wrong. Maybe it's better to raise an error if
MERGE specifies an action for which rules exist. -->
@@ -518,8 +518,8 @@ MERGE <replaceable class="parameter">total-count</replaceable>
This can also occur if row triggers make changes to the target table
and the rows so modified are then subsequently also modified by
<command>MERGE</command>.
- If the repeated action is an <command>INSERT</command> this will- cause a uniqueness violation while a repeated <command>UPDATE</command>+ If the repeated action is an <command>INSERT</command>, this will+ cause a uniqueness violation, while a repeated <command>UPDATE</command>
or <command>DELETE</command> will cause a cardinality violation; the
latter behavior is required by the <acronym>SQL</acronym> standard.
This differs from historical <productname>PostgreSQL</productname>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgmlindex 91f199dfe0..877925026a 100644--- a/doc/src/sgml/trigger.sgml+++ b/doc/src/sgml/trigger.sgml@@ -196,15 +196,16 @@
No separate triggers are defined for <command>MERGE</command>. Instead,
statement-level or row-level <command>UPDATE</command>,
<command>DELETE</command> and <command>INSERT</command> triggers are fired
- depending on what actions are specified in the <command>MERGE</command> query- and what actions are activated.+ depending on (for statement-level triggers) what actions are specified in+ the <command>MERGE</command> query and (for row-level triggers) what+ actions are performed.
</para>
<para>
While running a <command>MERGE</command> command, statement-level
<literal>BEFORE</literal> and <literal>AFTER</literal> triggers are fired for
events specified in the actions of the <command>MERGE</command> command,
- irrespective of whether the action is finally activated or not. This is same as+ irrespective of whether or not the action is ultimately performed. This is same as
an <command>UPDATE</command> statement that updates no rows, yet
statement-level triggers are fired. The row-level triggers are fired only
when a row is actually updated, inserted or deleted. So it's perfectly legal
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.outindex 840091ef75..e17ae19a1c 100644--- a/src/test/regress/expected/merge.out+++ b/src/test/regress/expected/merge.out@@ -1469,7 +1469,7 @@ SELECT * FROM pa_target ORDER BY tid;
ROLLBACK;
DROP TABLE pa_source;
DROP TABLE pa_target CASCADE;
--- Sub-partitionin+-- Sub-partitioning
CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text)
PARTITION BY RANGE (logts);
CREATE TABLE part_m01 PARTITION OF pa_target
@@ -1529,7 +1529,7 @@ INSERT INTO cj_source1 VALUES (3, 10, 400);
INSERT INTO cj_source2 VALUES (1, 'initial source2');
INSERT INTO cj_source2 VALUES (2, 'initial source2');
INSERT INTO cj_source2 VALUES (3, 'initial source2');
--- source relation is an unalised join+-- source relation is an unaliased join
MERGE INTO cj_target t
USING cj_source1 s1
INNER JOIN cj_source2 s2 ON sid1 = sid2
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sqlindex ead408664d..666e8d939f 100644--- a/src/test/regress/sql/merge.sql+++ b/src/test/regress/sql/merge.sql@@ -981,7 +981,7 @@ ROLLBACK;
DROP TABLE pa_source;
DROP TABLE pa_target CASCADE;
--- Sub-partitionin+-- Sub-partitioning
CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text)
PARTITION BY RANGE (logts);
@@ -1035,7 +1035,7 @@ INSERT INTO cj_source2 VALUES (1, 'initial source2');
INSERT INTO cj_source2 VALUES (2, 'initial source2');
INSERT INTO cj_source2 VALUES (3, 'initial source2');
--- source relation is an unalised join+-- source relation is an unaliased join
MERGE INTO cj_target t
USING cj_source1 s1
INNER JOIN cj_source2 s2 ON sid1 = sid2
--
2.17.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, alvherre@alvh.no-ip.org, pgsql-hackers@lists.postgresql.org
Subject: Re: support for MERGE
In-Reply-To: <20211113183349.GL17618@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox