41.2. Views and the Rule System
Views in
PostgreSQL
are implemented
using the rule system. A view is basically an empty table (having no
actual storage) with an
ON SELECT DO INSTEAD
rule.
Conventionally, that rule is named
_RETURN
.
So a view like
CREATE VIEW myview AS SELECT * FROM mytab;
is very nearly the same thing as
CREATE TABLE myview (same column list as mytab
);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;
although you can't actually write that, because tables are not
allowed to have
ON SELECT
rules.
A view can also have other kinds of
DO INSTEAD
rules, allowing
INSERT
,
UPDATE
,
or
DELETE
commands to be performed on the view
despite its lack of underlying storage.
This is discussed further below, in
Section 41.2.4
.
41.2.1. How
SELECT
Rules Work
#
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 rules on the other command types in that they modify the
query tree in place instead of creating a new one. So
SELECT
rules are described first.
Currently, there can be only one action in an
ON SELECT
rule, and it must
be an unconditional
SELECT
action that is
INSTEAD
. This restriction was
required to make rules safe enough to open them for ordinary users, and
it restricts
ON SELECT
rules to act like views.
The examples for this chapter 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. This 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 real tables we need in the first two rule system descriptions are these:
CREATE TABLE shoe_data ( shoename text, -- primary key sh_avail integer, -- available number of pairs slcolor text, -- preferred shoelace color slminlen real, -- minimum shoelace length slmaxlen real, -- maximum shoelace length slunit text -- length unit ); CREATE TABLE shoelace_data ( sl_name text, -- primary key sl_avail integer, -- available number of pairs sl_color text, -- shoelace color sl_len real, -- shoelace length sl_unit text -- length unit ); CREATE TABLE unit ( un_name text, -- primary key un_fact real -- factor to transform to cm );
As you can see, they represent shoe-store data.
The views are created as:
CREATE VIEW shoe AS SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen, sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE sh.slunit = un.un_name; CREATE VIEW shoelace AS SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, s.sl_len * u.un_fact AS sl_len_cm FROM shoelace_data s, unit u WHERE s.sl_unit = u.un_name; CREATE VIEW shoe_ready AS SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, least(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm;
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 query's range table. The rule has no rule
qualification (discussed later, with 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 action of our rule has a query qualification.
The action of the rule is one query tree that is a copy of the
SELECT
statement in the view creation command.
Note
The two extra range
table entries for
NEW
and
OLD
that 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 run a simple query on a view:
INSERT INTO unit VALUES ('cm', 1.0); INSERT INTO unit VALUES ('m', 100.0); INSERT INTO unit VALUES ('inch', 2.54); INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm'); INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch'); INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm'); INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm'); INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch'); INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch'); INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm'); INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm'); INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm'); INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch'); SELECT * FROM shoelace; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm -----------+----------+----------+--------+---------+----------- sl1 | 5 | black | 80 | cm | 80 sl2 | 6 | black | 100 | cm | 100 sl7 | 7 | brown | 60 | cm | 60 sl3 | 0 | black | 35 | inch | 88.9 sl4 | 8 | black | 40 | inch | 101.6 sl8 | 1 | brown | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 0 | brown | 0.9 | m | 90 (8 rows)
This is the simplest
SELECT
you can do on our
views, so we take this opportunity to explain the basics of view
rules. The
SELECT * FROM shoelace
was
interpreted by the parser and produced the query tree:
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace shoelace;
and this is given to the rule system. The rule system walks through the
range table and checks if there are rules
for any relation. When processing the range table entry for
shoelace
(the only one up to now) it finds the
_RETURN
rule with the query tree:
SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, s.sl_len * u.un_fact AS sl_len_cm FROM shoelace old, shoelace new, shoelace_data s, unit u WHERE s.sl_unit = u.un_name;
To expand the view, the rewriter simply creates a subquery range-table entry containing the rule's action query tree, and substitutes this range table entry for the original one that referenced the view. The resulting rewritten query tree is almost the same as if you had typed:
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM (SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, s.sl_len * u.un_fact AS sl_len_cm FROM shoelace_data s, unit u WHERE s.sl_unit = u.un_name) shoelace;
There is one difference however: the subquery's range table has two
extra entries
shoelace old
and
shoelace new
. These entries don't
participate directly in the query, since they aren't referenced by
the subquery's join tree or target list. The rewriter uses them
to store the access privilege check information that was originally present
in the range-table entry that referenced the view. In this way, the
executor will still check that the user has proper privileges to access
the view, even though there's no direct use of the view in the rewritten
query.
That was the first rule applied. The rule system will continue checking
the remaining range-table entries in the top query (in this example there
are no more), and it will recursively check the range-table entries in
the added subquery to see if any of them reference views. (But it
won't expand
old
or
new
- otherwise we'd have infinite recursion!)
In this example, there are no rewrite rules for
shoelace_data
or
unit
,
so rewriting is complete and the above is the final result given to
the planner.
Now we want to write a query that finds out for which shoes currently in the store we have the matching shoelaces (color and length) and where the total number of exactly matching pairs is greater than or equal to two.
SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail ----------+----------+---------+----------+------------- sh1 | 2 | sl1 | 5 | 2 sh3 | 4 | sl7 | 7 | 4 (2 rows)
The output of the parser this time is the query tree:
SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail FROM shoe_ready shoe_ready WHERE shoe_ready.total_avail >= 2;
The first rule applied will be the one for the
shoe_ready
view and it results in the
query tree:
SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail FROM (SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, least(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready WHERE shoe_ready.total_avail >= 2;
Similarly, the rules for
shoe
and
shoelace
are substituted into the range table of
the subquery, leading to a three-level final query tree:
SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail FROM (SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, least(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM (SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen, sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE sh.slunit = un.un_name) rsh, (SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, s.sl_len * u.un_fact AS sl_len_cm FROM shoelace_data s, unit u WHERE s.sl_unit = u.un_name) rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready WHERE shoe_ready.total_avail > 2;
This might look inefficient, but the planner will collapse this into a single-level query tree by " pulling up " the subqueries, and then it will plan the joins just as if we'd written them out manually. So collapsing the query tree is an optimization that the rewrite system doesn't have to concern itself with.
41.2.2. View Rules in Non-
SELECT
Statements
#
Two details of the query tree aren't touched in the description of view rules above. These are the command type and the result relation. In fact, the command type is not needed by view rules, but the result relation may affect the way in which the query rewriter works, because special care needs to be taken if the result relation is a view.
There are only a few differences between a query tree for a
SELECT
and one for any other
command. Obviously, they have a different command type and for a
command other than a
SELECT
, the result
relation points to the range-table entry where the result should
go. Everything else is absolutely the same. So having two tables
t1
and
t2
with columns
a
and
b
, the query trees for the two statements:
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;
are nearly identical. In particular:
-
The range tables contain entries for the tables
t1
andt2
. -
The target lists contain one variable that points to column
b
of the range table entry for tablet2
. -
The qualification expressions compare the columns
a
of both range-table entries for equality. -
The join trees show a simple join between
t1
andt2
.
The consequence is, that both query trees 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 target list by the planner and the final query tree will read
as:
UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;
and thus the executor run over the join will produce exactly the same result set as:
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
But there is a little problem in
UPDATE
: the part of the executor plan that does
the join does not care what the results from the join are
meant for. It just produces a result set of rows. The fact that
one is a
SELECT
command and the other is an
UPDATE
is handled higher up in the executor, where
it knows that this is an
UPDATE
, and it knows that
this result should go into table
t1
. But which of the rows
that are there has to be replaced by the new row?
To resolve this problem, another entry is added to the target list
in
UPDATE
(and also in
DELETE
) statements: the current tuple ID
(
CTID
).
This is a system column containing the
file block number and position in the block for the row. Knowing
the table, the
CTID
can be used to retrieve the
original row of
t1
to be updated. After adding the
CTID
to the target list, the query actually looks like:
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
Now another detail of
PostgreSQL
enters
the stage. Old table rows aren't overwritten, and this
is why
ROLLBACK
is fast. In an
UPDATE
,
the new result row is inserted into the table (after stripping the
CTID
) and in the row header of the old row, which the
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 commits the vacuum cleaner can eventually remove
the dead row.
Knowing all that, we can simply apply view rules in absolutely the same way to any command. There is no difference.
41.2.3. The Power of Views in PostgreSQL #
The above demonstrates how the rule system incorporates view
definitions into the original query tree. In the second example, a
simple
SELECT
from one view created a final
query tree that is a join of 4 tables (
unit
was used twice with
different names).
The benefit of implementing views with the rule system is that the planner 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 query tree. And this is still the situation when the original query is already a join over views. The planner has to decide which is the best path to execute the query, and the more information the planner has, the better this decision can be. And the rule system as implemented in PostgreSQL ensures that this is all information available about the query up to that point.
41.2.4. Updating a View #
What happens if a view is named as the target relation for an
INSERT
,
UPDATE
, or
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. There are several ways in which
PostgreSQL
can support the appearance of updating a view, however.
In order of user-experienced complexity those are: automatically substitute
in the underlying table for the view, execute a user-defined trigger,
or rewrite the query per a user-defined rule.
These options are discussed below.
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
CREATE VIEW
.
Alternatively, the operation may be handled by a user-provided
INSTEAD OF
trigger on the view
(see
CREATE TRIGGER
).
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
DELETE
, it's still necessary to expand the
view query to produce the
"
old
"
rows that the command will
attempt to update or delete. So the view is expanded as normal,
but another unexpanded range-table entry is added to the query
to represent the view in its capacity as the result relation.
The problem that now arises is how to identify the rows to be
updated in the view. Recall that when the result relation
is a table, a special
CTID
entry is added to the target
list to identify the physical locations of the rows to be updated.
This does not work if the result relation is a view, because a view
does not have any
CTID
, since its rows do not have
actual physical locations. Instead, for an
UPDATE
or
DELETE
operation, a special
wholerow
entry is added to the target list, which expands to include all
columns from the view. The executor uses this value to supply the
"
old
"
row to the
INSTEAD OF
trigger. It is
up to the trigger to work out what to update based on the old and
new row values.
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
of
Section 41.4
.
Note that rules are evaluated first, rewriting the original query
before it is planned and executed. Therefore, if a view has
INSTEAD OF
triggers as well as rules on
INSERT
,
UPDATE
, or
DELETE
, then the rules will be
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.