Skip to content

Commit 5f2e179

Browse files
deanrasheeddeanrasheed
committed
Support MERGE into updatable views.
This allows the target relation of MERGE to be an auto-updatable or trigger-updatable view, and includes support for WITH CHECK OPTION, security barrier views, and security invoker views. A trigger-updatable view must have INSTEAD OF triggers for every type of action (INSERT, UPDATE, and DELETE) mentioned in the MERGE command. An auto-updatable view must not have any INSTEAD OF triggers. Mixing auto-update and trigger-update actions (i.e., having a partial set of INSTEAD OF triggers) is not supported. Rule-updatable views are also not supported, since there is no rewriter support for non-SELECT rules with MERGE operations. Dean Rasheed, reviewed by Jian He and Alvaro Herrera. Discussion: https://postgr.es/m/CAEZATCVcB1g0nmxuEc-A+gGB0HnfcGQNGYH7gS=7rq0u0zOBXA@mail.gmail.com
1 parent 8b29a11 commit 5f2e179

File tree

23 files changed

+1380
-288
lines changed

23 files changed

+1380
-288
lines changed

‎doc/src/sgml/ref/create_view.sgml

+27-15
Original file line numberDiff line numberDiff line change
@@ -192,12 +192,14 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
192192
<listitem>
193193
<para>
194194
This option controls the behavior of automatically updatable views. When
195-
this option is specified, <command>INSERT</command> and <command>UPDATE</command>
195+
this option is specified, <command>INSERT</command>,
196+
<command>UPDATE</command>, and <command>MERGE</command>
196197
commands on the view will be checked to ensure that new rows satisfy the
197198
view-defining condition (that is, the new rows are checked to ensure that
198199
they are visible through the view). If they are not, the update will be
199200
rejected. If the <literal>CHECK OPTION</literal> is not specified,
200-
<command>INSERT</command> and <command>UPDATE</command> commands on the view are
201+
<command>INSERT</command>, <command>UPDATE</command>, and
202+
<command>MERGE</command> commands on the view are
201203
allowed to create rows that are not visible through the view. The
202204
following check options are supported:
203205

@@ -247,7 +249,8 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
247249
<command>INSERT</command> or <command>UPDATE</command> command to be rewritten, then
248250
all check options will be ignored in the rewritten query, including any
249251
checks from automatically updatable views defined on top of the relation
250-
with the <literal>INSTEAD</literal> rule.
252+
with the <literal>INSTEAD</literal> rule. <command>MERGE</command> is not
253+
supported if the view or any of its base relations have rules.
251254
</para>
252255
</listitem>
253256
</varlistentry>
@@ -360,7 +363,8 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
360363

361364
<para>
362365
Simple views are automatically updatable: the system will allow
363-
<command>INSERT</command>, <command>UPDATE</command> and <command>DELETE</command> statements
366+
<command>INSERT</command>, <command>UPDATE</command>,
367+
<command>DELETE</command>, and <command>MERGE</command> statements
364368
to be used on the view in the same way as on a regular table. A view is
365369
automatically updatable if it satisfies all of the following conditions:
366370

@@ -400,13 +404,15 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
400404
An automatically updatable view may contain a mix of updatable and
401405
non-updatable columns. A column is updatable if it is a simple reference
402406
to an updatable column of the underlying base relation; otherwise the
403-
column is read-only, and an error will be raised if an <command>INSERT</command>
404-
or <command>UPDATE</command> statement attempts to assign a value to it.
407+
column is read-only, and an error will be raised if an
408+
<command>INSERT</command>, <command>UPDATE</command>, or
409+
<command>MERGE</command> statement attempts to assign a value to it.
405410
</para>
406411

407412
<para>
408413
If the view is automatically updatable the system will convert any
409-
<command>INSERT</command>, <command>UPDATE</command> or <command>DELETE</command> statement
414+
<command>INSERT</command>, <command>UPDATE</command>,
415+
<command>DELETE</command>, or <command>MERGE</command> statement
410416
on the view into the corresponding statement on the underlying base
411417
relation. <command>INSERT</command> statements that have an <literal>ON
412418
CONFLICT UPDATE</literal> clause are fully supported.
@@ -415,17 +421,21 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
415421
<para>
416422
If an automatically updatable view contains a <literal>WHERE</literal>
417423
condition, the condition restricts which rows of the base relation are
418-
available to be modified by <command>UPDATE</command> and <command>DELETE</command>
419-
statements on the view. However, an <command>UPDATE</command> is allowed to
424+
available to be modified by <command>UPDATE</command>,
425+
<command>DELETE</command>, and <command>MERGE</command>
426+
statements on the view. However, an <command>UPDATE</command> or
427+
<command>MERGE</command> is allowed to
420428
change a row so that it no longer satisfies the <literal>WHERE</literal>
421429
condition, and thus is no longer visible through the view. Similarly,
422-
an <command>INSERT</command> command can potentially insert base-relation rows
430+
an <command>INSERT</command> or <command>MERGE</command> command can
431+
potentially insert base-relation rows
423432
that do not satisfy the <literal>WHERE</literal> condition and thus are not
424433
visible through the view (<literal>ON CONFLICT UPDATE</literal> may
425434
similarly affect an existing row not visible through the view).
426435
The <literal>CHECK OPTION</literal> may be used to prevent
427-
<command>INSERT</command> and <command>UPDATE</command> commands from creating
428-
such rows that are not visible through the view.
436+
<command>INSERT</command>, <command>UPDATE</command>, and
437+
<command>MERGE</command> commands from creating such rows that are not
438+
visible through the view.
429439
</para>
430440

431441
<para>
@@ -443,14 +453,16 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
443453

444454
<para>
445455
A more complex view that does not satisfy all these conditions is
446-
read-only by default: the system will not allow an insert, update, or
447-
delete on the view. You can get the effect of an updatable view by
456+
read-only by default: the system will not allow an <command>INSERT</command>,
457+
<command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>
458+
on the view. You can get the effect of an updatable view by
448459
creating <literal>INSTEAD OF</literal> triggers on the view, which must
449460
convert attempted inserts, etc. on the view into appropriate actions
450461
on other tables. For more information see <xref
451462
linkend="sql-createtrigger"/>. Another possibility is to create rules
452463
(see <xref linkend="sql-createrule"/>), but in practice triggers are
453-
easier to understand and use correctly.
464+
easier to understand and use correctly. Also note that <command>MERGE</command>
465+
is not supported on relations with rules.
454466
</para>
455467

456468
<para>

‎doc/src/sgml/ref/merge.sgml

+18-4
Original file line numberDiff line numberDiff line change
@@ -132,16 +132,26 @@ DELETE
132132
<term><replaceable class="parameter">target_table_name</replaceable></term>
133133
<listitem>
134134
<para>
135-
The name (optionally schema-qualified) of the target table to merge into.
136-
If <literal>ONLY</literal> is specified before the table name, matching
137-
rows are updated or deleted in the named table only. If
135+
The name (optionally schema-qualified) of the target table or view to
136+
merge into. If <literal>ONLY</literal> is specified before a table
137+
name, matching rows are updated or deleted in the named table only. If
138138
<literal>ONLY</literal> is not specified, matching rows are also updated
139139
or deleted in any tables inheriting from the named table. Optionally,
140140
<literal>*</literal> can be specified after the table name to explicitly
141141
indicate that descendant tables are included. The
142142
<literal>ONLY</literal> keyword and <literal>*</literal> option do not
143143
affect insert actions, which always insert into the named table only.
144144
</para>
145+
146+
<para>
147+
If <replaceable class="parameter">target_table_name</replaceable> is a
148+
view, it must either be automatically updatable with no
149+
<literal>INSTEAD OF</literal> triggers, or it must have
150+
<literal>INSTEAD OF</literal> triggers for every type of action
151+
(<literal>INSERT</literal>, <literal>UPDATE</literal>, and
152+
<literal>DELETE</literal>) specified in the <literal>WHEN</literal>
153+
clauses. Views with rules are not supported.
154+
</para>
145155
</listitem>
146156
</varlistentry>
147157

@@ -486,7 +496,11 @@ MERGE <replaceable class="parameter">total_count</replaceable>
486496
the action's event type.
487497
</para>
488498
</listitem>
489-
</orderedlist></para>
499+
</orderedlist>
500+
If the target relation is a view with <literal>INSTEAD OF ROW</literal>
501+
triggers for the action's event type, they are used to perform the
502+
action instead.
503+
</para>
490504
</listitem>
491505
</orderedlist></para>
492506
</listitem>

‎doc/src/sgml/rules.sgml

+21-19
Original file line numberDiff line numberDiff line change
@@ -797,9 +797,9 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
797797

798798
<para>
799799
What happens if a view is named as the target relation for an
800-
<command>INSERT</command>, <command>UPDATE</command>, or
801-
<command>DELETE</command>? Doing the substitutions
802-
described above would give a query tree in which the result
800+
<command>INSERT</command>, <command>UPDATE</command>,
801+
<command>DELETE</command>, or <command>MERGE</command>? Doing the
802+
substitutions described above would give a query tree in which the result
803803
relation points at a subquery range-table entry, which will not
804804
work. There are several ways in which <productname>PostgreSQL</productname>
805805
can support the appearance of updating a view, however.
@@ -813,11 +813,12 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
813813
If the subquery selects from a single base relation and is simple
814814
enough, the rewriter can automatically replace the subquery with the
815815
underlying base relation so that the <command>INSERT</command>,
816-
<command>UPDATE</command>, or <command>DELETE</command> is applied to
817-
the base relation in the appropriate way. Views that are
818-
<quote>simple enough</quote> for this are called <firstterm>automatically
819-
updatable</firstterm>. For detailed information on the kinds of view that can
820-
be automatically updated, see <xref linkend="sql-createview"/>.
816+
<command>UPDATE</command>, <command>DELETE</command>, or
817+
<command>MERGE</command> is applied to the base relation in the
818+
appropriate way. Views that are <quote>simple enough</quote> for this
819+
are called <firstterm>automatically updatable</firstterm>. For detailed
820+
information on the kinds of view that can be automatically updated, see
821+
<xref linkend="sql-createview"/>.
821822
</para>
822823

823824
<para>
@@ -827,10 +828,10 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
827828
Rewriting works slightly differently
828829
in this case. For <command>INSERT</command>, the rewriter does
829830
nothing at all with the view, leaving it as the result relation
830-
for the query. For <command>UPDATE</command> and
831-
<command>DELETE</command>, it's still necessary to expand the
831+
for the query. For <command>UPDATE</command>, <command>DELETE</command>,
832+
and <command>MERGE</command>, it's still necessary to expand the
832833
view query to produce the <quote>old</quote> rows that the command will
833-
attempt to update or delete. So the view is expanded as normal,
834+
attempt to update, delete, or merge. So the view is expanded as normal,
834835
but another unexpanded range-table entry is added to the query
835836
to represent the view in its capacity as the result relation.
836837
</para>
@@ -842,13 +843,13 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
842843
list to identify the physical locations of the rows to be updated.
843844
This does not work if the result relation is a view, because a view
844845
does not have any <acronym>CTID</acronym>, since its rows do not have
845-
actual physical locations. Instead, for an <command>UPDATE</command>
846-
or <command>DELETE</command> operation, a special <literal>wholerow</literal>
847-
entry is added to the target list, which expands to include all
848-
columns from the view. The executor uses this value to supply the
849-
<quote>old</quote> row to the <literal>INSTEAD OF</literal> trigger. It is
850-
up to the trigger to work out what to update based on the old and
851-
new row values.
846+
actual physical locations. Instead, for an <command>UPDATE</command>,
847+
<command>DELETE</command>, or <command>MERGE</command> operation, a
848+
special <literal>wholerow</literal> entry is added to the target list,
849+
which expands to include all columns from the view. The executor uses this
850+
value to supply the <quote>old</quote> row to the
851+
<literal>INSTEAD OF</literal> trigger. It is up to the trigger to work
852+
out what to update based on the old and new row values.
852853
</para>
853854

854855
<para>
@@ -857,7 +858,8 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
857858
<command>UPDATE</command>, and <command>DELETE</command> commands on
858859
a view. These rules will rewrite the command, typically into a command
859860
that updates one or more tables, rather than views. That is the topic
860-
of <xref linkend="rules-update"/>.
861+
of <xref linkend="rules-update"/>. Note that this will not work with
862+
<command>MERGE</command>, which currently does not support rules.
861863
</para>
862864

863865
<para>

‎src/backend/commands/copyfrom.c

+1-1
Original file line numberDiff line numberDiff line change
@@ -767,7 +767,7 @@ CopyFrom(CopyFromState cstate)
767767
ExecInitResultRelation(estate, resultRelInfo, 1);
768768

769769
/* Verify the named relation is a valid target for INSERT */
770-
CheckValidResultRel(resultRelInfo, CMD_INSERT);
770+
CheckValidResultRel(resultRelInfo, CMD_INSERT, NIL);
771771

772772
ExecOpenIndices(resultRelInfo, false);
773773

‎src/backend/executor/execMain.c

+14-37
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,7 @@
5656
#include "miscadmin.h"
5757
#include "parser/parse_relation.h"
5858
#include "parser/parsetree.h"
59+
#include "rewrite/rewriteHandler.h"
5960
#include "storage/bufmgr.h"
6061
#include "storage/lmgr.h"
6162
#include "tcop/utility.h"
@@ -1017,14 +1018,18 @@ InitPlan(QueryDesc *queryDesc, int eflags)
10171018
* Generally the parser and/or planner should have noticed any such mistake
10181019
* already, but let's make sure.
10191020
*
1021+
* For MERGE, mergeActions is the list of actions that may be performed. The
1022+
* result relation is required to support every action, regardless of whether
1023+
* or not they are all executed.
1024+
*
10201025
* Note: when changing this function, you probably also need to look at
10211026
* CheckValidRowMarkRel.
10221027
*/
10231028
void
1024-
CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
1029+
CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation,
1030+
List *mergeActions)
10251031
{
10261032
Relation resultRel = resultRelInfo->ri_RelationDesc;
1027-
TriggerDesc *trigDesc = resultRel->trigdesc;
10281033
FdwRoutine *fdwroutine;
10291034

10301035
switch (resultRel->rd_rel->relkind)
@@ -1048,42 +1053,14 @@ CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
10481053
case RELKIND_VIEW:
10491054

10501055
/*
1051-
* Okay only if there's a suitable INSTEAD OF trigger. Messages
1052-
* here should match rewriteHandler.c's rewriteTargetView and
1053-
* RewriteQuery, except that we omit errdetail because we haven't
1054-
* got the information handy (and given that we really shouldn't
1055-
* get here anyway, it's not worth great exertion to get).
1056+
* Okay only if there's a suitable INSTEAD OF trigger. Otherwise,
1057+
* complain, but omit errdetail because we haven't got the
1058+
* information handy (and given that it really shouldn't happen,
1059+
* it's not worth great exertion to get).
10561060
*/
1057-
switch (operation)
1058-
{
1059-
case CMD_INSERT:
1060-
if (!trigDesc || !trigDesc->trig_insert_instead_row)
1061-
ereport(ERROR,
1062-
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1063-
errmsg("cannot insert into view \"%s\"",
1064-
RelationGetRelationName(resultRel)),
1065-
errhint("To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.")));
1066-
break;
1067-
case CMD_UPDATE:
1068-
if (!trigDesc || !trigDesc->trig_update_instead_row)
1069-
ereport(ERROR,
1070-
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1071-
errmsg("cannot update view \"%s\"",
1072-
RelationGetRelationName(resultRel)),
1073-
errhint("To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.")));
1074-
break;
1075-
case CMD_DELETE:
1076-
if (!trigDesc || !trigDesc->trig_delete_instead_row)
1077-
ereport(ERROR,
1078-
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1079-
errmsg("cannot delete from view \"%s\"",
1080-
RelationGetRelationName(resultRel)),
1081-
errhint("To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.")));
1082-
break;
1083-
default:
1084-
elog(ERROR, "unrecognized CmdType: %d", (int) operation);
1085-
break;
1086-
}
1061+
if (!view_has_instead_trigger(resultRel, operation, mergeActions))
1062+
error_view_not_updatable(resultRel, operation, mergeActions,
1063+
NULL);
10871064
break;
10881065
case RELKIND_MATVIEW:
10891066
if (!MatViewIncrementalMaintenanceIsEnabled())

‎src/backend/executor/execPartition.c

+2-2
Original file line numberDiff line numberDiff line change
@@ -361,7 +361,7 @@ ExecFindPartition(ModifyTableState *mtstate,
361361
if (rri)
362362
{
363363
/* Verify this ResultRelInfo allows INSERTs */
364-
CheckValidResultRel(rri, CMD_INSERT);
364+
CheckValidResultRel(rri, CMD_INSERT, NIL);
365365

366366
/*
367367
* Initialize information needed to insert this and
@@ -527,7 +527,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
527527
* partition-key becomes a DELETE+INSERT operation, so this check is still
528528
* required when the operation is CMD_UPDATE.
529529
*/
530-
CheckValidResultRel(leaf_part_rri, CMD_INSERT);
530+
CheckValidResultRel(leaf_part_rri, CMD_INSERT, NIL);
531531

532532
/*
533533
* Open partition indices. The user may have asked to check for conflicts

0 commit comments

Comments
 (0)