Views in Postgres are
implemented using the rule system. In fact there is absolutely no
difference between a

CREATE VIEW myview AS SELECT * FROM mytab;

compared against the two commands

CREATE TABLE myview (same attribute list as for mytab);
CREATE RULE "_RETmyview" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;

because this is exactly what the CREATE VIEW command does
internally. This has some side effects. One of them is that the
information about a view in the Postgres system catalogs is exactly the same
as it is for a table. So for the query parsers, there is absolutely
no difference between a table and a view. They are the same thing -
relations. That is the important one for now.

Rules ON SELECT are applied to all queries as the last step,
even if the command given is an INSERT, UPDATE or DELETE. And they
have different semantics from the others in that they modify the
parsetree in place instead of creating a new one. So SELECT rules
are described first.

Currently, there could be only one action and it must be a
SELECT action that is INSTEAD. This restriction was required to
make rules safe enough to open them for ordinary users and it
restricts rules ON SELECT to real view rules.

The example for this document are two join views that do some
calculations and some more views using them in turn. One of the two
first views is customized later by adding rules for INSERT, UPDATE
and DELETE operations so that the final result will be a view that
behaves like a real table with some magic functionality. It is not
such a simple example to start from and this makes things harder to
get into. But it's better to have one example that covers all the
points discussed step by step rather than having many different
ones that might mix up in mind.

The database needed to play on the examples is named al_bundy.
You'll see soon why this is the database name. And it needs the
procedural language PL/pgSQL installed, because we need a little
min() function returning the lower of 2 integer values. We create
that as

I think most of us wear shoes and can realize that this is
really useful data. Well there are shoes out in the world that
don't require shoelaces, but this doesn't make Al's life easier and
so we ignore it.

The CREATE VIEW command for the shoelace view (which is the simplest one we have)
will create a relation shoelace and an entry in pg_rewrite that tells that there is a rewrite rule
that must be applied whenever the relation shoelace is referenced
in a queries rangetable. The rule has no rule qualification
(discussed in the non SELECT rules since SELECT rules currently
cannot have them) and it is INSTEAD. Note that rule qualifications
are not the same as query qualifications! The rules action has a
qualification.

The rules action is one querytree that is an exact copy of the
SELECT statement in the view creation command.

Note: The two extra range table entries for NEW and OLD
(named *NEW* and *CURRENT* for historical reasons in the printed
querytree) you can see in the pg_rewrite entry aren't of interest for SELECT
rules.

Now we populate unit,
shoe_data and shoelace_data and Al types the first SELECT in his
life:

and this is given to the rule system. The rule system walks
through the rangetable and checks if there are rules in pg_rewrite for any relation. When processing the
rangetable entry for shoelace (the only
one up to now) it finds the rule '_RETshoelace' with the parsetree

Note that the parser changed the calculation and
qualification into calls to the appropriate functions. But in fact
this changes nothing. The first step in rewriting is merging the
two rangetables. The resulting parsetree then reads

And in step 3 it replaces all the variables in the parsetree,
that reference the rangetable entry (the one for shoelace that is currently processed) by the
corresponding targetlist expressions from the rule action. This
results in the final query

That was the first rule applied. While this was done, the
rangetable has grown. So the rule system continues checking the
range table entries. The next one is number 2 (shoelace *OLD*).
Relation shoelace has a rule, but this
rangetable entry isn't referenced in any of the variables of the
parsetree, so it is ignored. Since all the remaining rangetable
entries either have no rules in pg_rewrite or aren't referenced, it reaches the end
of the rangetable. Rewriting is complete and the above is the final
result given into the optimizer. The optimizer ignores the extra
rangetable entries that aren't referenced by variables in the
parsetree and the plan produced by the planner/optimizer would be
exactly the same as if Al had typed the above SELECT query instead
of the view selection.

Now we face Al with the problem that the Blues Brothers appear
in his shop and want to buy some new shoes, and as the Blues
Brothers are, they want to wear the same shoes. And they want to
wear them immediately, so they need shoelaces too.

Al needs to know for which shoes currently in the store he has
the matching shoelaces (color and size) and where the total number
of exactly matching pairs is greater or equal to two. We theach him
how to do and he asks his database:

In reality the AND clauses in the qualification will be
operator nodes of type AND with a left and right expression. But
that makes it lesser readable as it already is, and there are more
rules to apply. So I only put them into some parantheses to group
them into logical units in the order they where added and we
continue with the rule for relation shoe
as it is the next rangetable entry that is referenced and has a
rule. The result of applying it is

Recursive processing of rules rewrote one SELECT from a view
into a parsetree, that is equivalent to exactly that what Al had to
type if there would be no views at all.

Note: There is currently no recursion stopping
mechanism for view rules in the rule system (only for the other
rules). This doesn't hurt much, because the only way to push this
into an endless loop (blowing up the backend until it reaches the
memory limit) is to create tables and then setup the view rules
by hand with CREATE RULE in such a way, that one selects from the
other that selects from the one. This could never happen if
CREATE VIEW is used because on the first CREATE VIEW, the second
relation does not exist and thus the first view cannot select
from the second.

Two details of the parsetree aren't touched in the description
of view rules above. These are the commandtype and the
resultrelation. In fact, view rules don't need these
informations.

There are only a few differences between a parsetree for a
SELECT and one for any other command. Obviously they have another
commandtype and this time the resultrelation points to the
rangetable entry where the result should go. Anything else is
absolutely the same. So having two tables t1 and t2 with attributes
a and b, the parsetrees for the two statements

The targetlists contain one variable that points to
attribute b of the rangetable entry for table t2.

The qualification expressions compare the attributes a of
both ranges for equality.

The consequence is, that both parsetrees result in similar
execution plans. They are both joins over the two tables. For the
UPDATE the missing columns from t1 are added to the targetlist by
the optimizer and the final parsetree will read as

UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a;

and thus the executor run over the join will produce exactly
the same result set as a

SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;

will do. But there is a little problem in UPDATE. The
executor does not care what the results from the join it is doing
are meant for. It just produces a result set of rows. The
difference that one is a SELECT command and the other is an UPDATE
is handled in the caller of the executor. The caller still knows
(looking at the parsetree) that this is an UPDATE, and he knows
that this result should go into table t1. But which of the 666 rows
that are there has to be replaced by the new row? The plan executed
is a join with a qualification that potentially could produce any
number of rows between 0 and 666 in unknown order.

To resolve this problem, another entry is added to the
targetlist in UPDATE and DELETE statements. The current tuple ID
(ctid). This is a system attribute with a special feature. It
contains the block and position in the block for the row. Knowing
the table, the ctid can be used to find one specific row in a 1.5GB
sized table containing millions of rows by fetching one single data
block. After adding the ctid to the targetlist, the final result
set could be defined as

SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;

Now another detail of Postgres enters the stage. At this moment,
table rows aren't overwritten and this is why ABORT TRANSACTION is
fast. In an UPDATE, the new result row is inserted into the table
(after stripping ctid) and in the tuple header of the row that ctid
pointed to the cmax and xmax entries are set to the current command
counter and current transaction ID. Thus the old row is hidden and
after the transaction commited the vacuum cleaner can really move
it out.

Knowing that all, we can simply apply view rules in absolutely
the same way to any command. There is no difference.

The above demonstrates how the rule system incorporates view
definitions into the original parsetree. In the second example a
simple SELECT from one view created a final parsetree that is a
join of 4 tables (unit is used twice with different names).

The benefit of implementing views with the rule system is, that
the optimizer has all the information about which tables have to be
scanned plus the relationships between these tables plus the
restrictive qualifications from the views plus the qualifications
from the original query in one single parsetree. And this is still
the situation when the original query is already a join over views.
Now the optimizer has to decide which is the best path to execute
the query. The more information the optimizer has, the better this
decision can be. And the rule system as implemented in Postgres ensures, that this is all information
available about the query up to now.

There was a long time where the Postgres rule system was considered broken.
The use of rules was not recommended and the only part working
where view rules. And also these view rules made problems because
the rule system wasn't able to apply them properly on other
statements than a SELECT (for example an UPDATE that used data from
a view didn't work).

During that time, development moved on and many features where
added to the parser and optimizer. The rule system got more and
more out of sync with their capabilities and it became harder and
harder to start fixing it. Thus, noone did.

For 6.4, someone locked the door, took a deep breath and
shuffled that damned thing up. What came out was a rule system with
the capabilities described in this document. But there are still
some constructs not handled and some where it fails due to things
that are currently not supported by the Postgres query optimizer.

Views with aggregate columns have bad problems. Aggregate
expressions in qualifications must be used in subselects.
Currently it is not possible to do a join of two views, each
having an aggregate column, and compare the two aggregate
values in the qualification. In the meantime it is possible to
put these aggregate expressions into functions with the
appropriate arguments and use them in the view definition.

Views of unions are currently not supported. Well it's easy
to rewrite a simple SELECT into a union. But it is a little
difficult if the view is part of a join doing an update.

ORDER BY clauses in view definitions aren't supported.

DISTINCT isn't supported in view definitions.

There is no good reason why the optimizer should not handle
parsetree constructs that the parser could never produce due to
limitations in the SQL syntax. The
author hopes that these items disappear in the future.

The interesting thing is that the return code for INSERT gave
us an object ID and told that 1 row has been inserted. But it
doesn't appear in shoe_data. Looking into
the database directory we can see, that the database file for the
view relation shoe seems now to have a
data block. And that is definitely the case.

We can also issue a DELETE and if it does not have a
qualification, it tells us that rows have been deleted and the next
vacuum run will reset the file to zero size.

The reason for that behaviour is, that the parsetree for the
INSERT does not reference the shoe
relation in any variable. The targetlist contains only constant
values. So there is no rule to apply and it goes down unchanged
into execution and the row is inserted. And so for the DELETE.

To change this we can define rules that modify the behaviour of
non-SELECT queries. This is the topic of the next section.