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 parse 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 rules ON SELECT to real view rules.
The examples 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 with 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
CREATE FUNCTION min(integer, integer) RETURNS integer AS '
BEGIN
IF $1 < $2 THEN
RETURN $1;
END IF;
RETURN $2;
END;
' LANGUAGE plpgsql; The real tables we need in the first two rule system descriptions
are these:
CREATE TABLE shoe_data (
shoename char(10), -- primary key
sh_avail integer, -- available # of pairs
slcolor char(10), -- preferred shoelace color
slminlen float, -- miminum shoelace length
slmaxlen float, -- maximum shoelace length
slunit char(8) -- length unit
);
CREATE TABLE shoelace_data (
sl_name char(10), -- primary key
sl_avail integer, -- available # of pairs
sl_color char(10), -- shoelace color
sl_len float, -- shoelace length
sl_unit char(8) -- length unit
);
CREATE TABLE unit (
un_name char(8), -- the primary key
un_fact float -- factor to transform to cm
);
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 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 rule's action has a query qualification.
The rule's action 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 *CURRENT* 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 Al types the first
SELECT in his life:
al_bundy=> INSERT INTO unit VALUES ('cm', 1.0);
al_bundy=> INSERT INTO unit VALUES ('m', 100.0);
al_bundy=> INSERT INTO unit VALUES ('inch', 2.54);
al_bundy=>
al_bundy=> INSERT INTO shoe_data VALUES
al_bundy-> ('sh1', 2, 'black', 70.0, 90.0, 'cm');
al_bundy=> INSERT INTO shoe_data VALUES
al_bundy-> ('sh2', 0, 'black', 30.0, 40.0, 'inch');
al_bundy=> INSERT INTO shoe_data VALUES
al_bundy-> ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
al_bundy=> INSERT INTO shoe_data VALUES
al_bundy-> ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
al_bundy=>
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl1', 5, 'black', 80.0, 'cm');
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl2', 6, 'black', 100.0, 'cm');
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl3', 0, 'black', 35.0 , 'inch');
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl4', 8, 'black', 40.0 , 'inch');
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl5', 4, 'brown', 1.0 , 'm');
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl6', 0, 'brown', 0.9 , 'm');
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl7', 7, 'brown', 60 , 'cm');
al_bundy=> INSERT INTO shoelace_data VALUES
al_bundy-> ('sl8', 1, 'brown', 40 , 'inch');
al_bundy=>
al_bundy=> 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)
It's the simplest SELECT Al can do on our views, so we take this
to explain the basics of view rules.
The SELECT * FROM shoelace was interpreted by the parser and
produced the parse 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 in pg_rewrite
for any relation. When processing the range table entry for
shoelace (the only one up to now) it finds the
_RETURN rule with the parse tree
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
float8mul(s.sl_len, u.un_fact) AS sl_len_cm
FROM shoelace *OLD*, shoelace *NEW*,
shoelace_data s, unit u
WHERE bpchareq(s.sl_unit, u.un_name);
Note that the parser changed the calculation and qualification into
calls to the appropriate functions. But
in fact this changes nothing.
To expand the view, the rewriter simply creates a subselect range-table
entry containing the rule's action parse tree, and substitutes this
range table entry for the original one that referenced the view. The
resulting rewritten parse tree is almost the same as if Al 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 sub-query's range table has two
extra entries shoelace *OLD*, shoelace *NEW*. These entries don't
participate directly in the query, since they aren't referenced by
the sub-query's join tree or target list. The rewriter uses them
to store the access permission check info 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 permissions 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 sub-query 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 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 teach him what to do and he asks his database:
al_bundy=> 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)
Al is a shoe guru and so he knows that only shoes of type sh1
would fit (shoelace sl7 is brown and shoes that need brown shoelaces
aren't shoes the Blues Brothers would ever wear).
The output of the parser this time is the parse 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 int4ge(shoe_ready.total_avail, 2);
The first rule applied will be the one for the
shoe_ready view and it results in the
parse 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 int4ge(shoe_ready.total_avail, 2);
Similarly, the rules for shoe and
shoelace are substituted into the range table of
the sub-query, 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 int4ge(shoe_ready.total_avail, 2);
It turns out that the planner will collapse this tree into a two-level
query tree: the bottommost selects 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 (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
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 parse 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 parse tree for a SELECT
and one for any other command. Obviously they have another command type
and this time 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 attributes
a and b, the parse 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.
The range tables contain entries for the tables t1 and t2.
The target lists contain one variable that points to attribute
b of the range table entry for table t2.
The qualification expressions compare the attributes a of both
ranges for equality.
The join trees show a simple join between t1 and t2.
The consequence is, that both parse 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 parse 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
parse tree) that this is an UPDATE, and he 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 attribute 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 t1 row 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. 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 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.
