rules.sgml

关系型数据库 Postgresql 6.5.2

<Chapter Id="rules">
<Title>The <ProductName>Postgres</ProductName> Rule System</Title>

<Para>
     Production rule systems are conceptually simple, but
     there are many subtle points involved in actually using
     them. Some of these points and
     the theoretical foundations of the <ProductName>Postgres</ProductName>
     rule system can be found in
[<XRef LinkEnd="STON90b" EndTerm="STON90b">].
</Para>

<Para>
     Some other database systems define active database rules. These
     are usually stored procedures and triggers and are implemented
     in <ProductName>Postgres</ProductName> as functions and triggers.
</Para>

<Para>
     The query rewrite rule system (the "rule system" from now on) 
     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 optimizer for  execution.   It
     is  very powerful, and can be used for many things such
     as query language procedures, views, and versions.  The
     power  of  this  rule system is discussed in 
[<XRef LinkEnd="ONG90" EndTerm="ONG90">]
 as well as
[<XRef LinkEnd="STON90b" EndTerm="STON90b">].
</para>
<Sect1>
<Title>What is a Querytree?</Title>

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

<Para>
    The rule system is located between the query parser and the optimizer.
    It takes the output of the parser, one querytree, and the rewrite
    rules from the <FileName>pg_rewrite</FileName> catalog, which are
    querytrees too with some extra information, and creates zero or many
    querytrees as result. So it's 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 querytree? It is an internal representation of an
    <Acronym>SQL</Acronym> statement where the single parts that built
    it are stored separately. These querytrees are visible when starting
    the <ProductName>Postgres</ProductName> backend with debuglevel 4
    and typing queries into the interactive backend interface. The rule
    actions in the <FileName>pg_rewrite</FileName> system catalog are
    also stored as querytrees. They are not formatted like the debug
    output, but they contain exactly the same information.
</Para>

<Para>
    Reading a querytree requires some experience and it was a hard
    time when I started to work on the rule system. I can remember
    that I was standing at the coffee machine and I saw the cup
    in a targetlist, water and coffee powder in a rangetable and all
    the buttons in a qualification expression. Since 
    <Acronym>SQL</Acronym> representations of querytrees are
    sufficient to understand the rule system, this document will
    not teach how to read them. It might help to learn
    it and the naming conventions are required in the later following
    descriptions.
</Para>

<Sect2>
<Title>The Parts of a Querytree</Title>

<Para>
    When reading the <Acronym>SQL</Acronym> representations of the 
    querytrees in this document it is necessary to be able to identify
    the parts the statement is broken into when it is in the querytree
    structure. The parts of a querytree are
</Para>

<Para>
<VariableList>
    <VarListEntry>
    <Term>
        the commandtype
    </Term>
    <ListItem>
    <Para>
        This is a simple value telling which command
	(SELECT, INSERT, UPDATE, DELETE) produced the parsetree.
    </Para>
    </ListItem>
    </VarListEntry>

    <VarListEntry>
    <Term>
        the rangetable
    </Term>
    <ListItem>
    <Para>
        The rangtable is a list of relations that are used in the query.
	In a SELECT statement that are the relations given after
	the FROM keyword.
    </Para>

    <Para>
        Every rangetable entry identifies a table or view and tells
	by which name it is called in the other parts of the query.
	In the querytree the rangetable entries are referenced by
	index 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 rangetables of rules
	have been merged in. The examples in this document will not have
	this situation.
    </Para>
    </ListItem>
    </VarListEntry>

    <VarListEntry>
    <Term>
        the resultrelation
    </Term>
    <ListItem>
    <Para>
        This is an index into the rangetable that identifies the
	relation where the results of the query go.
    </Para>

    <Para>
	SELECT queries
	normally don't have a result relation. The special case
	of a SELECT INTO is mostly identical to a CREATE TABLE,
	INSERT ... SELECT sequence and is not discussed separately
	here.
    </Para>

    <Para>
        On INSERT, UPDATE and DELETE queries the resultrelation
	is the table (or view!) where the changes take effect.
    </Para>
    </ListItem>
    </VarListEntry>

    <VarListEntry>
    <Term>
        the targetlist
    </Term>
    <ListItem>
    <Para>
        The targetlist is a list of expressions that define the result
	of the query. In the case of a SELECT, the expressions are what
	builds the final output of the query. They are the expressions
	between the SELECT and the FROM keywords (* is just an
	abbreviation for all the attribute names of a relation). 
    </Para>

    <Para>
        DELETE queries don't need a targetlist because they don't
	produce any result. In fact the optimizer will add a special
	entry to the empty targetlist. But this is after the rule
	system and will be discussed later. For the rule system the
	targetlist is empty.
    </Para>

    <Para>
        In INSERT queries the targetlist describes the new rows that
	should go into the resultrelation. Missing columns of the 
	resultrelation will be added by the optimizer with a constant
	NULL expression. It is the expressions in the VALUES clause
	or the ones from the SELECT clause on INSERT ... SELECT.
    </Para>

    <Para>
        On UPDATE queries, it describes the new rows that should
	replace the old ones. Here now the optimizer will add missing
	columns by inserting expressions that put the values from the
	old rows into the new one. And it will add the special entry
	like for DELETE too. It is the expressions from the
	SET attribute = expression part of the query.
    </Para>

    <Para>
        Every entry in the targetlist contains an expression that can
	be a constant value, a variable pointing to an attribute of one
	of the relations in the rangetable, 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 queries qualification is an expression much like one of those
	contained in the targetlist entries. The result value of this
	expression is a boolean that tells if the operation
	(INSERT, UPDATE, DELETE or SELECT) for the final result row should be
	executed or not. It is the WHERE clause of an
	<Acronym>SQL</Acronym> statement.
    </Para>
    </ListItem>
    </VarListEntry>

    <VarListEntry>
    <Term>
        the others
    </Term>
    <ListItem>
    <Para>
        The other parts of the querytree like the ORDER BY 
	clause arent of interest here. The rule system
	substitutes entries there while applying rules, but that
	doesn't have much to do with the fundamentals of the rule
	system. GROUP BY is a special thing when it appears in
        a view definition and still needs to be documented.
    </Para>
    </ListItem>
    </VarListEntry>

</VariableList>
</para>
</Sect2>
</Sect1>

<Sect1>
<Title>Views and the Rule System</Title>

<Sect2>
<Title>Implementation of Views in <ProductName>Postgres</ProductName></Title>

<Para>
    Views in <ProductName>Postgres</ProductName> are implemented
    using the rule system. In fact there is absolutely no difference
    between a

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

<ProgramListing>
    CREATE TABLE myview (<Replaceable>same attribute list as for mytab</Replaceable>);
    CREATE RULE "_RETmyview" AS ON SELECT TO myview DO INSTEAD
        SELECT * FROM mytab;
</ProgramListing>
    
    because this is exactly what the CREATE VIEW command does internally.
    This has some side effects. One of them is that
    the information about a view in the <ProductName>Postgres</ProductName>
    system catalogs is exactly the same as it is for a table. So for the
    query parsers, there is absolutely no difference between
    a table and a view. They are the same thing - relations. That is the
    important one for now.
</Para>
</Sect2>

<Sect2>
<Title>How SELECT Rules Work</Title>

<Para>
    Rules ON SELECT are applied to all queries as the
    last step, even if the command
    given is an INSERT, UPDATE or DELETE. And they have different
    semantics from the others in that they modify the parsetree in
    place instead of creating a new one.
    So SELECT rules are described first.
</Para>

<Para>
    Currently, there could be only one action and it must be a
    SELECT action that is INSTEAD. This restriction was required
    to make rules safe enough to open them for ordinary users and
    it restricts rules ON SELECT to real view rules.
</Para>

<Para>
    The example for this document 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
    INSERT, UPDATE and DELETE operations so that the final result will
    be a view that behaves like a real table with some magic functionality.
    It 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>
    The database needed to play on the examples is named al_bundy.
    You'll see soon why this is the database name. And it needs the
    procedural language PL/pgSQL installed, because
    we need a little min() function returning the lower of 2
    integer values. We create that as

<ProgramListing>
    CREATE FUNCTION min(integer, integer) RETURNS integer AS
        'BEGIN
            IF $1 < $2 THEN
                RETURN $1;
            END IF;
            RETURN $2;
        END;'
    LANGUAGE 'plpgsql';
</ProgramListing>
</Para>

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

<ProgramListing>
    CREATE TABLE shoe_data (
        shoename   char(10),      -- primary key
        sh_avail   integer,       -- available # of pairs
        slcolor    char(10),      -- preferred shoelace color
        slminlen   float,         -- miminum shoelace length
        slmaxlen   float,         -- maximum shoelace length
        slunit     char(8)        -- length unit
    );

    CREATE TABLE shoelace_data (
        sl_name    char(10),      -- primary key
        sl_avail   integer,       -- available # of pairs
        sl_color   char(10),      -- shoelace color
        sl_len     float,         -- shoelace length
        sl_unit    char(8)        -- length unit
    );

    CREATE TABLE unit (
        un_name    char(8),       -- the primary key
        un_fact    float          -- factor to transform to cm
    );
</ProgramListing>

    I think most of us wear shoes and can realize that this is
    really useful data. Well there are shoes out in the world
    that don't require shoelaces, but this doesn't make Al's
    life easier and so we ignore it.
</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