rules.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

<!-- $PostgreSQL: pgsql/doc/src/sgml/rules.sgml,v 1.44 2005/10/22 14:44:35 alvherre Exp $ -->

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