rules.sgml

关系型数据库 Postgresql 6.5.2

        <ItemizedList>
	    <ListItem>
	    <Para>
	        The parsetree from the rule action where the
		original parsetrees qualification has been added.
	    </Para>
	    </ListItem>
	</ItemizedList>
    </Para>
    </ListItem>

    <ListItem>
    <Para>
        Qualification given and not INSTEAD:
        <ItemizedList>
	    <ListItem>
	    <Para>
	        The parsetree from the rule action where the rule
		qualification and the original parsetrees 
		qualification have been added.
	    </Para>
	    </ListItem>
	</ItemizedList>
    </Para>
    </ListItem>

    <ListItem>
    <Para>
        Qualification given and INSTEAD:
        <ItemizedList>
	    <ListItem>
	    <Para>
	        The parsetree from the rule action where the rule
		qualification and the original parsetrees 
		qualification have been added.
	    </Para>
	    </ListItem>

	    <ListItem>
	    <Para>
	        The original parsetree where the negated rule
		qualification has been added.
	    </Para>
	    </ListItem>
	</ItemizedList>
    </Para>
    </ListItem>
    </ItemizedList>
</para>
<Para>
    Finally, if the rule is not INSTEAD, the unchanged original parsetree is
    added to the list. Since only qualified INSTEAD rules already add the
    original parsetree, we end up with a total maximum of two parsetrees
    for a rule with one action.
</Para>

<Para>
    The parsetrees generated from rule actions are thrown into the
    rewrite system again and maybe more rules get applied resulting
    in more or less parsetrees.
    So the parsetrees in the rule actions must have either another commandtype
    or another resultrelation. Otherwise this recursive process will end up in a loop.
    There is a compiled in recursion limit of currently 10 iterations.
    If after 10 iterations there are still update rules to apply the
    rule system assumes a loop over multiple rule definitions and aborts the
    transaction.
</Para>

<Para>
    The parsetrees found in the actions of the <Filename>pg_rewrite</Filename>
    system catalog are only templates. Since they can reference the
    rangetable entries for NEW and OLD, some substitutions have to be made
    before they can be used. For any reference to NEW, the targetlist of
    the original query is searched for a corresponding entry. If found,
    that entries expression is placed into the reference. Otherwise
    NEW means the same as OLD. Any reference to OLD is replaced by a
    reference to the rangetable entry which is the resultrelation.
</Para>

<Sect3>
<Title>A First Rule Step by Step</Title>

<Para>
    We want to trace changes to the sl_avail column in the
    <Filename>shoelace_data</Filename> relation. So we setup a
    log table and a rule that writes us entries every time
    and UPDATE is performed on <Filename>shoelace_data</Filename>.

<ProgramListing>
    CREATE TABLE shoelace_log (
        sl_name    char(10),      -- shoelace changed
        sl_avail   integer,       -- new available value
        log_who    name,          -- who did it
        log_when   datetime       -- when
    );

    CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
        WHERE NEW.sl_avail != OLD.sl_avail
        DO INSERT INTO shoelace_log VALUES (
                                        NEW.sl_name,
                                        NEW.sl_avail,
                                        getpgusername(),
                                        'now'::text
                                    );
</ProgramListing>

    One interesting detail is the casting of 'now' in the rules
    INSERT action to type text. Without that, the parser would see
    at CREATE RULE time, that the target type in <Filename>shoelace_log</Filename>
    is a datetime and tries to make a constant from it - with success.
    So a constant datetime value would be stored in the rule action
    and all log entries would have the time of the CREATE RULE statement.
    Not exactly what we want. The casting causes that the parser
    constructs a datetime('now'::text) from it and this will be 
    evaluated when the rule is executed.
</Para>

<Para>
    Now Al does

<ProgramListing>
    al_bundy=> UPDATE shoelace_data SET sl_avail = 6                       
    al_bundy->     WHERE sl_name = 'sl7';
</ProgramListing>

    and we look at the logtable.

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

    That's what we expected. What happened in the background is the following.
    The parser created the parsetree (this time the parts of the original
    parsetree are highlighted because the base of operations is the
    rule action for update rules).

<ProgramListing>
    <FirstTerm>UPDATE shoelace_data SET sl_avail = 6
      FROM shoelace_data shoelace_data
     WHERE bpchareq(shoelace_data.sl_name, 'sl7');</FirstTerm>
</ProgramListing>

    There is a rule 'log_shoelace' that is ON UPDATE with the rule
    qualification expression

<ProgramListing>
    int4ne(NEW.sl_avail, OLD.sl_avail)
</ProgramListing>

    and one action

<ProgramListing>
    INSERT INTO shoelace_log SELECT 
           *NEW*.sl_name, *NEW*.sl_avail,
           getpgusername(), datetime('now'::text)
      FROM shoelace_data *NEW*, shoelace_data *OLD*,
           shoelace_log shoelace_log;
</ProgramListing>

    Don't trust the output of the pg_rules system view. It specially
    handles the situation that there are only references to NEW
    and OLD in the INSERT and outputs the VALUES format of INSERT.
    In fact there is no difference between an INSERT ... VALUES
    and an INSERT ... SELECT on parsetree level. They both have
    rangetables, targetlists and maybe qualifications etc. The
    optimizer later decides, if to create an execution plan of
    type result, seqscan, indexscan, join or whatever for that
    parsetree. If there are no references to
    rangetable entries leftin the parsetree , it becomes
    a result execution plan
    (the INSERT ... VALUES version). The rule action above can
    truely result in both variants.
</Para>

<Para>
    The rule is a qualified non-INSTEAD rule, so the rule system
    has to return two parsetrees. The modified rule action and the original
    parsetree. In the first step the rangetable of the original query is
    incorporated into the rules action parsetree. This results in

<ProgramListing>
    INSERT INTO shoelace_log SELECT 
           *NEW*.sl_name, *NEW*.sl_avai,
           getpgusername(), datetime('now'::text)
      FROM <FirstTerm>shoelace_data shoelace_data</FirstTerm>, shoelace_data *NEW*,
           shoelace_data *OLD*, shoelace_log shoelace_log;
</ProgramListing>

    In step 2 the rule qualification is added to it, so the result set
    is restricted to rows where sl_avail changes.

<ProgramListing>
    INSERT INTO shoelace_log SELECT 
           *NEW*.sl_name, *NEW*.sl_avai,
           getpgusername(), datetime('now'::text)
      FROM shoelace_data shoelace_data, shoelace_data *NEW*,
           shoelace_data *OLD*, shoelace_log shoelace_log
     <FirstTerm>WHERE int4ne(*NEW*.sl_avail, *OLD*.sl_avail)</FirstTerm>;
</ProgramListing>

    In step 3 the original parsetrees qualification is added,
    restricting the resultset further to only the rows touched
    by the original parsetree.

<ProgramListing>
    INSERT INTO shoelace_log SELECT 
           *NEW*.sl_name, *NEW*.sl_avai,
           getpgusername(), datetime('now'::text)
      FROM shoelace_data shoelace_data, shoelace_data *NEW*,
           shoelace_data *OLD*, shoelace_log shoelace_log
     WHERE int4ne(*NEW*.sl_avail, *OLD*.sl_avail)
       <FirstTerm>AND bpchareq(shoelace_data.sl_name, 'sl7')</FirstTerm>;
</ProgramListing>

    Step 4 substitutes NEW references by the targetlist entries from the
    original parsetree or with the matching variable references
    from the result relation.

<ProgramListing>
    INSERT INTO shoelace_log SELECT 
           <FirstTerm>shoelace_data.sl_name</FirstTerm>, <FirstTerm>6</FirstTerm>,
           getpgusername(), datetime('now'::text)
      FROM shoelace_data shoelace_data, shoelace_data *NEW*,
           shoelace_data *OLD*, shoelace_log shoelace_log
     WHERE int4ne(<FirstTerm>6</FirstTerm>, *OLD*.sl_avail)
       AND bpchareq(shoelace_data.sl_name, 'sl7');
</ProgramListing>

    Step 5 replaces OLD references into resultrelation references.

<ProgramListing>
    INSERT INTO shoelace_log SELECT 
           shoelace_data.sl_name, 6,
           getpgusername(), datetime('now'::text)
      FROM shoelace_data shoelace_data, shoelace_data *NEW*,
           shoelace_data *OLD*, shoelace_log shoelace_log
     WHERE int4ne(6, <FirstTerm>shoelace_data.sl_avail</FirstTerm>)
       AND bpchareq(shoelace_data.sl_name, 'sl7');
</ProgramListing>

    That's it. So reduced to the max the return from the rule system
    is a list of two parsetrees that are the same as the statements:

<ProgramListing>
    INSERT INTO shoelace_log SELECT
           shoelace_data.sl_name, 6,
           getpgusername(), 'now'
      FROM shoelace_data
     WHERE 6 != 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 defines. The subtitutions and the qualifications
    added ensure, that if the original query would be an

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

    No log entry would get written because due to the fact that this
    time the original parsetree does not contain a targetlist
    entry for sl_avail, NEW.sl_avail will get replaced by
    shoelace_data.sl_avail resulting in the extra query

<ProgramListing>
    INSERT INTO shoelace_log SELECT
           shoelace_data.sl_name, <FirstTerm>shoelace_data.sl_avail</FirstTerm>,
           getpgusername(), 'now'
      FROM shoelace_data
     WHERE <FirstTerm>shoelace_data.sl_avail</FirstTerm> != shoelace_data.sl_avail
       AND shoelace_data.sl_name = 'sl7';
</ProgramListing>

    and that qualification will never be true. Since the is no
    difference on parsetree level between an INSERT ... SELECT,
    and an INSERT ... VALUES, it will also
    work if the original query modifies multiple rows. So if Al
    would issue the command

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

    four rows in fact get updated (sl1, sl2, sl3 and sl4).
    But sl3 already has sl_avail = 0. This time, the original
    parsetrees qualification is different and that results
    in the extra parsetree

<ProgramListing>
    INSERT INTO shoelace_log SELECT
           shoelace_data.sl_name, 0,
           getpgusername(), 'now'
      FROM shoelace_data
     WHERE 0 != shoelace_data.sl_avail
       AND <FirstTerm>shoelace_data.sl_color = 'black'</FirstTerm>;
</ProgramListing>

    This parsetree will surely insert three new log entries. And
    that's absolutely correct.
</Para>

<Para>
    It is important, that the original parsetree is executed last.
    The <ProductName>Postgres</ProductName> "traffic cop" does
    a command counter increment between the execution of the two
    parsetrees so the second one can see changes made by the first.
    If the UPDATE would have been executed first, all the rows
    are already set to zero, so the logging INSERT
    would not find any row where 0 != shoelace_data.sl_avail.
</Para>
</Sect3>

</Sect2>

<Sect2>
<Title>Cooperation with Views</Title>

<Para>
    A simple way to protect view relations from the mentioned
    possibility that someone can INSERT, UPDATE and DELETE
    invisible data on them is to let those parsetrees get
    thrown away. 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 Al now tries to do any of these operations on the view
    relation <Filename>shoe</Filename>, the rule system will
    apply the rules. Since the rules have
    no actions and are INSTEAD, the resulting list of
    parsetrees 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.

    <Note>
    <Title>Note</Title>
    <Para>
    This fact might irritate frontend applications because
    absolutely nothing happened on the database and thus, the
    backend will not return anything for the query. Not
    even a PGRES_EMPTY_QUERY or so will be available in libpq.
    In psql, nothing happens. This might change in the future.
    </Para>
    </Note>