From 5f2e179bd31e5f5803005101eb12a8d7bf8db8f3 Mon Sep 17 00:00:00 2001 From: Dean Rasheed Date: Thu, 29 Feb 2024 15:56:59 +0000 Subject: 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 --- doc/src/sgml/ref/create_view.sgml | 42 +++++++++++++++++++++++++-------------- doc/src/sgml/ref/merge.sgml | 22 ++++++++++++++++---- doc/src/sgml/rules.sgml | 40 +++++++++++++++++++------------------ 3 files changed, 66 insertions(+), 38 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 3b26205f788..e8d9d3c8d0f 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -192,12 +192,14 @@ CREATE VIEW [ schema . ] view_name This option controls the behavior of automatically updatable views. When - this option is specified, INSERT and UPDATE + this option is specified, INSERT, + UPDATE, and MERGE commands on the view will be checked to ensure that new rows satisfy the view-defining condition (that is, the new rows are checked to ensure that they are visible through the view). If they are not, the update will be rejected. If the CHECK OPTION is not specified, - INSERT and UPDATE commands on the view are + INSERT, UPDATE, and + MERGE commands on the view are allowed to create rows that are not visible through the view. The following check options are supported: @@ -247,7 +249,8 @@ CREATE VIEW [ schema . ] view_nameINSERT or UPDATE command to be rewritten, then all check options will be ignored in the rewritten query, including any checks from automatically updatable views defined on top of the relation - with the INSTEAD rule. + with the INSTEAD rule. MERGE is not + supported if the view or any of its base relations have rules. @@ -360,7 +363,8 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; Simple views are automatically updatable: the system will allow - INSERT, UPDATE and DELETE statements + INSERT, UPDATE, + DELETE, and MERGE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions: @@ -400,13 +404,15 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; An automatically updatable view may contain a mix of updatable and non-updatable columns. A column is updatable if it is a simple reference to an updatable column of the underlying base relation; otherwise the - column is read-only, and an error will be raised if an INSERT - or UPDATE statement attempts to assign a value to it. + column is read-only, and an error will be raised if an + INSERT, UPDATE, or + MERGE statement attempts to assign a value to it. If the view is automatically updatable the system will convert any - INSERT, UPDATE or DELETE statement + INSERT, UPDATE, + DELETE, or MERGE statement on the view into the corresponding statement on the underlying base relation. INSERT statements that have an ON CONFLICT UPDATE clause are fully supported. @@ -415,17 +421,21 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; If an automatically updatable view contains a WHERE condition, the condition restricts which rows of the base relation are - available to be modified by UPDATE and DELETE - statements on the view. However, an UPDATE is allowed to + available to be modified by UPDATE, + DELETE, and MERGE + statements on the view. However, an UPDATE or + MERGE is allowed to change a row so that it no longer satisfies the WHERE condition, and thus is no longer visible through the view. Similarly, - an INSERT command can potentially insert base-relation rows + an INSERT or MERGE command can + potentially insert base-relation rows that do not satisfy the WHERE condition and thus are not visible through the view (ON CONFLICT UPDATE may similarly affect an existing row not visible through the view). The CHECK OPTION may be used to prevent - INSERT and UPDATE commands from creating - such rows that are not visible through the view. + INSERT, UPDATE, and + MERGE commands from creating such rows that are not + visible through the view. @@ -443,14 +453,16 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; A more complex view that does not satisfy all these conditions is - read-only by default: the system will not allow an insert, update, or - delete on the view. You can get the effect of an updatable view by + read-only by default: the system will not allow an INSERT, + UPDATE, DELETE, or MERGE + on the view. You can get the effect of an updatable view by creating INSTEAD OF triggers on the view, which must convert attempted inserts, etc. on the view into appropriate actions on other tables. For more information see . Another possibility is to create rules (see ), but in practice triggers are - easier to understand and use correctly. + easier to understand and use correctly. Also note that MERGE + is not supported on relations with rules. diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml index bb34ef9b921..7a8ac40259c 100644 --- a/doc/src/sgml/ref/merge.sgml +++ b/doc/src/sgml/ref/merge.sgml @@ -132,9 +132,9 @@ DELETE target_table_name - The name (optionally schema-qualified) of the target table to merge into. - If ONLY is specified before the table name, matching - rows are updated or deleted in the named table only. If + The name (optionally schema-qualified) of the target table or view to + merge into. If ONLY is specified before a table + name, matching rows are updated or deleted in the named table only. If ONLY is not specified, matching rows are also updated or deleted in any tables inheriting from the named table. Optionally, * can be specified after the table name to explicitly @@ -142,6 +142,16 @@ DELETE ONLY keyword and * option do not affect insert actions, which always insert into the named table only. + + + If target_table_name is a + view, it must either be automatically updatable with no + INSTEAD OF triggers, or it must have + INSTEAD OF triggers for every type of action + (INSERT, UPDATE, and + DELETE) specified in the WHEN + clauses. Views with rules are not supported. + @@ -486,7 +496,11 @@ MERGE total_count the action's event type. - + + If the target relation is a view with INSTEAD OF ROW + triggers for the action's event type, they are used to perform the + action instead. + diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index d229b94d396..784c16e76ea 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -797,9 +797,9 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; What happens if a view is named as the target relation for an - INSERT, UPDATE, or - DELETE? Doing the substitutions - described above would give a query tree in which the result + INSERT, UPDATE, + DELETE, or MERGE? Doing the + substitutions described above would give a query tree in which the result relation points at a subquery range-table entry, which will not work. There are several ways in which PostgreSQL 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; If the subquery selects from a single base relation and is simple enough, the rewriter can automatically replace the subquery with the underlying base relation so that the INSERT, - UPDATE, or DELETE is applied to - the base relation in the appropriate way. Views that are - simple enough for this are called automatically - updatable. For detailed information on the kinds of view that can - be automatically updated, see . + UPDATE, DELETE, or + MERGE is applied to the base relation in the + appropriate way. Views that are simple enough for this + are called automatically updatable. For detailed + information on the kinds of view that can be automatically updated, see + . @@ -827,10 +828,10 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; Rewriting works slightly differently in this case. For INSERT, the rewriter does nothing at all with the view, leaving it as the result relation - for the query. For UPDATE and - DELETE, it's still necessary to expand the + for the query. For UPDATE, DELETE, + and MERGE, it's still necessary to expand the view query to produce the old rows that the command will - attempt to update or delete. So the view is expanded as normal, + attempt to update, delete, or merge. So the view is expanded as normal, but another unexpanded range-table entry is added to the query to represent the view in its capacity as the result relation. @@ -842,13 +843,13 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; list to identify the physical locations of the rows to be updated. This does not work if the result relation is a view, because a view does not have any CTID, since its rows do not have - actual physical locations. Instead, for an UPDATE - or DELETE operation, a special wholerow - entry is added to the target list, which expands to include all - columns from the view. The executor uses this value to supply the - old row to the INSTEAD OF trigger. It is - up to the trigger to work out what to update based on the old and - new row values. + actual physical locations. Instead, for an UPDATE, + DELETE, or MERGE operation, a + special wholerow entry is added to the target list, + which expands to include all columns from the view. The executor uses this + value to supply the old row to the + INSTEAD OF trigger. It is up to the trigger to work + out what to update based on the old and new row values. @@ -857,7 +858,8 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; UPDATE, and DELETE commands on a view. These rules will rewrite the command, typically into a command that updates one or more tables, rather than views. That is the topic - of . + of . Note that this will not work with + MERGE, which currently does not support rules. -- cgit v1.2.3