Sophie: postgresql8.3-docs-8.3.8-1mdv2010.0 i586

postgresql8.3-docs-8.3.8-1mdv2010.0.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Rules versus Triggers</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.3.8 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="The Rule System"
HREF="rules.html"><LINK
REL="PREVIOUS"
TITLE="Rules and Command Status"
HREF="rules-status.html"><LINK
REL="NEXT"
TITLE="Procedural Languages"
HREF="xplang.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-09-04T05:13:45"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.3.8 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="rules-status.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="rules.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 36. The Rule System</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="rules.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="xplang.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="RULES-TRIGGERS"
>36.6. Rules versus Triggers</A
></H1
><A
NAME="AEN43064"
></A
><A
NAME="AEN43067"
></A
><P
>    Many things that can be done using triggers can also be
    implemented using the <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
    rule system.  One of the things that cannot be implemented by
    rules are some kinds of constraints, especially foreign keys. It is possible
    to place a qualified rule that rewrites a command to <TT
CLASS="LITERAL"
>NOTHING</TT
>
    if the value of a column does not appear in another table.
    But then the data is silently thrown away and that's
    not a good idea. If checks for valid values are required,
    and in the case of an invalid value an error message should
    be generated, it must be done by a trigger.</P
><P
>    On the other hand, a trigger that is fired on
    <TT
CLASS="COMMAND"
>INSERT</TT
> on a view can do the same as a rule: put
    the data somewhere else and suppress the insert in the view. But
    it cannot do the same thing on <TT
CLASS="COMMAND"
>UPDATE</TT
> or
    <TT
CLASS="COMMAND"
>DELETE</TT
>, because there is no real data in the
    view relation that could be scanned, and thus the trigger would
    never get called. Only a rule will help.</P
><P
>    For the things that can be implemented by both, which is best
    depends on the usage of the database.
    A trigger is fired for any affected row once. A rule manipulates
    the query or generates an additional query. So if many
    rows are affected in one statement, a rule issuing one extra
    command is likely to be faster than a trigger that is
    called for every single row and must execute its operations
    many times.  However, the trigger approach is conceptually far
    simpler than the rule approach, and is easier for novices to get right.</P
><P
>    Here we show an example of how the choice of rules versus triggers
    plays out in one situation.  There are two tables:

</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE computer (
    hostname        text,    -- indexed
    manufacturer    text     -- indexed
);

CREATE TABLE software (
    software        text,    -- indexed
    hostname        text     -- indexed
);</PRE
><P>

    Both tables have many thousands of rows and the indexes on
    <TT
CLASS="STRUCTFIELD"
>hostname</TT
> are unique.  The rule or trigger should
    implement a constraint that deletes rows from <TT
CLASS="LITERAL"
>software</TT
>
    that reference a deleted computer.  The trigger would use this command:

</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM software WHERE hostname = $1;</PRE
><P>

    Since the trigger is called for each individual row deleted from
    <TT
CLASS="LITERAL"
>computer</TT
>, it can prepare and save the plan for this
    command and pass the <TT
CLASS="STRUCTFIELD"
>hostname</TT
> value in the
    parameter.  The rule would be written as:

</P><PRE
CLASS="PROGRAMLISTING"
>CREATE RULE computer_del AS ON DELETE TO computer
    DO DELETE FROM software WHERE hostname = OLD.hostname;</PRE
><P>
   </P
><P
>    Now we look at different types of deletes. In the case of a:
    
</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM computer WHERE hostname = 'mypc.local.net';</PRE
><P>

    the table <TT
CLASS="LITERAL"
>computer</TT
> is scanned by index (fast), and the
    command issued by the trigger would also use an index scan (also fast).
    The extra command from the rule would be:

</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
                       AND software.hostname = computer.hostname;</PRE
><P>

    Since there are appropriate indexes setup, the planner
    will create a plan of

</P><PRE
CLASS="LITERALLAYOUT"
>Nestloop
  -&gt;  Index Scan using comp_hostidx on computer
  -&gt;  Index Scan using soft_hostidx on software</PRE
><P>

    So there would be not that much difference in speed between
    the trigger and the rule implementation.
   </P
><P
>    With the next delete we want to get rid of all the 2000 computers
    where the <TT
CLASS="STRUCTFIELD"
>hostname</TT
> starts with
    <TT
CLASS="LITERAL"
>old</TT
>. There are two possible commands to do that. One
    is:

</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM computer WHERE hostname &gt;= 'old'
                       AND hostname &lt;  'ole'</PRE
><P>

    The command added by the rule will be:

</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM software WHERE computer.hostname &gt;= 'old' AND computer.hostname &lt; 'ole'
                       AND software.hostname = computer.hostname;</PRE
><P>

    with the plan

</P><PRE
CLASS="LITERALLAYOUT"
>Hash Join
  -&gt;  Seq Scan on software
  -&gt;  Hash
    -&gt;  Index Scan using comp_hostidx on computer</PRE
><P>

    The other possible command is:

</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM computer WHERE hostname ~ '^old';</PRE
><P>

    which results in the following executing plan for the command
    added by the rule:

</P><PRE
CLASS="LITERALLAYOUT"
>Nestloop
  -&gt;  Index Scan using comp_hostidx on computer
  -&gt;  Index Scan using soft_hostidx on software</PRE
><P>

    This shows, that the planner does not realize that the
    qualification for <TT
CLASS="STRUCTFIELD"
>hostname</TT
> in
    <TT
CLASS="LITERAL"
>computer</TT
> could also be used for an index scan on
    <TT
CLASS="LITERAL"
>software</TT
> when there are multiple qualification
    expressions combined with <TT
CLASS="LITERAL"
>AND</TT
>, which is what it does
    in the regular-expression version of the command. The trigger will
    get invoked once for each of the 2000 old computers that have to be
    deleted, and that will result in one index scan over
    <TT
CLASS="LITERAL"
>computer</TT
> and 2000 index scans over
    <TT
CLASS="LITERAL"
>software</TT
>. The rule implementation will do it with two
    commands that use indexes.  And it depends on the overall size of
    the table <TT
CLASS="LITERAL"
>software</TT
> whether the rule will still be faster in the
    sequential scan situation. 2000 command executions from the trigger over the SPI
    manager take some time, even if all the index blocks will soon be in the cache.</P
><P
>    The last command we look at is:

</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM computer WHERE manufacturer = 'bim';</PRE
><P>

    Again this could result in many rows to be deleted from
    <TT
CLASS="LITERAL"
>computer</TT
>. So the trigger will again run many commands
    through the executor.  The command generated by the rule will be:

</P><PRE
CLASS="PROGRAMLISTING"
>DELETE FROM software WHERE computer.manufacturer = 'bim'
                       AND software.hostname = computer.hostname;</PRE
><P>

    The plan for that command will again be the nested loop over two
    index scans, only using a different index on <TT
CLASS="LITERAL"
>computer</TT
>:

</P><PRE
CLASS="PROGRAMLISTING"
>Nestloop
  -&gt;  Index Scan using comp_manufidx on computer
  -&gt;  Index Scan using soft_hostidx on software</PRE
><P>

    In any of these cases, the extra commands from the rule system
    will be more or less independent from the number of affected rows
    in a command.</P
><P
>    The summary is, rules will only be significantly slower than
    triggers if their actions result in large and badly qualified
    joins, a situation where the planner fails.</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="rules-status.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="xplang.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Rules and Command Status</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="rules.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Procedural Languages</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>