Updatable views

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 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_nameandvfoo. 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.