Updatable views

From PostgreSQL wiki
Jump to navigationJump to search

This project aims to implement SQL92-compatible updatable views. Currently there is a prototype which implements single-relation views with local and cascaded check option via the present rule rewrite system.

The proposed patch was submitted for inclusion in PostgreSQL version 8.2, however, there were some serious concerns which makes it necessary to rethink the whole implementation and rule rewrite system. The attached discussion summarizes some of the most import issues which should be resolved by the final implementation.

This page is created to show development process, submit ideas and to support developers while designing the new core feature.


What should updatable views do at least?

The absolutely minimal goal is to reach compatibility with the SQL-92 Standard. They are described as follows:

  • Only one base relation is allowed. If we find a joined view, we treat the view as non-updatable.
  • No grouping, distinction, union or aggregation allowed
  • The view’s target list should only consist of “real” columns which are derived from the underlying view/relation directly. We could distinguish between “insertable”, “possible insertable” and “non-insertable”, as suggested in SQL-99, but we treat the whole target-list as non-insertable as soon as we get functions, constants or server-side variables in there.
  • The check option could only be applied to an updatable (insertable) view.

So far we should able to do the following: 

CREATE TABLE foo(id INTEGER PRIMARY KEY, 
                 name TEXT NOT NULL);


CREATE VIEW vfoo 
AS 
SELECT 
   id, 
   name 
FROM 
   foo 
WHERE
   id BETWEEN 1 AND 10000 
WITH CHECK OPTION;

This will create a table foo and an updatable view vfoo which allows to insert tuples with values for ID between 1 and 10000. Other values will be rejected and it is not possible to update values to contain values for id which violates this condition. As shown, we should consider a view qualification combined with a WITH CHECK OPTION option on view creation as a view constraint, validating any data that is inserted or updated against it. I think we don't need to take care for delete operations, because we can't delete data from the view which isn't in the view already.

The SQL standard defines two additional keywords for CHECK OPTION:

  • CASCADED CHECK OPTION
  • LOCAL CHECK OPTION
CREATE VIEW vfoo_name 
AS 
SELECT 
   id, name 
FROM 
   vfoo 
WHERE
  name LIKE 'bernd%'
WITH CASCADED CHECK OPTION;

CASCADED forces to check against all view constraints defined by all underlying view definitions. LOCAL checks against view constraints defined on the current selected view only. This makes things more complicated since we need to do recursive checks wether we have CASCADED or LOCAL check options in a view update chain. The example above will check insert and update operations against the view qualification (the view constraint) defined by the view vfoo_name and vfoo. If we are going to replace CASCADED with LOCAL, only the view constraint of the selected view vfoo_name is validated against new tuples.

Current implementation

Translators

The current implementation covers the complete requirements the SQL-92 standard defines and as described above. We do not support joined views and partial updatable column lists (as described in SQL-99 as possible insertable, insertable and non-insertable views).

The whole functionality is implemented on top of the existing rule rewrite system. Entry point into the updatable view stuff is src/backend/commands/view.c, function DefineView(). This will create the necessary virtual relation and stuff and last but not least the updatable view's rules via DefineViewRules(). This will cascade to CreateViewUpdateRules(), which is the entry point in src/backends/rewrite/viewUpdate.c for automatic creation of all updatable view rules.

We define three transformers there, which translate a given SELECT query tree into its corresponding DELETE, UPDATE or INSERT query tree:

  • transform_select_to_delete()
  • transform_select_to_update()
  • transform_select_to_insert()

Transforming a given query tree into a specific alternate action is not trivial, but not that hard it seems on the first cut. PostgreSQL’s Query structure defines some important fields in there, we need to touch:

  • targetList
  • joinTree (holds FROM and WHERE expressions/clauses)
  • rtable

Most of the work is to rewrite varno and resno’s and to adjust ordering of target entries, because views could have a different “order” of columns. Note that in SQL there is no such definition of “ordered columns”, but all columns are stored internally in a specific order, so we have to take care of this.

Before entering any translator we call the checkTree() functions to examine the given SQL query tree to match SQL-92 requirements. We are refusing any attempt to translate a given query tree into its corresponding update action if we found it violating the rules described above.

The result of these operations are three rules, which provide the required DML-capabilities for updatable views (per SQL-92), named as follows:

  • _INSERT: insert rule
  • _UPDATE: update rule
  • _DELETE: delete rule

We need to take care on replacing these rules when modifying an existing view with the REPLACE keyword.

Issues with the existing implementation

CHECK OPTION and multi-expression evaluation

The proposed implementation of CHECK OPTION has some issues, mainly because of volatile functions which produce incorrect results in rules because of double evaluation of expressions in the Rule Rewrite System [2].

We need to reconsider the existing implementation and to reimplement the CHECK OPTION in a way that allows any view condition to be evaluated in a safe way. One idea is to push all CHECK OPTIONs into a table constraint [4]. The rewriter would then be responsible to collect all required constraints and to apply them to the current query tree. However, to implement this reliable we need a plan invalidation mechanism to adjust cached query plans and teach them about new constraints collected there. Tom Lane planned this for 8.3, so let us see. Another issue raises up, when we want to cover subqueries in the check option. It seems the SQL standard allows this and other databases do it accordingly. We need to examine wether our constraint code is able to handle such expressions.

Another idea is to use something like RI Triggers (much like the same as foreign key checks are implemented in PostgreSQL). I do not know exactly wether the SQL standard allows subqueries in a CHECK OPTION, but if true this would be hard to implement in check constraints. I have not dug into this idea any deeper, but i think it is worth to consider.

Deficiencies in the Rule System

Transaction visibility logic is enforced on rules as on any other SQL command. For multi-action rules we need to consider that between each separate action there is a CommandCounterIncrement(), which forces subsequent actions to "see" the modifications of any previous action. This becomes important as soon as we are going to update joined views and their joinkey in an UPDATE command, for example.

Related Links

Retrieved from "https://wiki.postgresql.org/index.php?title=Updatable_views&oldid=17558"