rules.sgml

PostgreSQL7.4.6 for Linux

   <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 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>
    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
  ->  Merge Join
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on s
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on shoelace_arrive
  ->  Seq Scan on shoelace_data
</literallayout>

    while omitting the extra range table entry would result in a

<literallayout class="monospaced">
Merge Join
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Sort
              ->  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