The code to unlink dropped relations in FinishPreparedTransaction() was

[PostgreSQL.git] / doc / src / sgml / rules.sgml

<!-- $PostgreSQL$ -->

<chapter id="rules">
<title>The Rule System</title>

 <indexterm zone="rules">
  <primary>rule</primary>
 </indexterm>

<para>
     This chapter discusses the rule system in
     <productname>PostgreSQL</productname>.  Production rule systems
     are conceptually simple, but there are many subtle points
     involved in actually using them.
</para>

<para>
     Some other database systems define active database rules, which
     are usually stored procedures and triggers.  In
     <productname>PostgreSQL</productname>, these can be implemented
     using functions and triggers as well.
</para>

<para>
     The rule system (more precisely speaking, the query rewrite rule
     system) is totally different from stored procedures and triggers.
     It modifies queries to take rules into consideration, and then
     passes the modified query to the query planner for planning and
     execution.  It is very powerful, and can be used for many things
     such as query language procedures, views, and versions.  The
     theoretical foundations and the power of this rule system are
     also discussed in <xref linkend="STON90b"> and <xref
     linkend="ONG90">.
</para>

<sect1 id="querytree">
<title>The Query Tree</title>

<indexterm zone="querytree">
 <primary>query tree</primary>
</indexterm>

<para>
    To understand how the rule system works it is necessary to know
    when it is invoked and what its input and results are.
</para>

<para>
    The rule system is located between the parser and the planner.
    It takes the output of the parser, one query tree, and the user-defined
    rewrite rules, which are also
    query trees with some extra information, and creates zero or more
    query trees as result. So its input and output are always things
    the parser itself could have produced and thus, anything it sees
    is basically representable as an <acronym>SQL</acronym> statement.
</para>

<para>
    Now what is a query tree? It is an internal representation of an
    <acronym>SQL</acronym> statement where the single parts that it is
    built from are stored separately. These query trees can be shown
    in the server log if you set the configuration parameters
    <varname>debug_print_parse</varname>,
    <varname>debug_print_rewritten</varname>, or
    <varname>debug_print_plan</varname>.  The rule actions are also
    stored as query trees, in the system catalog
    <structname>pg_rewrite</structname>.  They are not formatted like
    the log output, but they contain exactly the same information.
</para>

<para>
    Reading a raw query tree requires some experience.  But since
    <acronym>SQL</acronym> representations of query trees are
    sufficient to understand the rule system, this chapter will not
    teach how to read them.
</para>

<para>
    When reading the <acronym>SQL</acronym> representations of the 
    query trees in this chapter it is necessary to be able to identify
    the parts the statement is broken into when it is in the query tree
    structure. The parts of a query tree are

<variablelist>
    <varlistentry>
    <term>
        the command type
    </term>
    <listitem>
    <para>
        This is a simple value telling which command
        (<command>SELECT</command>, <command>INSERT</command>,
        <command>UPDATE</command>, <command>DELETE</command>) produced
        the query tree.
    </para>
    </listitem>
    </varlistentry>

    <varlistentry>
    <term>
        the range table
    </term>
      <indexterm><primary>range table</></>
    <listitem>
    <para>
        The range table is a list of relations that are used in the query.
        In a <command>SELECT</command> statement these are the relations given after
        the <literal>FROM</literal> key word.
    </para>

    <para>
        Every range table entry identifies a table or view and tells
        by which name it is called in the other parts of the query.
        In the query tree, the range table entries are referenced by
        number rather than by name, so here it doesn't matter if there
        are duplicate names as it would in an <acronym>SQL</acronym>
        statement. This can happen after the range tables of rules
        have been merged in. The examples in this chapter will not have
        this situation.
    </para>
    </listitem>
    </varlistentry>

    <varlistentry>
    <term>
        the result relation
    </term>
    <listitem>
    <para>
        This is an index into the range table that identifies the
        relation where the results of the query go.
    </para>

    <para>
        <command>SELECT</command> queries normally don't have a result
        relation. The special case of a <command>SELECT INTO</command> is
        mostly identical to a <command>CREATE TABLE</command> followed by a
        <literal>INSERT ... SELECT</literal> and is not discussed
        separately here.
    </para>

    <para>
        For <command>INSERT</command>, <command>UPDATE</command>, and
        <command>DELETE</command> commands, the result relation is the table
        (or view!) where the changes are to take effect.
    </para>
    </listitem>
    </varlistentry>

    <varlistentry>
    <term>
        the target list
    </term>
    <indexterm><primary>target list</></>
    <listitem>
    <para>
        The target list is a list of expressions that define the
        result of the query.  In the case of a
        <command>SELECT</command>, these expressions are the ones that
        build the final output of the query.  They correspond to the
        expressions between the key words <command>SELECT</command>
        and <command>FROM</command>.  (<literal>*</literal> is just an
        abbreviation for all the column names of a relation.  It is
        expanded by the parser into the individual columns, so the
        rule system never sees it.)
    </para>

    <para>
        <command>DELETE</command> commands don't need a target list
        because they don't produce any result. In fact, the planner will
        add a special <acronym>CTID</> entry to the empty target list, but
        this is after the rule system and will be discussed later; for the
        rule system, the target list is empty.
    </para>

    <para>
        For <command>INSERT</command> commands, the target list describes
        the new rows that should go into the result relation. It consists of the
        expressions in the <literal>VALUES</> clause or the ones from the
        <command>SELECT</command> clause in <literal>INSERT
        ... SELECT</literal>.  The first step of the rewrite process adds
        target list entries for any columns that were not assigned to by
        the original command but have defaults.  Any remaining columns (with
        neither a given value nor a default) will be filled in by the
        planner with a constant null expression.
    </para>

    <para>
        For <command>UPDATE</command> commands, the target list
        describes the new rows that should replace the old ones. In the
        rule system, it contains just the expressions from the <literal>SET
        column = expression</literal> part of the command.  The planner will handle
        missing columns by inserting expressions that copy the values from
        the old row into the new one. And it will add the special
        <acronym>CTID</> entry just as for <command>DELETE</command>, too.
    </para>

    <para>
        Every entry in the target list contains an expression that can
        be a constant value, a variable pointing to a column of one
        of the relations in the range table, a parameter, or an expression
        tree made of function calls, constants, variables, operators, etc.
    </para>
    </listitem>
    </varlistentry>

    <varlistentry>
    <term>
        the qualification
    </term>
    <listitem>
    <para>
        The query's qualification is an expression much like one of
        those contained in the target list entries. The result value of
        this expression is a Boolean that tells whether the operation
        (<command>INSERT</command>, <command>UPDATE</command>,
        <command>DELETE</command>, or <command>SELECT</command>) for the
        final result row should be executed or not. It corresponds to the <literal>WHERE</> clause
        of an <acronym>SQL</acronym> statement.
    </para>
    </listitem>
    </varlistentry>

    <varlistentry>
    <term>
        the join tree
    </term>
    <listitem>
    <para>
        The query's join tree shows the structure of the <literal>FROM</> clause.
        For a simple query like <literal>SELECT ... FROM a, b, c</literal>, the join tree is just
        a list of the <literal>FROM</> items, because we are allowed to join them in
        any order.  But when <literal>JOIN</> expressions, particularly outer joins,
        are used, we have to join in the order shown by the joins.
        In that case, the join tree shows the structure of the <literal>JOIN</> expressions.  The
        restrictions associated with particular <literal>JOIN</> clauses (from <literal>ON</> or
        <literal>USING</> expressions) are stored as qualification expressions attached
        to those join-tree nodes.  It turns out to be convenient to store
        the top-level <literal>WHERE</> expression as a qualification attached to the
        top-level join-tree item, too.  So really the join tree represents
        both the <literal>FROM</> and <literal>WHERE</> clauses of a <command>SELECT</command>.
    </para>
    </listitem>
    </varlistentry>

    <varlistentry>
    <term>
        the others
    </term>
    <listitem>
    <para>
        The other parts of the query tree like the <literal>ORDER BY</>
        clause aren't of interest here. The rule system
        substitutes some entries there while applying rules, but that
        doesn't have much to do with the fundamentals of the rule
        system.
    </para>
    </listitem>
    </varlistentry>

</variablelist>
</para>
</sect1>

<sect1 id="rules-views">
<title>Views and the Rule System</title>

<indexterm zone="rules-views">
 <primary>rule</primary>
 <secondary>and views</secondary>
</indexterm>

<indexterm zone="rules-views">
 <primary>view</>
 <secondary>implementation through rules</>
</indexterm>

<para>
    Views in <productname>PostgreSQL</productname> are implemented
    using the rule system. In fact, there is essentially no difference
    between:

<programlisting>
CREATE VIEW myview AS SELECT * FROM mytab;
</programlisting>
    
    compared against the two commands:

<programlisting>
CREATE TABLE myview (<replaceable>same column list as mytab</replaceable>);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;
</programlisting>
    
    because this is exactly what the <command>CREATE VIEW</command>
    command does internally.  This has some side effects. One of them
    is that the information about a view in the
    <productname>PostgreSQL</productname> system catalogs is exactly
    the same as it is for a table. So for the parser, there is
    absolutely no difference between a table and a view. They are the
    same thing: relations.
</para>

<sect2 id="rules-select">
<title>How <command>SELECT</command> Rules Work</title>

<indexterm zone="rules-select">
 <primary>rule</primary>
 <secondary sortas="SELECT">for SELECT</secondary>
</indexterm>

<para>
    Rules <literal>ON SELECT</> are applied to all queries as the last step, even
    if the command given is an <command>INSERT</command>,
    <command>UPDATE</command> or <command>DELETE</command>. 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
    <command>SELECT</command> rules are described first.
</para>

<para>
    Currently, there can be only one action in an <literal>ON SELECT</> rule, and it must
    be an unconditional <command>SELECT</> action that is <literal>INSTEAD</>. This restriction was
    required to make rules safe enough to open them for ordinary users, and
    it restricts <literal>ON SELECT</> rules to act like views.
</para>

<para>
    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
    <command>INSERT</command>, <command>UPDATE</command>, and
    <command>DELETE</command> 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.
</para>

<para>
For the example, we need a little <literal>min</literal> function that
returns the lower of 2 integer values. We create that as:

<programlisting>
CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
    SELECT CASE WHEN $1 &lt; $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;
</programlisting>
</para>

<para>
    The real tables we need in the first two rule system descriptions
    are these:

<programlisting>
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
);
</programlisting>

    As you can see, they represent shoe-store data.
</para>

<para>
    The views are created as:

<programlisting>
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 &gt;= rsh.slminlen_cm
       AND rsl.sl_len_cm &lt;= 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*</> &mdash; 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>
    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.

<programlisting>
SELECT * FROM shoe_ready WHERE total_avail &gt;= 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 &gt;= 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 &gt;= rsh.slminlen_cm
           AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail &gt;= 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 &gt;= rsh.slminlen_cm
           AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail &gt; 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>
</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 FROM t2 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 FROM t2 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 commits the vacuum cleaner can really remove it.
</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.
</para>

<para>
    To change this, we can define rules that modify the behavior of
    these kinds of commands. This is the topic of the next section.
</para>
</sect2>

</sect1>

<sect1 id="rules-update">
<title>Rules on <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</></title>

<indexterm zone="rules-update">
 <primary>rule</primary>
 <secondary sortas="INSERT">for INSERT</secondary>
</indexterm>

<indexterm zone="rules-update">
 <primary>rule</primary>
 <secondary sortas="UPDATE">for UPDATE</secondary>
</indexterm>

<indexterm zone="rules-update">
 <primary>rule</primary>
 <secondary sortas="DELETE">for DELETE</secondary>
</indexterm>

<para>
    Rules that are defined on <command>INSERT</>, <command>UPDATE</>,
    and <command>DELETE</> are significantly different from the view rules
    described in the previous section. First, their <command>CREATE
    RULE</command> command allows more:

    <itemizedlist>
        <listitem>
        <para>
            They are allowed to have no action.
        </para>
        </listitem>

        <listitem>
        <para>
            They can have multiple actions.
        </para>
        </listitem>

        <listitem>
        <para>
            They can be <literal>INSTEAD</> or <literal>ALSO</> (the default).
        </para>
        </listitem>

        <listitem>
        <para>
            The pseudorelations <literal>NEW</> and <literal>OLD</> become useful.
        </para>
        </listitem>

        <listitem>
        <para>
            They can have rule qualifications.
        </para>
        </listitem>
    </itemizedlist>

    Second, they don't modify the query tree in place. Instead they
    create zero or more new query trees and can throw away the
    original one.
</para>

<sect2>
<title>How Update Rules Work</title>

<para>
    Keep the syntax:

<programlisting>
CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
    TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
    DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }
</programlisting>

    in mind.
    In the following, <firstterm>update rules</> means rules that are defined
    on <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</>.
</para>

<para>
    Update rules get applied by the rule system when the result
    relation and the command type of a query tree are equal to the
    object and event given in the <command>CREATE RULE</command> command.
    For update rules, the rule system creates a list of query trees.
    Initially the query-tree list is empty.
    There can be zero (<literal>NOTHING</> key word), one, or multiple actions.
    To simplify, we will look at a rule with one action. This rule
    can have a qualification or not and it can be <literal>INSTEAD</> or
    <literal>ALSO</> (the default).
</para>

<para>
    What is a rule qualification? It is a restriction that tells
    when the actions of the rule should be done and when not. This
    qualification can only reference the pseudorelations <literal>NEW</> and/or <literal>OLD</>,
    which basically represent the relation that was given as object (but with a
    special meaning).
</para>

   <para>
    So we have three cases that produce the following query trees for
    a one-action rule.

    <variablelist>
     <varlistentry>
      <term>No qualification, with either <literal>ALSO</> or
      <literal>INSTEAD</></term>
      <listitem>
       <para>
        the query tree from the rule action with the original query
        tree's qualification added
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>Qualification given and <literal>ALSO</></term>
      <listitem>
       <para>
        the query tree from the rule action with the rule
        qualification and the original query tree's qualification
        added
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>Qualification given and <literal>INSTEAD</></term>
      <listitem>
       <para>
        the query tree from the rule action with the rule
        qualification and the original query tree's qualification; and
        the original query tree with the negated rule qualification
        added
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    Finally, if the rule is <literal>ALSO</>, the unchanged original query tree is
    added to the list. Since only qualified <literal>INSTEAD</> rules already add the
    original query tree, we end up with either one or two output query trees
    for a rule with one action.
</para>

<para>
    For <literal>ON INSERT</> rules, the original query (if not suppressed by <literal>INSTEAD</>)
    is done before any actions added by rules.  This allows the actions to
    see the inserted row(s).  But for <literal>ON UPDATE</> and <literal>ON
    DELETE</> rules, the original query is done after the actions added by rules.
    This ensures that the actions can see the to-be-updated or to-be-deleted
    rows; otherwise, the actions might do nothing because they find no rows
    matching their qualifications.
</para>

<para>
    The query trees generated from rule actions are thrown into the
    rewrite system again, and maybe more rules get applied resulting
    in more or less query trees.
    So a rule's actions must have either a different
    command type or a different result relation than the rule itself is
    on, otherwise this recursive process will end up in an infinite loop.
    (Recursive expansion of a rule will be detected and reported as an
    error.)
</para>

<para>
    The query trees found in the actions of the
    <structname>pg_rewrite</structname> system catalog are only
    templates. Since they can reference the range-table entries for
    <literal>NEW</> and <literal>OLD</>, some substitutions have to be made before they can be
    used. For any reference to <literal>NEW</>, the target list of the original
    query is searched for a corresponding entry. If found, that
    entry's expression replaces the reference. Otherwise, <literal>NEW</> means the
    same as <literal>OLD</> (for an <command>UPDATE</command>) or is replaced by
    a null value (for an <command>INSERT</command>). Any reference to <literal>OLD</> is
    replaced by a reference to the range-table entry that is the
    result relation.
</para>

<para>
    After the system is done applying update rules, it applies view rules to the
    produced query tree(s).  Views cannot insert new update actions so
    there is no need to apply update rules to the output of view rewriting.
</para>

<sect3>
<title>A First Rule Step by Step</title>

<para>
    Say we want to trace changes to the <literal>sl_avail</> column in the
    <literal>shoelace_data</literal> relation. So we set up a log table
    and a rule that conditionally writes a log entry when an
    <command>UPDATE</command> is performed on
    <literal>shoelace_data</literal>.

<programlisting>
CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail &lt;&gt; OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );
</programlisting>
</para>

<para>
    Now someone does:

<programlisting>
UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
</programlisting>

    and we look at the log table:

<programlisting>
SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who | log_when                        
---------+----------+---------+----------------------------------
 sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST
(1 row)
</programlisting>
   </para>

   <para>
    That's what we expected. What happened in the background is the following.
    The parser created the query tree:

<programlisting>
UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';
</programlisting>

    There is a rule <literal>log_shoelace</literal> that is <literal>ON UPDATE</> with the rule
    qualification expression:

<programlisting>
NEW.sl_avail &lt;&gt; OLD.sl_avail
</programlisting>

    and the action:

<programlisting>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*;
</programlisting>

    (This looks a little strange since you cannot normally write
    <literal>INSERT ... VALUES ... FROM</>.  The <literal>FROM</>
    clause here is just to indicate that there are range-table entries
    in the query tree for <literal>*NEW*</> and <literal>*OLD*</>.
    These are needed so that they can be referenced by variables in
    the <command>INSERT</command> command's query tree.)
</para>

<para>
    The rule is a qualified <literal>ALSO</> rule, so the rule system
    has to return two query trees: the modified rule action and the original
    query tree. In step 1, the range table of the original query is
    incorporated into the rule's action query tree. This results in:

<programlisting>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       <emphasis>shoelace_data shoelace_data</emphasis>;
</programlisting>

    In step 2, the rule qualification is added to it, so the result set
    is restricted to rows where <literal>sl_avail</> changes:

<programlisting>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 <emphasis>WHERE *NEW*.sl_avail &lt;&gt; *OLD*.sl_avail</emphasis>;
</programlisting>

    (This looks even stranger, since <literal>INSERT ... VALUES</> doesn't have
    a <literal>WHERE</> clause either, but the planner and executor will have no
    difficulty with it.  They need to support this same functionality
    anyway for <literal>INSERT ... SELECT</>.)
   </para>

   <para>
    In step 3, the original query tree's qualification is added,
    restricting the result set further to only the rows that would have been touched
    by the original query:

<programlisting>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE *NEW*.sl_avail &lt;&gt; *OLD*.sl_avail
   <emphasis>AND shoelace_data.sl_name = 'sl7'</emphasis>;
</programlisting>
   </para>

   <para>
    Step 4 replaces references to <literal>NEW</> by the target list entries from the
    original query tree or by the matching variable references
    from the result relation:

<programlisting>
INSERT INTO shoelace_log VALUES (
       <emphasis>shoelace_data.sl_name</emphasis>, <emphasis>6</emphasis>,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE <emphasis>6</emphasis> &lt;&gt; *OLD*.sl_avail
   AND shoelace_data.sl_name = 'sl7';
</programlisting>

   </para>

   <para>
    Step 5 changes <literal>OLD</> references into result relation references:

<programlisting>
INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE 6 &lt;&gt; <emphasis>shoelace_data.sl_avail</emphasis>
   AND shoelace_data.sl_name = 'sl7';
</programlisting>
   </para>

   <para>
    That's it.  Since the rule is <literal>ALSO</>, we also output the
    original query tree.  In short, the output from the rule system
    is a list of two query trees that correspond to these statements:

<programlisting>
INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 &lt;&gt; shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

UPDATE shoelace_data SET sl_avail = 6
 WHERE sl_name = 'sl7';
</programlisting>

    These are executed in this order, and that is exactly what
    the rule was meant to do.
   </para>

   <para>
    The substitutions and the added qualifications
    ensure that, if the original query would be, say:

<programlisting>
UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';
</programlisting>

    no log entry would get written.  In that case, the original query
    tree does not contain a target list entry for
    <literal>sl_avail</>, so <literal>NEW.sl_avail</> will get
    replaced by <literal>shoelace_data.sl_avail</>.  Thus, the extra
    command generated by the rule is:

<programlisting>
INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, <emphasis>shoelace_data.sl_avail</emphasis>,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE <emphasis>shoelace_data.sl_avail</emphasis> &lt;&gt; shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';
</programlisting>

    and that qualification will never be true.
   </para>

   <para>
    It will also work if the original query modifies multiple rows. So
    if someone issued the command:

<programlisting>
UPDATE shoelace_data SET sl_avail = 0
 WHERE sl_color = 'black';
</programlisting>

    four rows in fact get updated (<literal>sl1</>, <literal>sl2</>, <literal>sl3</>, and <literal>sl4</>).
    But <literal>sl3</> already has <literal>sl_avail = 0</>.   In this case, the original
    query trees qualification is different and that results
    in the extra query tree:

<programlisting>
INSERT INTO shoelace_log
SELECT shoelace_data.sl_name, 0,
       current_user, current_timestamp
  FROM shoelace_data
 WHERE 0 &lt;&gt; shoelace_data.sl_avail
   AND <emphasis>shoelace_data.sl_color = 'black'</emphasis>;
</programlisting>

    being generated by the rule.  This query tree will surely insert
    three new log entries. And that's absolutely correct.
</para>

<para>
    Here we can see why it is important that the original query tree
    is executed last.  If the <command>UPDATE</command> had been
    executed first, all the rows would have already been set to zero, so the
    logging <command>INSERT</command> would not find any row where
    <literal>0 &lt;&gt; shoelace_data.sl_avail</literal>.
</para>
</sect3>

</sect2>

<sect2 id="rules-update-views">
<title>Cooperation with Views</title>

<indexterm zone="rules-update-views"><primary>view</><secondary>updating</></>

<para>
    A simple way to protect view relations from the mentioned
    possibility that someone can try to run <command>INSERT</command>,
    <command>UPDATE</command>, or <command>DELETE</command> on them is
    to let those query trees get thrown away.  So we could create the rules:

<programlisting>
CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_del_protect AS ON DELETE TO shoe
    DO INSTEAD NOTHING;
</programlisting>

    If someone now tries to do any of these operations on the view
    relation <literal>shoe</literal>, the rule system will
    apply these rules. Since the rules have
    no actions and are <literal>INSTEAD</>, the resulting list of
    query trees will be empty and the whole query will become
    nothing because there is nothing left to be optimized or
    executed after the rule system is done with it.
</para>

<para>
    A more sophisticated way to use the rule system is to
    create rules that rewrite the query tree into one that
    does the right operation on the real tables. To do that
    on the <literal>shoelace</literal> view, we create
    the following rules:

<programlisting>
CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    );

CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
    DO INSTEAD
    UPDATE shoelace_data
       SET sl_name = NEW.sl_name,
           sl_avail = NEW.sl_avail,
           sl_color = NEW.sl_color,
           sl_len = NEW.sl_len,
           sl_unit = NEW.sl_unit
     WHERE sl_name = OLD.sl_name;

CREATE RULE shoelace_del AS ON DELETE TO shoelace
    DO INSTEAD
    DELETE FROM shoelace_data
     WHERE sl_name = OLD.sl_name;
</programlisting>
   </para>

   <para>
    If you want to support <literal>RETURNING</> queries on the view,
    you need to make the rules include <literal>RETURNING</> clauses that
    compute the view rows.  This is usually pretty trivial for views on a
    single table, but it's a bit tedious for join views such as
    <literal>shoelace</literal>.  An example for the insert case is:

<programlisting>
CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    )
    RETURNING
           shoelace_data.*,
           (SELECT shoelace_data.sl_len * u.un_fact
            FROM unit u WHERE shoelace_data.sl_unit = u.un_name);
</programlisting>

    Note that this one rule supports both <command>INSERT</> and
    <command>INSERT RETURNING</> queries on the view &mdash; the
    <literal>RETURNING</> clause is simply ignored for <command>INSERT</>.
   </para>

   <para>
    Now assume that once in a while, a pack of shoelaces arrives at
    the shop and a big parts list along with it.  But you don't want
    to manually update the <literal>shoelace</literal> view every
    time.  Instead we setup two little tables: one where you can
    insert the items from the part list, and one with a special
    trick. The creation commands for these are:

<programlisting>
CREATE TABLE shoelace_arrive (
    arr_name    text,
    arr_quant   integer
);

CREATE TABLE shoelace_ok (
    ok_name     text,
    ok_quant    integer
);

CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
    DO INSTEAD
    UPDATE shoelace
       SET sl_avail = sl_avail + NEW.ok_quant
     WHERE sl_name = NEW.ok_name;
</programlisting>

    Now you can fill the table <literal>shoelace_arrive</literal> with
    the data from the parts list:

<programlisting>
SELECT * FROM shoelace_arrive;

 arr_name | arr_quant
----------+-----------
 sl3      |        10
 sl6      |        20
 sl8      |        20
(3 rows)
</programlisting>

    Take a quick look at the current data:
    
<programlisting>
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      |        6 | 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>

    Now move the arrived shoelaces in:

<programlisting>
INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
</programlisting>

    and check the results:

<programlisting>
SELECT * FROM shoelace ORDER BY sl_name;

 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      |        6 | brown    |     60 | cm      |        60
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl3      |       10 | black    |     35 | inch    |      88.9
 sl8      |       21 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |       20 | brown    |    0.9 | m       |        90
(8 rows)

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who| log_when                        
---------+----------+--------+----------------------------------
 sl7     |        6 | Al     | Tue Oct 20 19:14:45 1998 MET DST
 sl3     |       10 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl6     |       20 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl8     |       21 | Al     | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)
</programlisting>
   </para>

   <para>
    It's a long way from the one <literal>INSERT ... SELECT</literal>
    to these results. And the description of the query-tree
    transformation will be the last in this chapter.  First, there is
    the parser's output:

<programlisting>
INSERT INTO shoelace_ok
SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;
</programlisting>

    Now the first rule <literal>shoelace_ok_ins</literal> is applied and turns this
    into:

<programlisting>
UPDATE shoelace
   SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace
 WHERE shoelace.sl_name = shoelace_arrive.arr_name;
</programlisting>

    and throws away the original <command>INSERT</command> on
    <literal>shoelace_ok</literal>.  This rewritten query is passed to
    the rule system again, and the second applied rule
    <literal>shoelace_upd</literal> produces:

<programlisting>
UPDATE shoelace_data
   SET sl_name = shoelace.sl_name,
       sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,
       sl_color = shoelace.sl_color,
       sl_len = shoelace.sl_len,
       sl_unit = shoelace.sl_unit
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace, shoelace *OLD*,
       shoelace *NEW*, shoelace_data shoelace_data
 WHERE shoelace.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = shoelace.sl_name;
</programlisting>

    Again it's an <literal>INSTEAD</> rule and the previous query tree is trashed.
    Note that this query still uses the view <literal>shoelace</literal>.
    But the rule system isn't finished with this step, so it continues
    and applies the <literal>_RETURN</literal> rule on it, and we get:

<programlisting>
UPDATE shoelace_data
   SET sl_name = s.sl_name,
       sl_avail = s.sl_avail + shoelace_arrive.arr_quant,
       sl_color = s.sl_color,
       sl_len = s.sl_len,
       sl_unit = s.sl_unit
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace, shoelace *OLD*,
       shoelace *NEW*, shoelace_data shoelace_data,
       shoelace *OLD*, shoelace *NEW*,
       shoelace_data s, unit u
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name;
</programlisting>

    Finally, the rule <literal>log_shoelace</literal> gets applied,
    producing the extra query tree:

<programlisting>
INSERT INTO shoelace_log
SELECT s.sl_name,
       s.sl_avail + shoelace_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace, shoelace *OLD*,
       shoelace *NEW*, shoelace_data shoelace_data,
       shoelace *OLD*, shoelace *NEW*,
       shoelace_data s, unit u,
       shoelace_data *OLD*, shoelace_data *NEW*
       shoelace_log shoelace_log
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name
   AND (s.sl_avail + shoelace_arrive.arr_quant) &lt;&gt; s.sl_avail;
</programlisting>
    
    After that the rule system runs out of rules and returns the 
    generated query trees.
   </para>

   <para>
    So we end up with two final query trees that are equivalent to the
    <acronym>SQL</acronym> statements:

<programlisting>
INSERT INTO shoelace_log
SELECT s.sl_name,
       s.sl_avail + shoelace_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,
       shoelace_data s
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name
   AND s.sl_avail + shoelace_arrive.arr_quant &lt;&gt; s.sl_avail;
           
UPDATE shoelace_data
   SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive,
       shoelace_data shoelace_data,
       shoelace_data s
 WHERE s.sl_name = shoelace_arrive.sl_name
   AND shoelace_data.sl_name = s.sl_name;
</programlisting>

    The result is that data coming from one relation inserted into another,
    changed into updates on a third, changed into updating
    a fourth plus logging that final update in a fifth
    gets reduced into two queries.
</para>

<para>
    There is a little detail that's a bit ugly. Looking at the two
    queries, it turns out that the <literal>shoelace_data</literal>
    relation appears twice in the range table where it could
    definitely be reduced to one. The planner does not handle it and
    so the execution plan for the rule systems output of the
    <command>INSERT</command> will be

<literallayout class="monospaced">
Nested Loop
  -&gt;  Merge Join
        -&gt;  Seq Scan
              -&gt;  Sort
                    -&gt;  Seq Scan on s
        -&gt;  Seq Scan
              -&gt;  Sort
                    -&gt;  Seq Scan on shoelace_arrive
  -&gt;  Seq Scan on shoelace_data
</literallayout>

    while omitting the extra range table entry would result in a

<literallayout class="monospaced">
Merge Join
  -&gt;  Seq Scan
        -&gt;  Sort
              -&gt;  Seq Scan on s
  -&gt;  Seq Scan
        -&gt;  Sort
              -&gt;  Seq Scan on shoelace_arrive
</literallayout>

    which produces exactly the same entries in the log table.  Thus,
    the rule system caused one extra scan on the table
    <literal>shoelace_data</literal> that is absolutely not
    necessary. And the same redundant scan is done once more in the
    <command>UPDATE</command>. But it was a really hard job to make
    that all possible at all.
</para>

<para>
    Now we make a final demonstration of the
    <productname>PostgreSQL</productname> rule system and its power.
    Say you add some shoelaces with extraordinary colors to your
    database:

<programlisting>
INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
</programlisting>

    We would like to make a view to check which
    <literal>shoelace</literal> entries do not fit any shoe in color.
    The view for this is:

<programlisting>
CREATE VIEW shoelace_mismatch AS
    SELECT * FROM shoelace WHERE NOT EXISTS
        (SELECT shoename FROM shoe WHERE slcolor = sl_color);
</programlisting>

    Its output is:

<programlisting>
SELECT * FROM shoelace_mismatch;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl9     |        0 | pink     |     35 | inch    |      88.9
 sl10    |     1000 | magenta  |     40 | inch    |     101.6
</programlisting>
   </para>

   <para>
    Now we want to set it up so that mismatching shoelaces that are
    not in stock are deleted from the database.
    To make it a little harder for <productname>PostgreSQL</productname>,
    we don't delete it directly. Instead we create one more view:

<programlisting>
CREATE VIEW shoelace_can_delete AS
    SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;
</programlisting>

    and do it this way:

<programlisting>
DELETE FROM shoelace WHERE EXISTS
    (SELECT * FROM shoelace_can_delete
             WHERE sl_name = shoelace.sl_name);
</programlisting>

    <foreignphrase>Voilà</foreignphrase>:

<programlisting>
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     |        6 | brown    |     60 | cm      |        60
 sl4     |        8 | black    |     40 | inch    |     101.6
 sl3     |       10 | black    |     35 | inch    |      88.9
 sl8     |       21 | brown    |     40 | inch    |     101.6
 sl10    |     1000 | magenta  |     40 | inch    |     101.6
 sl5     |        4 | brown    |      1 | m       |       100
 sl6     |       20 | brown    |    0.9 | m       |        90
(9 rows)
</programlisting>
   </para>

   <para>
    A <command>DELETE</command> on a view, with a subquery qualification that
    in total uses 4 nesting/joined views, where one of them
    itself has a subquery qualification containing a view
    and where calculated view columns are used,
    gets rewritten into 
    one single query tree that deletes the requested data
    from a real table.
</para>

<para>
    There are probably only a few situations out in the real world
    where such a construct is necessary. But it makes you feel
    comfortable that it works.
</para>
</sect2>

</sect1>

<sect1 id="rules-privileges">
<title>Rules and Privileges</title>

<indexterm zone="rules-privileges">
 <primary>privilege</primary>
 <secondary sortas="Regeln">with rules</secondary>
</indexterm>

<indexterm zone="rules-privileges">
 <primary>privilege</primary>
 <secondary sortas="Sichten">with views</secondary>
</indexterm>

<para>
    Due to rewriting of queries by the <productname>PostgreSQL</productname>
    rule system, other tables/views than those used in the original
    query get accessed. When update rules are used, this can include write access
    to tables.
</para>

<para>
    Rewrite rules don't have a separate owner. The owner of
    a relation (table or view) is automatically the owner of the
    rewrite rules that are defined for it.
    The <productname>PostgreSQL</productname> rule system changes the
    behavior of the default access control system. Relations that
    are used due to rules get checked against the
    privileges of the rule owner, not the user invoking the rule.
    This means that a user only needs the required privileges
    for the tables/views that he names explicitly in his queries.
</para>

<para>
    For example: A user has a list of phone numbers where some of
    them are private, the others are of interest for the secretary of the office.
    He can construct the following:

<programlisting>
CREATE TABLE phone_data (person text, phone text, private boolean);
CREATE VIEW phone_number AS
    SELECT person, phone FROM phone_data WHERE NOT private;
GRANT SELECT ON phone_number TO secretary;
</programlisting>
    
    Nobody except him (and the database superusers) can access the
    <literal>phone_data</> table. But because of the <command>GRANT</>,
    the secretary can run a <command>SELECT</command> on the
    <literal>phone_number</> view. The rule system will rewrite the
    <command>SELECT</command> from <literal>phone_number</> into a
    <command>SELECT</command> from <literal>phone_data</> and add the
    qualification that only entries where <literal>private</> is false
    are wanted. Since the user is the owner of
    <literal>phone_number</> and therefore the owner of the rule, the
    read access to <literal>phone_data</> is now checked against his
    privileges and the query is permitted. The check for accessing
    <literal>phone_number</> is also performed, but this is done
    against the invoking user, so nobody but the user and the
    secretary can use it.
</para>

<para>
    The privileges are checked rule by rule. So the secretary is for now the
    only one who can see the public phone numbers. But the secretary can setup
    another view and grant access to that to the public. Then, anyone
    can see the <literal>phone_number</> data through the secretary's view.
    What the secretary cannot do is to create a view that directly
    accesses <literal>phone_data</>.  (Actually he can, but it will not work since
    every access will be denied during the permission checks.)
    And as soon as the user will notice, that the secretary opened
    his <literal>phone_number</> view, he can revoke his access. Immediately, any
    access to the secretary's view would fail.
</para>

<para>
    One might think that this rule-by-rule checking is a security
    hole, but in fact it isn't.   But if it did not work this way, the secretary
    could set up a table with the same columns as <literal>phone_number</> and
    copy the data to there once per day. Then it's his own data and
    he can grant access to everyone he wants. A
    <command>GRANT</command> command means, <quote>I trust you</quote>.
    If someone you trust does the thing above, it's time to
    think it over and then use <command>REVOKE</command>.
</para>

<para>
    This mechanism also works for update rules. In the examples of
    the previous section, the owner of the tables in the example
    database could grant the privileges <literal>SELECT</>,
    <literal>INSERT</>, <literal>UPDATE</>, and <literal>DELETE</> on
    the <literal>shoelace</> view to someone else, but only
    <literal>SELECT</> on <literal>shoelace_log</>. The rule action to
    write log entries will still be executed successfully, and that
    other user could see the log entries.  But he cannot create fake
    entries, nor could he manipulate or remove existing ones.
</para>
</sect1>

<sect1 id="rules-status">
<title>Rules and Command Status</title>

<para>
    The <productname>PostgreSQL</productname> server returns a command
    status string, such as <literal>INSERT 149592 1</>, for each
    command it receives.  This is simple enough when there are no rules
    involved, but what happens when the query is rewritten by rules?
</para>

<para>
    Rules affect the command status as follows:

    <itemizedlist>
     <listitem>
      <para>
       If there is no unconditional <literal>INSTEAD</> rule for the query, then
       the originally given query will be executed, and its command
       status will be returned as usual.  (But note that if there were
       any conditional <literal>INSTEAD</> rules, the negation of their qualifications
       will have been added to the original query.  This might reduce the
       number of rows it processes, and if so the reported status will
       be affected.)
      </para>
     </listitem>

     <listitem>
      <para>
       If there is any unconditional <literal>INSTEAD</> rule for the query, then
       the original query will not be executed at all.  In this case,
       the server will return the command status for the last query
       that was inserted by an <literal>INSTEAD</> rule (conditional or
       unconditional) and is of the same command type
       (<command>INSERT</command>, <command>UPDATE</command>, or
       <command>DELETE</command>) as the original query.  If no query
       meeting those requirements is added by any rule, then the
       returned command status shows the original query type and
       zeroes for the row-count and OID fields.
      </para>
     </listitem>
    </itemizedlist>

    (This system was established in <productname>PostgreSQL</> 7.3.
    In versions before that, the command status might show different
    results when rules exist.)
</para>

<para>
    The programmer can ensure that any desired <literal>INSTEAD</> rule is the one
    that sets the command status in the second case, by giving it the
    alphabetically last rule name among the active rules, so that it
    gets applied last.
</para>
</sect1>

<sect1 id="rules-triggers">
<title>Rules versus Triggers</title>

<indexterm zone="rules-triggers">
 <primary>rule</primary>
 <secondary sortas="Trigger">compared with triggers</secondary>
</indexterm>

<indexterm zone="rules-triggers">
 <primary>trigger</primary>
 <secondary sortas="Regeln">compared with rules</secondary>
</indexterm>

<para>
    Many things that can be done using triggers can also be
    implemented using the <productname>PostgreSQL</productname>
    rule system.  One of the things that cannot be implemented by
    rules are some kinds of constraints, especially foreign keys. It is possible
    to place a qualified rule that rewrites a command to <literal>NOTHING</>
    if the value of a column does not appear in another table.
    But then the data is silently thrown away and that's
    not a good idea. If checks for valid values are required,
    and in the case of an invalid value an error message should
    be generated, it must be done by a trigger.
</para>

<para>
    On the other hand, a trigger cannot be created on views because
    there is no real data in a view relation;  however INSERT, UPDATE,
    and DELETE rules can be created on views.
</para>

<para>
    For the things that can be implemented by both, which is best
    depends on the usage of the database.
    A trigger is fired for any affected row once. A rule manipulates
    the query or generates an additional query. So if many
    rows are affected in one statement, a rule issuing one extra
    command is likely to be faster than a trigger that is
    called for every single row and must execute its operations
    many times.  However, the trigger approach is conceptually far
    simpler than the rule approach, and is easier for novices to get right.
</para>

<para>
    Here we show an example of how the choice of rules versus triggers
    plays out in one situation.  There are two tables:

<programlisting>
CREATE TABLE computer (
    hostname        text,    -- indexed
    manufacturer    text     -- indexed
);

CREATE TABLE software (
    software        text,    -- indexed
    hostname        text     -- indexed
);
</programlisting>

    Both tables have many thousands of rows and the indexes on
    <structfield>hostname</> are unique.  The rule or trigger should
    implement a constraint that deletes rows from <literal>software</>
    that reference a deleted computer.  The trigger would use this command:

<programlisting>
DELETE FROM software WHERE hostname = $1;
</programlisting>

    Since the trigger is called for each individual row deleted from
    <literal>computer</>, it can prepare and save the plan for this
    command and pass the <structfield>hostname</> value in the
    parameter.  The rule would be written as:

<programlisting>
CREATE RULE computer_del AS ON DELETE TO computer
    DO DELETE FROM software WHERE hostname = OLD.hostname;
</programlisting>
   </para>

   <para>
    Now we look at different types of deletes. In the case of a:
    
<programlisting>
DELETE FROM computer WHERE hostname = 'mypc.local.net';
</programlisting>

    the table <literal>computer</> is scanned by index (fast), and the
    command issued by the trigger would also use an index scan (also fast).
    The extra command from the rule would be:

<programlisting>
DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
                       AND software.hostname = computer.hostname;
</programlisting>

    Since there are appropriate indexes setup, the planner
    will create a plan of

<literallayout class="monospaced">
Nestloop
  -&gt;  Index Scan using comp_hostidx on computer
  -&gt;  Index Scan using soft_hostidx on software
</literallayout>

    So there would be not that much difference in speed between
    the trigger and the rule implementation.
   </para>

   <para>
    With the next delete we want to get rid of all the 2000 computers
    where the <structfield>hostname</> starts with
    <literal>old</>. There are two possible commands to do that. One
    is:

<programlisting>
DELETE FROM computer WHERE hostname &gt;= 'old'
                       AND hostname &lt;  'ole'
</programlisting>

    The command added by the rule will be:

<programlisting>
DELETE FROM software WHERE computer.hostname &gt;= 'old' AND computer.hostname &lt; 'ole'
                       AND software.hostname = computer.hostname;
</programlisting>

    with the plan

<literallayout class="monospaced">
Hash Join
  -&gt;  Seq Scan on software
  -&gt;  Hash
    -&gt;  Index Scan using comp_hostidx on computer
</literallayout>

    The other possible command is:

<programlisting>
DELETE FROM computer WHERE hostname ~ '^old';
</programlisting>

    which results in the following executing plan for the command
    added by the rule:

<literallayout class="monospaced">
Nestloop
  -&gt;  Index Scan using comp_hostidx on computer
  -&gt;  Index Scan using soft_hostidx on software
</literallayout>

    This shows, that the planner does not realize that the
    qualification for <structfield>hostname</> in
    <literal>computer</> could also be used for an index scan on
    <literal>software</> when there are multiple qualification
    expressions combined with <literal>AND</>, which is what it does
    in the regular-expression version of the command. The trigger will
    get invoked once for each of the 2000 old computers that have to be
    deleted, and that will result in one index scan over
    <literal>computer</> and 2000 index scans over
    <literal>software</>. The rule implementation will do it with two
    commands that use indexes.  And it depends on the overall size of
    the table <literal>software</> whether the rule will still be faster in the
    sequential scan situation. 2000 command executions from the trigger over the SPI
    manager take some time, even if all the index blocks will soon be in the cache.
</para>

<para>
    The last command we look at is:

<programlisting>
DELETE FROM computer WHERE manufacturer = 'bim';
</programlisting>

    Again this could result in many rows to be deleted from
    <literal>computer</>. So the trigger will again run many commands
    through the executor.  The command generated by the rule will be:

<programlisting>
DELETE FROM software WHERE computer.manufacturer = 'bim'
                       AND software.hostname = computer.hostname;
</programlisting>

    The plan for that command will again be the nested loop over two
    index scans, only using a different index on <literal>computer</>:

<programlisting>
Nestloop
  -&gt;  Index Scan using comp_manufidx on computer
  -&gt;  Index Scan using soft_hostidx on software
</programlisting>

    In any of these cases, the extra commands from the rule system
    will be more or less independent from the number of affected rows
    in a command.
</para>

<![IGNORE[
<!-- What's happening with this?  If it doesn't come back, remove this section. -->
<para>
    Another situation is cases on <command>UPDATE</command> where it depends on the
    change of an attribute if an action should be performed or
    not. The only way to
    create a rule as in the shoelace_log example is to do it with
    a rule qualification. That results in an extra query that is
    performed always, even if the attribute of interest cannot
    change at all because it does not appear in the target list
    of the initial query. When this is enabled again, it will be
    one more advantage of rules over triggers. Optimization of
    a trigger must fail by definition in this case, because the
    fact that its actions will only be done when a specific attribute
    is updated is hidden in its functionality. The definition of
    a trigger only allows to specify it on row level, so whenever a
    row is touched, the trigger must be called to make its
    decision. The rule system will know it by looking up the
    target list and will suppress the additional query completely
    if the attribute isn't touched. So the rule, qualified or not,
    will only do its scans if there ever could be something to do.
</para>
]]>

<para>
    The summary is, rules will only be significantly slower than
    triggers if their actions result in large and badly qualified
    joins, a situation where the planner fails.
</para>
</sect1>

</chapter>