rules.sgml

关系型数据库 Postgresql 6.5.2

</Para>

<Para>
    A more sophisticated way to use the rule system is to
    create rules that rewrite the parsetree into one that
    does the right operation on the real tables. To do that
    on the <Filename>shoelace</Filename> 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>

    Now there is a pack of shoelaces arriving in Al's shop and it has
    a big partlist. Al is not that good in calculating and so
    we don't want him to manually update the shoelace view.
    Instead we setup two little tables, one where he can
    insert the items from the partlist and one with a special
    trick. The create commands for anything are:

<ProgramListing>
    CREATE TABLE shoelace_arrive (
        arr_name    char(10),
        arr_quant   integer
    );

    CREATE TABLE shoelace_ok (
        ok_name     char(10),
        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 Al can sit down and do whatever until

<ProgramListing>
    al_bundy=> SELECT * FROM shoelace_arrive;
    arr_name  |arr_quant
    ----------+---------
    sl3       |       10
    sl6       |       20
    sl8       |       20
    (3 rows)
</ProgramListing>

    is exactly that what's on the part list. We take a quick look
    at the current data,
    
<ProgramListing>
    al_bundy=> 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
    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>

    move the arrived shoelaces in

<ProgramListing>
    al_bundy=> INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
</ProgramListing>

    and check the results

<ProgramListing>
    al_bundy=> 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)

    al_bundy=> 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>

    It's a long way from the one INSERT ... SELECT to these
    results. And it's description will be the last in this
    document (but not the last example :-). First there was the parsers 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 'shoelace_ok_ins' is applied and turns it
    into

<ProgramListing>
    UPDATE shoelace SET
           sl_avail = int4pl(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 bpchareq(shoelace.sl_name, showlace_arrive.arr_name);
</ProgramListing>

    and throws away the original INSERT on <Filename>shoelace_ok</Filename>.
    This rewritten query is passed to the rule system again and
    the second applied rule 'shoelace_upd' produced

<ProgramListing>
    UPDATE shoelace_data SET
           sl_name = shoelace.sl_name,
           sl_avail = int4pl(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 showlace_data
     WHERE bpchareq(shoelace.sl_name, showlace_arrive.arr_name)
       AND bpchareq(shoelace_data.sl_name, shoelace.sl_name);
</ProgramListing>

    Again it's an INSTEAD rule and the previous parsetree is trashed.
    Note that this query sill uses the view <Filename>shoelace</Filename>
    But the rule system isn't finished with this loop so it continues
    and applies the rule '_RETshoelace' on it and we get

<ProgramListing>
    UPDATE shoelace_data SET
           sl_name = s.sl_name,
           sl_avail = int4pl(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 showlace_data,
           shoelace *OLD*, shoelace *NEW*,
           shoelace_data s, unit u
     WHERE bpchareq(s.sl_name, showlace_arrive.arr_name)
       AND bpchareq(shoelace_data.sl_name, s.sl_name);
</ProgramListing>

    Again an update rule has been applied and so the wheel
    turns on and we are in rewrite round 3. This time rule
    'log_shoelace' gets applied what produces the extra
    parsetree

<ProgramListing>
    INSERT INTO shoelace_log SELECT
           s.sl_name,
           int4pl(s.sl_avail, shoelace_arrive.arr_quant),
           getpgusername(),
           datetime('now'::text)
      FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
           shoelace_ok *OLD*, shoelace_ok *NEW*,
           shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data showlace_data,
           shoelace *OLD*, shoelace *NEW*,
           shoelace_data s, unit u,
           shoelace_data *OLD*, shoelace_data *NEW*
           shoelace_log shoelace_log
     WHERE bpchareq(s.sl_name,  showlace_arrive.arr_name)
       AND bpchareq(shoelace_data.sl_name, s.sl_name);
       AND int4ne(int4pl(s.sl_avail, shoelace_arrive.arr_quant),
                                                    s.sl_avail);
</ProgramListing>
    
    After that the rule system runs out of rules and returns the 
    generated parsetrees.
    So we end up with two final parsetrees that are equal to the
    <Acronym>SQL</Acronym> statements

<ProgramListing>
    INSERT INTO shoelace_log SELECT
           s.sl_name,
           s.sl_avail + shoelace_arrive.arr_quant,
           getpgusername(),
           'now'
      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 != 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 turns out, that the <Filename>shoelace_data</Filename>
    relation appears twice in the rangetable where it could definitely
    be reduced to one. The optimizer does not handle it and so the
    execution plan for the rule systems output of the INSERT will be

<ProgramListing>
Nested Loop
  ->  Merge Join
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on s
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on shoelace_arrive
  ->  Seq Scan on shoelace_data
</ProgramListing>

    while omitting the extra rangetable entry would result in a

<ProgramListing>
Merge Join
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on shoelace_arrive
</ProgramListing>

    that totally produces the same entries in the log relation.
    Thus, the rule system caused one extra scan on the
    <Filename>shoelace_data</Filename> relation that is
    absolutely not necessary. And the same obsolete scan
    is done once more in the UPDATE. But it was a really hard
    job to make that all possible at all.
</Para>

<Para>
    A final demonstration of the <ProductName>Postgres</ProductName>
    rule system and it's power. There is a cute blonde that
    sells shoelaces. And what Al could never realize, she's not
    only cute, she's smart too - a little too smart. Thus, it
    happens from time to time that Al orders shoelaces that
    are absolutely not sellable. This time he ordered 1000 pairs
    of magenta shoelaces and since another kind is currently not
    available but he committed to buy some, he also prepared
    his database for pink ones.

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

    Since this happens often, we must lookup for shoelace entries,
    that fit for absolutely no shoe sometimes. We could do that in
    a complicated statement every time, or we can setup a view
    for it. The view for this is

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

    It's output is

<ProgramListing>
    al_bundy=> SELECT * FROM shoelace_obsolete;
    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>

    For the 1000 magenta shoelaces we must debt Al before we can
    throw 'em away, but that's another problem. The pink entry we delete.
    To make it a little harder for <ProductName>Postgres</ProductName>,
    we don't delete it directly. Instead we create one more view

<ProgramListing>
    CREATE VIEW shoelace_candelete AS
        SELECT * FROM shoelace_obsolete WHERE sl_avail = 0;
</ProgramListing>

    and do it this way:

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

    Voila:

<ProgramListing>
    al_bundy=> 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>

    A DELETE on a view, with a subselect qualification that
    in total uses 4 nesting/joined views, where one of them
    itself has a subselect qualification containing a view
    and where calculated view columns are used,