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.
For the example, we need a little min function that
returns the lower of 2 integer values. We create that as
CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;
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,
min(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 (named *NEW* and *OLD* for
historical reasons in the printed query tree) 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 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,
min(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,
min(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;
It turns out that the planner will collapse this tree into a
two-level query tree: the bottommost SELECT
commands will be "pulled up" into the middle
SELECT since there's no need to process them
separately. But the middle SELECT will remain
separate from the top, because it contains aggregate functions.
If we pulled those up it would change the behavior of the topmost
SELECT, which we don't want. However,
collapsing the query tree is an optimization that the rewrite
system doesn't have to concern itself with.
Note: There is currently no recursion stopping mechanism for view rules
in the rule system (only for the other kinds of rules). This
doesn't hurt much, because the only way to push this into an
endless loop (bloating up the server process 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 for
the first CREATE VIEW, the second relation does
not exist and thus the first view cannot select from the second.
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, view rules don't need this information.
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 WHERE t1.a = t2.a;
are nearly identical. In particular:
The range tables contain entries for the tables t1 and t2.
The target lists contain one variable that points to column
b of the range table entry for table t2.
The qualification expressions compare the columns a of both
range-table entries for equality.
The join trees show a simple join between t1 and t2.
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 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 query tree) 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 EnterpriseDB 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 committed the vacuum cleaner can really move it
out.
Knowing all that, we can simply apply view rules in absolutely
the same way to any command. There is no difference.