Movatterモバイル変換

39.2.1. HowSELECT Rules Work

39.2.2. View Rules in Non-SELECT Statements

39.2.3. The Power of Views inPostgreSQL

39.2.4. Updating a View

RulesON SELECT are applied to all queries as the last step, even if the command given is anINSERT,UPDATE orDELETE. 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. SoSELECT rules are described first.

Currently, there can be only one action in anON SELECT rule, and it must be an unconditionalSELECT action that isINSTEAD. This restriction was required to make rules safe enough to open them for ordinary users, and it restrictsON 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 forINSERT,UPDATE, andDELETE 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;

TheCREATE VIEW command for theshoelace view (which is the simplest one we have) will create a relationshoelace and an entry inpg_rewrite that tells that there is a rewrite rule that must be applied whenever the relationshoelace is referenced in a query's range table. The rule has no rule qualification (discussed later, with the non-SELECT rules, sinceSELECT rules currently cannot have them) and it isINSTEAD. 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 theSELECT statement in the view creation command.

Now we populateunit,shoe_data andshoelace_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 simplestSELECT you can do on our views, so we take this opportunity to explain the basics of view rules. TheSELECT * 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 forshoelace (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 entriesshoelace old andshoelace 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 expandold ornew — otherwise we'd have infinite recursion!) In this example, there are no rewrite rules forshoelace_data orunit, 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 theshoe_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 forshoe andshoelace 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.

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 aSELECT and one for any other command. Obviously, they have a different command type and for a command other than aSELECT, the result relation points to the range-table entry where the result should go. Everything else is absolutely the same. So having two tablest1 andt2 with columnsa andb, 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 consequence is, that both query trees result in similar execution plans: They are both joins over the two tables. For theUPDATE the missing columns fromt1 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 inUPDATE: 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 aSELECT command and the other is anUPDATE is handled higher up in the executor, where it knows that this is anUPDATE, and it knows that this result should go into tablet1. 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 inUPDATE (and also inDELETE) 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, theCTID can be used to retrieve the original row oft1 to be updated. After adding theCTID 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 ofPostgreSQL enters the stage. Old table rows aren't overwritten, and this is whyROLLBACK is fast. In anUPDATE, the new result row is inserted into the table (after stripping theCTID) and in the row header of the old row, which theCTID pointed to, thecmax andxmax 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.

The above demonstrates how the rule system incorporates view definitions into the original query tree. In the second example, a simpleSELECT 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 inPostgreSQL ensures that this is all information available about the query up to that point.

What happens if a view is named as the target relation for anINSERT,UPDATE,DELETE, orMERGE? 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 whichPostgreSQL 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 theINSERT,UPDATE,DELETE, orMERGE is applied to the base relation in the appropriate way. Views that are“simple enough” for this are calledautomatically updatable. For detailed information on the kinds of view that can be automatically updated, seeCREATE VIEW.

Alternatively, the operation may be handled by a user-providedINSTEAD OF trigger on the view (seeCREATE TRIGGER). Rewriting works slightly differently in this case. ForINSERT, the rewriter does nothing at all with the view, leaving it as the result relation for the query. ForUPDATE,DELETE, andMERGE, it's still necessary to expand the view query to produce the“old” rows that the command will attempt to update, delete, or merge. 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 specialCTID 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 anyCTID, since its rows do not have actual physical locations. Instead, for anUPDATE,DELETE, orMERGE operation, a specialwholerow 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 theINSTEAD 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 defineINSTEAD rules that specify substitute actions forINSERT,UPDATE, andDELETE 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 ofSection 39.4. Note that this will not work withMERGE, which currently does not support rules on the target relation other thanSELECT rules.

Note that rules are evaluated first, rewriting the original query before it is planned and executed. Therefore, if a view hasINSTEAD OF triggers as well as rules onINSERT,UPDATE, orDELETE, then the rules will be evaluated first, and depending on the result, the triggers may not be used at all.

Automatic rewriting of anINSERT,UPDATE,DELETE, orMERGE 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 noINSTEAD rules orINSTEAD 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.