Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / rules-views.txt

39.2. Views and the Rule System #

   39.2.1. How SELECT Rules Work
   39.2.2. View Rules in Non-SELECT Statements
   39.2.3. The Power of Views in PostgreSQL
   39.2.4. Updating a View

   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 39.2.4.

39.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.

39.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 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 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.

39.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.

39.2.4. Updating a View #

   What happens if a view is named as the target relation for an INSERT,
   UPDATE, DELETE, or MERGE? 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, DELETE, or MERGE
   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, DELETE, and MERGE, 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 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,
   DELETE, or MERGE 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 39.4. Note that this will not work with MERGE, which
   currently does not support rules on the target relation other than
   SELECT rules.

   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, DELETE, or MERGE 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.