From PostgreSQL wiki
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
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.
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
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
LOCAL, only the view constraint of the selected view vfoo_name is validated against new tuples.
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
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
INSERT query tree:
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:
- joinTree (holds
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
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 .
We need to reconsider the existing implementation and to reimplement the
in a way that allows any view condition to be evaluated in a safe way. One idea is to
CHECK OPTIONs into a table constraint . 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
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.
-  Studienarbeit Updatable Views mit dem PostgreSQL Rule System (German)
-  CHECK OPTION and volatile functions
-  Patch for Updatable Views (as submitted for inclusion in 8.2, updated against 8.3 without CHECK OPTION support)
-  Thoughts from Tom Lane
-  updateable views and updatable cursors
-  Merged prototype into 8.4dev
-  More thoughts from Tom Lane