From a99c42f291421572aef2b0a9360294c7d89b8bc7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 8 Dec 2012 18:25:48 -0500 Subject: Support automatically-updatable views. This patch makes "simple" views automatically updatable, without the need to create either INSTEAD OF triggers or INSTEAD rules. "Simple" views are those classified as updatable according to SQL-92 rules. The rewriter transforms INSERT/UPDATE/DELETE commands on such views directly into an equivalent command on the underlying table, which will generally have noticeably better performance than is possible with either triggers or user-written rules. A view that has INSTEAD OF triggers or INSTEAD rules continues to operate the same as before. For the moment, security_barrier views are not considered simple. Also, we do not support WITH CHECK OPTION. These features may be added in future. Dean Rasheed, reviewed by Amit Kapila --- doc/src/sgml/intro.sgml | 2 +- doc/src/sgml/ref/alter_table.sgml | 8 +-- doc/src/sgml/ref/alter_view.sgml | 9 +-- doc/src/sgml/ref/create_rule.sgml | 25 ++++++-- doc/src/sgml/ref/create_view.sgml | 125 +++++++++++++++++++++++++++++++++----- doc/src/sgml/rules.sgml | 47 +++++++++++--- 6 files changed, 175 insertions(+), 41 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/intro.sgml b/doc/src/sgml/intro.sgml index 4d3f93f3174..f0dba6f56fb 100644 --- a/doc/src/sgml/intro.sgml +++ b/doc/src/sgml/intro.sgml @@ -110,7 +110,7 @@ triggers - views + updatable views transactional integrity diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 356419e2d08..5437626c3fe 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -147,11 +147,9 @@ ALTER TABLE [ IF EXISTS ] name These forms set or remove the default value for a column. - The default values only apply to subsequent INSERT - commands; they do not cause rows already in the table to change. - Defaults can also be created for views, in which case they are - inserted into INSERT statements on the view before - the view's ON INSERT rule is applied. + Default values only apply in subsequent INSERT + or UPDATE commands; they do not cause rows already in the + table to change. diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml index 521f05b84a1..0e2b140241e 100644 --- a/doc/src/sgml/ref/alter_view.sgml +++ b/doc/src/sgml/ref/alter_view.sgml @@ -80,10 +80,11 @@ ALTER VIEW [ IF EXISTS ] name RESET These forms set or remove the default value for a column. - A default value associated with a view column is - inserted into INSERT statements on the view before - the view's ON INSERT rule is applied, if - the INSERT does not specify a value for the column. + A view column's default value is substituted into any + INSERT or UPDATE command whose target is the + view, before applying any rules or triggers for the view. The view's + default will therefore take precedence over any default values from + underlying relations. diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index d4c33921290..381ea3ed6b4 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -45,10 +45,10 @@ CREATE [ OR REPLACE ] RULE name AS additional commands to be executed when a given command on a given table is executed. Alternatively, an INSTEAD rule can replace a given command by another, or cause a command - not to be executed at all. Rules are used to implement table + not to be executed at all. Rules are used to implement SQL views as well. It is important to realize that a rule is really a command transformation mechanism, or command macro. The - transformation happens before the execution of the commands starts. + transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule. More information about the rules system is in . @@ -73,13 +73,11 @@ CREATE [ OR REPLACE ] RULE name AS sufficient for your purposes) to replace update actions on the view with appropriate updates on other tables. If you want to support INSERT RETURNING and so on, then be sure to put a suitable - RETURNING clause into each of these rules. Alternatively, - an updatable view can be implemented using INSTEAD OF - triggers (see ). + RETURNING clause into each of these rules. - There is a catch if you try to use conditional rules for view + There is a catch if you try to use conditional rules for complex view updates: there must be an unconditional INSTEAD rule for each action you wish to allow on the view. If the rule is conditional, or is not @@ -95,6 +93,21 @@ CREATE [ OR REPLACE ] RULE name AS INSTEAD NOTHING action. (This method does not currently work to support RETURNING queries, however.) + + + + A view that is simple enough to be automatically updatable (see ) does not require a user-created rule in + order to be updatable. While you can create an explicit rule anyway, + the automatic update transformation will generally outperform an + explicit rule. + + + + Another alternative worth considering is to use INSTEAD OF + triggers (see ) in place of rules. + + diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 9e3bc2954f2..abbde94772c 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -127,17 +127,6 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW n Notes - - Currently, views are read only: the system will not allow an insert, - update, or delete on a view. You can get the effect of an updatable - view by creating INSTEAD 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. - - Use the statement to drop views. @@ -175,6 +164,105 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; to replace it (this includes being a member of the owning role). + + Updatable Views + + + updatable views + + + + Simple views are automatically updatable: the system will allow + INSERT, UPDATE and DELETE 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: + + + + + The view must have exactly one entry in its FROM list, + which must be a table or another updatable view. + + + + + + The view definition must not contain WITH, + DISTINCT, GROUP BY, HAVING, + LIMIT, or OFFSET clauses at the top level. + + + + + + The view definition must not contain set operations (UNION, + INTERSECT or EXCEPT) at the top level. + + + + + + All columns in the view's select list must be simple references to + columns of the underlying relation. They cannot be expressions, + literals or functions. System columns cannot be referenced, either. + + + + + + No column of the underlying relation can appear more than once in + the view's select list. + + + + + + The view must not have the security_barrier property. + + + + + + + If the view is automatically updatable the system will convert any + INSERT, UPDATE or DELETE statement + on the view into the corresponding statement on the underlying base + relation. + + + + 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 + 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 + that do not satisfy the WHERE condition and thus are not + visible through the view. + + + + 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 + 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. + + + + Note that the user performing the insert, update or delete on the view + must have the corresponding insert, update or delete privilege on the + view. In addition the view's owner must have the relevant privileges on + the underlying base relations, but the user performing the update does + not need any permissions on the underlying base relations (see + ). + + @@ -217,11 +305,15 @@ CREATE VIEW name [ ( CHECK OPTION - This option has to do with updatable views. All - INSERT and UPDATE commands on the view - will be checked to ensure data satisfy the view-defining - condition (that is, the new data would be visible through the - view). If they do not, the update will be rejected. + This option controls the behavior of automatically updatable views. + When given, INSERT and UPDATE commands on + the view will be checked to ensure new rows satisfy the + view-defining condition (that is, the new rows would be visible + through the view). If they do not, the update will be rejected. + Without CHECK OPTION, INSERT and + UPDATE commands on the view are allowed to create rows + that are not visible through the view. (The latter behavior is the + only one currently provided by PostgreSQL.) @@ -252,6 +344,7 @@ CREATE VIEW name [ ( CREATE OR REPLACE VIEW is a PostgreSQL language extension. So is the concept of a temporary view. + The WITH clause is an extension as well. diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index cc02ada7c71..5811de7942f 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -808,13 +808,28 @@ 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? Simply doing the substitutions + DELETE? 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. Instead, the rewriter assumes that the operation will be - handled by an INSTEAD OF trigger on the view. - (If there is no such trigger, the executor will throw an error - when execution starts.) Rewriting works slightly differently + work. There are several ways in which PostgreSQL + can support the appearance of updating a view, however. + + + + 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 . + + + + Alternatively, the operation may be handled by a user-provided + INSTEAD OF trigger on the view. + 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 @@ -842,10 +857,8 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; - If there are no INSTEAD OF triggers to update the view, - the executor will throw an error, because it cannot automatically - update a view by itself. To change this, we can define rules that - modify the behavior of INSERT, + Another possibility is for the user to define INSTEAD + rules that specify substitute actions for INSERT, 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 @@ -860,6 +873,22 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; evaluated first, and depending on the result, the triggers may not be used at all. + + + Automatic rewriting of an INSERT, + UPDATE, or DELETE query on a + simple view is always tried last. Therefore, if a view has rules or + triggers, they will override the default behavior of automatically + updatable views. + + + + If there are no INSTEAD rules or INSTEAD OF + triggers for the view, and the rewriter cannot automatically rewrite + the query as an update on the underlying base relation, an error will + be thrown because the executor cannot update a view as such. + + -- cgit v1.2.3