On 17.03.22 12:31, Alvaro Herrera wrote:
0001 pushed. Here's 0002 again for cfbot, with no changes other than
pgindent cleanup.
I did a cursory read through and want to offer some trivial amendments
in the attached patches. The 0001 adds back various serial commas, the
0002 is assorted other stuff.
One functional change I recommend is the tab completion of the MERGE
target. I think the filtering in Query_for_list_of_mergetargets is a
bit too particular. For example, if a table is a possible MERGE target,
and then someone adds a rule, it will disappear from the completions,
without explanation, which could be confusing. I think we can be
generous in what we accept and then let the actual parse analysis
provide suitable error messages. Also, consider forward-compatibility
if support for further targets is added. I would consider dropping
Query_for_list_of_mergetargets and just using Query_for_list_of_updatables.
In any case, the min_server_version could be dropped. That is usually
only used if the query would fail in an older version, but not if the
command being completed wouldn't work. For example, we don't restrict
in what versions you can complete partitioned indexes.From 18ad74bfc2b6d1a758eaab9d2332532f6abb074d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 18 Mar 2022 11:44:16 +0100
Subject: [PATCH 1/2] fixup! MERGE SQL Command following SQL:2016
Add back serial commas.
---
doc/src/sgml/libpq.sgml | 2 +-
doc/src/sgml/mvcc.sgml | 2 +-
doc/src/sgml/ref/create_policy.sgml | 2 +-
doc/src/sgml/ref/insert.sgml | 2 +-
doc/src/sgml/ref/merge.sgml | 6 +++---
doc/src/sgml/trigger.sgml | 2 +-
src/include/nodes/execnodes.h | 2 +-
src/include/nodes/pathnodes.h | 2 +-
src/include/nodes/plannodes.h | 2 +-
9 files changed, 11 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 5fcfb29a55..70233aa872 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -4128,7 +4128,7 @@ <title>Retrieving Other Result Information</title>
<command>MERGE</command>, <command>MOVE</command>,
<command>FETCH</command>,
or <command>COPY</command> statement, or an <command>EXECUTE</command>
of a
prepared query that contains an <command>INSERT</command>,
- <command>UPDATE</command>, <command>DELETE</command>
+ <command>UPDATE</command>, <command>DELETE</command>,
or <command>MERGE</command> statement.
If the command that generated the <structname>PGresult</structname> was
anything
else, <xref linkend="libpq-PQcmdTuples"/> returns an empty string. The
caller
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 62451d297b..9b3061c03d 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -955,7 +955,7 @@ <title>Table-Level Lock Modes</title>
<para>
The commands <command>UPDATE</command>,
- <command>DELETE</command>, <command>INSERT</command> and
+ <command>DELETE</command>, <command>INSERT</command>, and
<command>MERGE</command>
acquire this lock mode on the target table (in addition to
<literal>ACCESS SHARE</literal> locks on any other referenced
diff --git a/doc/src/sgml/ref/create_policy.sgml
b/doc/src/sgml/ref/create_policy.sgml
index f45882814d..c8ecc06347 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -619,7 +619,7 @@ <title>Notes</title>
<para>
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
+ <command>UPDATE</command>, and <command>DELETE</command> are applied
while executing <command>MERGE</command>, depending on the actions that are
performed.
</para>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index ad61d757af..41cafc8455 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -592,7 +592,7 @@ <title>Notes</title>
<para>
You may also wish to consider using <command>MERGE</command>, since that
- allows mixing <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.sgml
index 73778666e9..3a1823ceed 100644
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -118,8 +118,8 @@ <title>Description</title>
<para>
<command>MERGE</command> is not supported if the
<replaceable class="parameter">target_table_name</replaceable> is a
- materialized view, foreign table or if it has any
- <literal>RULEs</literal> defined on it.
+ materialized view, foreign table, or if it has any
+ rules defined on it.
</para>
</refsect1>
@@ -153,7 +153,7 @@ <title>Parameters</title>
<term><replaceable class="parameter">source_table_name</replaceable></term>
<listitem>
<para>
- The name (optionally schema-qualified) of the source table, view or
+ The name (optionally schema-qualified) of the source table, view, or
transition table.
</para>
</listitem>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 2662be243f..04e702a795 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -195,7 +195,7 @@ <title>Overview of Trigger Behavior</title>
<para>
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
+ <command>DELETE</command>, and <command>INSERT</command> triggers are fired
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.
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 84e3ebf7a6..11e295890c 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1222,7 +1222,7 @@ typedef struct ProjectSetState
typedef struct ModifyTableState
{
PlanState ps; /* its first field is
NodeTag */
- CmdType operation; /* INSERT, UPDATE, DELETE or
MERGE */
+ CmdType operation; /* INSERT, UPDATE, DELETE, or
MERGE */
bool canSetTag; /* do we set the command
tag/es_processed? */
bool mt_done; /* are we done? */
int mt_nrels; /* number of entries in
resultRelInfo[] */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 1092c87434..365000bdcd 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1885,7 +1885,7 @@ typedef struct ModifyTablePath
{
Path path;
Path *subpath; /* Path producing source data */
- CmdType operation; /* INSERT, UPDATE, DELETE or
MERGE */
+ CmdType operation; /* INSERT, UPDATE, DELETE, or
MERGE */
bool canSetTag; /* do we set the command
tag/es_processed? */
Index nominalRelation; /* Parent RT index for use of
EXPLAIN */
Index rootRelation; /* Root RT index, if target is
partitioned */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index aa712cea71..50ef3dda05 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -218,7 +218,7 @@ typedef struct ProjectSet
typedef struct ModifyTable
{
Plan plan;
- CmdType operation; /* INSERT, UPDATE, DELETE or
MERGE */
+ CmdType operation; /* INSERT, UPDATE, DELETE, or
MERGE */
bool canSetTag; /* do we set the command
tag/es_processed? */
Index nominalRelation; /* Parent RT index for use of
EXPLAIN */
Index rootRelation; /* Root RT index, if target is
partitioned */
--
2.35.1
From 2cbb5a9c55e9316a7f26fdbcd0d056feff15c86f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 18 Mar 2022 12:04:20 +0100
Subject: [PATCH 2/2] fixup! MERGE SQL Command following SQL:2016
---
doc/src/sgml/mvcc.sgml | 2 +-
doc/src/sgml/ref/insert.sgml | 2 +-
doc/src/sgml/ref/merge.sgml | 36 ++++++++++++--------------
src/backend/catalog/sql_features.txt | 2 +-
src/backend/executor/README | 2 +-
src/backend/executor/nodeModifyTable.c | 6 ++---
src/backend/parser/gram.y | 24 ++++++++---------
src/backend/parser/parse_merge.c | 2 +-
src/bin/psql/tab-complete.c | 19 +++++---------
9 files changed, 42 insertions(+), 53 deletions(-)
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 9b3061c03d..db86326aef 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -450,7 +450,7 @@ <title>Read Committed Isolation Level</title>
and a unique index is present and a duplicate row is concurrently
inserted, then a uniqueness violation is raised.
<command>MERGE</command> does not attempt to avoid the
- <literal>ERROR</literal> by executing an <command>UPDATE</command>.
+ error by executing an <command>UPDATE</command>.
</para>
<para>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 41cafc8455..a9af9959c0 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -766,7 +766,7 @@ <title>Compatibility</title>
Also, the case in
which a column name list is omitted, but not all the columns are
filled from the <literal>VALUES</literal> clause or
<replaceable>query</replaceable>,
- is disallowed by the standard. If you prefer a more SQL Standard
+ is disallowed by the standard. If you prefer a more SQL standard
conforming statement than <literal>ON CONFLICT</literal>, see
<xref linkend="sql-merge"/>.
</para>
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
index 3a1823ceed..8d1d160e02 100644
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -13,7 +13,7 @@
<refnamediv>
<refname>MERGE</refname>
- <refpurpose>insert, update, or delete rows of a table based upon source
data</refpurpose>
+ <refpurpose>conditionally insert, update, or delete rows of a
table</refpurpose>
</refnamediv>
<refsynopsisdiv>
@@ -24,32 +24,32 @@
ON <replaceable class="parameter">join_condition</replaceable>
<replaceable class="parameter">when_clause</replaceable> [...]
-where <replaceable class="parameter">data_source</replaceable> is
+<phrase>where <replaceable class="parameter">data_source</replaceable>
is</phrase>
{ <replaceable class="parameter">source_table_name</replaceable> |
- ( source_query )
+ ( <replaceable class="parameter">source_query</replaceable> )
}
[ [ AS ] <replaceable class="parameter">source_alias</replaceable> ]
-and <replaceable class="parameter">when_clause</replaceable> is
+<phrase>and <replaceable class="parameter">when_clause</replaceable>
is</phrase>
{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ]
THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable
class="parameter">merge_delete</replaceable> | DO NOTHING } |
WHEN NOT MATCHED [ AND <replaceable
class="parameter">condition</replaceable> ] THEN { <replaceable
class="parameter">merge_insert</replaceable> | DO NOTHING }
}
-and <replaceable class="parameter">merge_insert</replaceable> is
+<phrase>and <replaceable class="parameter">merge_insert</replaceable>
is</phrase>
INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT
} [, ...] ) | DEFAULT VALUES }
-and <replaceable class="parameter">merge_update</replaceable> is
+<phrase>and <replaceable class="parameter">merge_update</replaceable>
is</phrase>
UPDATE SET { <replaceable class="parameter">column_name</replaceable> = {
<replaceable class="parameter">expression</replaceable> | DEFAULT } |
( <replaceable class="parameter">column_name</replaceable> [,
...] ) = ( { <replaceable class="parameter">expression</replaceable> | DEFAULT
} [, ...] )
} [, ...]
-and <replaceable class="parameter">merge_delete</replaceable> is
+<phrase>and <replaceable class="parameter">merge_delete</replaceable>
is</phrase>
DELETE
</synopsis>
@@ -414,18 +414,18 @@ <title>Outputs</title>
On successful completion, a <command>MERGE</command> command returns a
command
tag of the form
<screen>
-MERGE <replaceable class="parameter">total-count</replaceable>
+MERGE <replaceable class="parameter">total_count</replaceable>
</screen>
- The <replaceable class="parameter">total-count</replaceable> is the total
+ The <replaceable class="parameter">total_count</replaceable> is the total
number of rows changed (whether inserted, updated, or deleted).
- If <replaceable class="parameter">total-count</replaceable> is 0, no rows
+ If <replaceable class="parameter">total_count</replaceable> is 0, no rows
were changed in any way.
</para>
</refsect1>
<refsect1>
- <title>Execution</title>
+ <title>Notes</title>
<para>
The following steps take place during the execution of
@@ -458,7 +458,7 @@ <title>Execution</title>
</listitem>
<listitem>
<para>
- When a condition returns true, perform the following actions
+ When a condition returns true, perform the following actions:
<orderedlist>
<listitem>
<para>
@@ -531,10 +531,6 @@ <title>Execution</title>
possible that no action will be taken for a candidate change row.
</para>
- </refsect1>
- <refsect1>
- <title>Notes</title>
-
<para>
The order in which rows are generated from the data source is
indeterminate by default.
@@ -550,7 +546,6 @@ <title>Notes</title>
<literal>RETURNING</literal> or <literal>WITH</literal> clauses.
</para>
- <tip>
<para>
You may also wish to consider using <command>INSERT ... ON
CONFLICT</command>
as an alternative statement which offers the ability to run an
@@ -558,7 +553,6 @@ <title>Notes</title>
occurs. There are a variety of differences and restrictions between
the two statement types and they are not interchangeable.
</para>
- </tip>
</refsect1>
<refsect1>
@@ -578,10 +572,12 @@ <title>Examples</title>
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionValue);
</programlisting>
+ </para>
- notice that this would be exactly equivalent to the following
+ <para>
+ Notice that this would be exactly equivalent to the following
statement because the <literal>MATCHED</literal> result does not change
- during execution
+ during execution.
<programlisting>
MERGE INTO CustomerAccount CA
diff --git a/src/backend/catalog/sql_features.txt
b/src/backend/catalog/sql_features.txt
index d4cb8128ff..4c3e29111d 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -240,7 +240,7 @@ F311 Schema definition statement 02 CREATE
TABLE for persistent base tables YES
F311 Schema definition statement 03 CREATE VIEW YES
F311 Schema definition statement 04 CREATE VIEW: WITH CHECK OPTION
YES
F311 Schema definition statement 05 GRANT statement YES
-F312 MERGE statement YES also consider INSERT ... ON
CONFLICT DO UPDATE
+F312 MERGE statement YES
F313 Enhanced MERGE statement YES
F314 MERGE statement with DELETE branch YES
F321 User authorization YES
diff --git a/src/backend/executor/README b/src/backend/executor/README
index ea7469508e..0b5183fc4a 100644
--- a/src/backend/executor/README
+++ b/src/backend/executor/README
@@ -226,7 +226,7 @@ are simple Vars using only one step instead of two.
MERGE
-----
-MERGE is a multiple-table, multiple-action command: it specifies a target
+MERGE is a multiple-table, multiple-action command: It specifies a target
table and a source relation, and can contain multiple WHEN MATCHED and
WHEN NOT MATCHED clauses, each of which specifies one UPDATE, INSERT,
UPDATE, or DO NOTHING actions. The target table is modified by MERGE,
diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index 6fc318583a..02b6513423 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -2290,7 +2290,7 @@ ExecOnConflictUpdate(ModifyTableContext *context,
* to break.
*
* It is the user's responsibility to prevent this
situation from
- * occurring. These problems are why SQL Standard
similarly
+ * occurring. These problems are why the SQL standard
similarly
* specifies that for SQL MERGE, an exception must be
raised in
* the event of an attempt to update the same row twice.
*/
@@ -2303,7 +2303,7 @@ ExecOnConflictUpdate(ModifyTableContext *context,
if (TransactionIdIsCurrentTransactionId(xmin))
ereport(ERROR,
(errcode(ERRCODE_CARDINALITY_VIOLATION),
- /*- translator: %s is a SQL command name */
+ /* translator: %s is a SQL command name */
errmsg("%s command cannot
affect row a second time",
"ON CONFLICT DO
UPDATE"),
errhint("Ensure that no rows
proposed for insertion within the same command have duplicate constrained
values.")));
@@ -2723,7 +2723,7 @@ lmerge_matched:;
if
(TransactionIdIsCurrentTransactionId(context->tmfd.xmax))
ereport(ERROR,
(errcode(ERRCODE_CARDINALITY_VIOLATION),
- /*- translator: %s is a SQL command
name */
+ /* translator: %s is a SQL command name
*/
errmsg("%s command
cannot affect row a second time",
"MERGE"),
errhint("Ensure that
not more than one source row matches any one target row.")));
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 34371d3869..6ad50fa11d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11475,7 +11475,7 @@ set_target_list:
/*****************************************************************************
*
* QUERY:
- * MERGE STATEMENTS
+ * MERGE
*
*****************************************************************************/
@@ -11495,13 +11495,13 @@ MergeStmt:
$$ = (Node *)m;
}
- ;
+ ;
merge_when_list:
merge_when_clause
{ $$ = list_make1($1); }
| merge_when_list merge_when_clause { $$ =
lappend($1,$2); }
- ;
+ ;
merge_when_clause:
WHEN MATCHED opt_merge_when_and_condition THEN
merge_update
@@ -11550,16 +11550,16 @@ merge_when_clause:
$$ = (Node *)m;
}
- ;
+ ;
opt_merge_when_and_condition:
- AND a_expr { $$ = $2; }
- | { $$ =
NULL; }
- ;
+ AND a_expr { $$ = $2; }
+ | { $$ =
NULL; }
+ ;
merge_delete:
- DELETE_P { $$ = NULL; }
- ;
+ DELETE_P { $$ = NULL; }
+ ;
merge_update:
UPDATE SET set_clause_list
@@ -11569,7 +11569,7 @@ merge_update:
$$ = n;
}
- ;
+ ;
merge_insert:
INSERT merge_values_clause
@@ -11609,14 +11609,14 @@ merge_insert:
n->values = NIL;
$$ = n;
}
- ;
+ ;
merge_values_clause:
VALUES '(' expr_list ')'
{
$$ = $3;
}
- ;
+ ;
/*****************************************************************************
*
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
index 1f0f9058b3..0f54e9e8e1 100644
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -383,7 +383,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
qry->mergeActionList = mergeActionList;
- /* RETURNING could potentially be added in the future, but not in SQL
Std */
+ /* RETURNING could potentially be added in the future, but not in SQL
std */
qry->returningList = NULL;
qry->hasTargetSRFs = false;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 85ed254d85..09054b22e8 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -804,24 +804,17 @@ static const SchemaQuery Query_for_list_of_updatables = {
/* Relations supporting MERGE */
static const SchemaQuery Query_for_list_of_mergetargets = {
- /* min_server_version */
- 150000,
- /* catname */
- "pg_catalog.pg_class c",
- /* selcondition */
+ .min_server_version = 150000,
+ .catname = "pg_catalog.pg_class c",
+ .selcondition =
"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_PARTITIONED_TABLE) ") AND "
"c.relhasrules = false AND "
"(c.relhassubclass = false OR "
" c.relkind = " CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
- /* viscondition */
- "pg_catalog.pg_table_is_visible(c.oid)",
- /* namespace */
- "c.relnamespace",
- /* result */
- "pg_catalog.quote_ident(c.relname)",
- /* qualresult */
- NULL
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "pg_catalog.quote_ident(c.relname)",
};
/* Relations supporting SELECT */
--
2.35.1
