rules.sgml

PostgreSQL7.4.6 for Linux

           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;
</ProgramListing>

    The <command>CREATE VIEW</command> command for the
    <literal>shoelace</literal> view (which is the simplest one we
    have) will create a relation <literal>shoelace</> and an entry in
    <structname>pg_rewrite</structname> that tells that there is a
    rewrite rule that must be applied whenever the relation <literal>shoelace</>
    is referenced in a query's range table.  The rule has no rule
    qualification (discussed later, with the non-<command>SELECT</> rules, since
    <command>SELECT</> rules currently cannot have them) and it is <literal>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
    <command>SELECT</command> statement in the view creation command.
</Para>
    
    <Note>
    <Para>
    The two extra range
    table entries for <literal>NEW</> and <literal>OLD</> (named <literal>*NEW*</> and <literal>*OLD*</> for
    historical reasons in the printed query tree) you can see in
    the <structname>pg_rewrite</structname> entry aren't of interest
    for <command>SELECT</command> rules.
    </Para>
    </Note>

<Para>
    Now we populate <literal>unit</literal>, <literal>shoe_data</literal>
    and <literal>shoelace_data</literal> and run a simple query on a view:

<ProgramListing>
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)
</ProgramListing>
   </para>

   <para>
    This is the simplest <command>SELECT</command> you can do on our
    views, so we take this opportunity to explain the basics of view
    rules.  The <literal>SELECT * FROM shoelace</literal> was
    interpreted by the parser and produced the query tree

<ProgramListing>
SELECT shoelace.sl_name, shoelace.sl_avail,
       shoelace.sl_color, shoelace.sl_len,
       shoelace.sl_unit, shoelace.sl_len_cm
  FROM shoelace shoelace;
</ProgramListing>

    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
    <literal>shoelace</literal> (the only one up to now) it finds the
    <literal>_RETURN</literal> rule with the query tree

<ProgramListing>
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;
</ProgramListing>
</Para>

<Para>
    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

<ProgramListing>
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;
</ProgramListing>

     There is one difference however: the subquery's range table has two
     extra entries <literal>shoelace *OLD*</> and <literal>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.
</Para>

<Para>
    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 <literal>*OLD*</> or <literal>*NEW*</> --- otherwise we'd have infinite recursion!)
    In this example, there are no rewrite rules for <literal>shoelace_data</> or <literal>unit</>,
    so rewriting is complete and the above is the final result given to
    the planner.
</Para>

<Para>
    No 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.

<ProgramListing>
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)
</ProgramListing>
</Para>

<Para>
    The output of the parser this time is the query tree

<ProgramListing>
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;
</ProgramListing>

    The first rule applied will be the one for the 
    <literal>shoe_ready</literal> view and it results in the
    query tree

<ProgramListing>
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;
</ProgramListing>

    Similarly, the rules for <literal>shoe</literal> and
    <literal>shoelace</literal> are substituted into the range table of
    the subquery, leading to a three-level final query tree:

<ProgramListing>
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;
</ProgramListing>
   </para>

   <para>
    It turns out that the planner will collapse this tree into a
    two-level query tree: the bottommost <command>SELECT</command>
    commands will be <quote>pulled up</quote> into the middle
    <command>SELECT</command> since there's no need to process them
    separately.  But the middle <command>SELECT</command> will remain
    separate from the top, because it contains aggregate functions.
    If we pulled those up it would change the behavior of the topmost
    <command>SELECT</command>, which we don't want.  However,
    collapsing the query tree is an optimization that the rewrite
    system doesn't have to concern itself with.
   </para>

   <Note>
    <Para>
    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 <command>CREATE RULE</command> in such a way, that one
    selects from the other that selects from the one.  This could
    never happen if <command>CREATE VIEW</command> is used because for
    the first <command>CREATE VIEW</command>, the second relation does
    not exist and thus the first view cannot select from the second.
    </Para>
   </Note>
</Sect2>

<Sect2>
<Title>View Rules in Non-<command>SELECT</command> Statements</Title>

<Para>
    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.
</Para>

<Para>
    There are only a few differences between a query tree for a
    <command>SELECT</command> and one for any other
    command. Obviously, they have a different command type and for a
    command other than a <command>SELECT</command>, the result
    relation points to the range-table entry where the result should
    go.  Everything else is absolutely the same.  So having two tables
    <literal>t1</> and <literal>t2</> with columns <literal>a</> and
    <literal>b</>, the query trees for the two statements

<ProgramListing>
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;

UPDATE t1 SET b = t2.b WHERE t1.a = t2.a;
</ProgramListing>

    are nearly identical.  In particular:

    <ItemizedList>
        <ListItem>
	<Para>
	    The range tables contain entries for the tables <literal>t1</> and <literal>t2</>.
	</Para>
        </ListItem>

        <ListItem>
	<Para>
	    The target lists contain one variable that points to column
	    <literal>b</> of the range table entry for table <literal>t2</>.
	</Para>
        </ListItem>

        <ListItem>
	<Para>
	    The qualification expressions compare the columns <literal>a</> of both
	    range-table entries for equality.
	</Para>
        </ListItem>

        <ListItem>
	<Para>
	    The join trees show a simple join between <literal>t1</> and <literal>t2</>.
	</Para>
        </ListItem>
    </ItemizedList>
   </para>

   <para>
    The consequence is, that both query trees result in similar
    execution plans: They are both joins over the two tables. For the
    <command>UPDATE</command> the missing columns from <literal>t1</> are added to
    the target list by the planner and the final query tree will read
    as

<ProgramListing>
UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a;
</ProgramListing>

    and thus the executor run over the join will produce exactly the
    same result set as a

<ProgramListing>
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
</ProgramListing>
    
    will do. But there is a little problem in
    <command>UPDATE</command>: 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
    <command>SELECT</command> command and the other is an
    <command>UPDATE</command> is handled in the caller of the
    executor. The caller still knows (looking at the query tree) that
    this is an <command>UPDATE</command>, and it knows that this
    result should go into table <literal>t1</>. But which of the rows that are
    there has to be replaced by the new row?
</Para>

<Para>
    To resolve this problem, another entry is added to the target list
    in <command>UPDATE</command> (and also in
    <command>DELETE</command>) statements: the current tuple ID
    (<acronym>CTID</>).<indexterm><primary>CTID</></>  This is a system column containing the
    file block number and position in the block for the row. Knowing
    the table, the <acronym>CTID</> can be used to retrieve the
    original row of <literal>t1</> to be updated.  After adding the <acronym>CTID</>
    to the target list, the query actually looks like

<ProgramListing>
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
</ProgramListing>
    
    Now another detail of <ProductName>PostgreSQL</ProductName> enters
    the stage. Old table rows aren't overwritten, and this
    is why <command>ROLLBACK</command> is fast. In an <command>UPDATE</command>,
    the new result row is inserted into the table (after stripping the
    <acronym>CTID</>) and in the row header of the old row, which the
    <acronym>CTID</> pointed to, the <literal>cmax</> and
    <literal>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.
</Para>

<Para>
    Knowing all that, we can simply apply view rules in absolutely
    the same way to any command. There is no difference.
</Para>
</Sect2>

<Sect2>
<Title>The Power of Views in <ProductName>PostgreSQL</ProductName></Title>

<Para>
    The above demonstrates how the rule system incorporates view
    definitions into the original query tree. In the second example, a
    simple <command>SELECT</command> from one view created a final
    query tree that is a join of 4 tables (<literal>unit</> was used twice with
    different names).
</Para>

<Para>
    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 <ProductName>PostgreSQL</ProductName>
    ensures, that this is all information available about the query
    up to that point.
</Para>
</Sect2>

<Sect2 id="rules-views-update">
<Title>Updating a View</Title>

<Para>
    What happens if a view is named as the target relation for an
    <command>INSERT</command>, <command>UPDATE</command>, or
    <command>DELETE</command>?  After doing the substitutions
    described above, we will have a query tree in which the result
    relation points at a subquery range-table entry.  This will not
    work, so the rewriter throws an error if it sees it has produced
    such a thing.