diff options
Diffstat (limited to 'doc/src/sgml/html/rules-triggers.html')
| -rw-r--r-- | doc/src/sgml/html/rules-triggers.html | 178 |
1 files changed, 178 insertions, 0 deletions
diff --git a/doc/src/sgml/html/rules-triggers.html b/doc/src/sgml/html/rules-triggers.html new file mode 100644 index 0000000..10e1909 --- /dev/null +++ b/doc/src/sgml/html/rules-triggers.html @@ -0,0 +1,178 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>41.7. Rules Versus Triggers</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="rules-status.html" title="41.6. Rules and Command Status" /><link rel="next" href="xplang.html" title="Chapter 42. Procedural Languages" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">41.7. Rules Versus Triggers</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-status.html" title="41.6. Rules and Command Status">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><th width="60%" align="center">Chapter 41. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xplang.html" title="Chapter 42. Procedural Languages">Next</a></td></tr></table><hr /></div><div class="sect1" id="RULES-TRIGGERS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">41.7. Rules Versus Triggers</h2></div></div></div><a id="id-1.8.6.12.2" class="indexterm"></a><a id="id-1.8.6.12.3" class="indexterm"></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 <code class="literal">NOTHING</code> + 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> + In this chapter, we focused on using rules to update views. All of + the update rule examples in this chapter can also be implemented + using <code class="literal">INSTEAD OF</code> triggers on the views. Writing such + triggers is often easier than writing rules, particularly if complex + logic is required to perform the update. +</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 once for each affected row. A rule modifies + 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 re-determine what to do + 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 + <code class="structfield">hostname</code> are unique. The rule or trigger should + implement a constraint that deletes rows from <code class="literal">software</code> + 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 + <code class="literal">computer</code>, it can prepare and save the plan for this + command and pass the <code class="structfield">hostname</code> 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 <code class="literal">computer</code> 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 set up, the planner + will create a plan of + +</p><pre class="literallayout"> +Nestloop + -> Index Scan using comp_hostidx on computer + -> 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 <code class="structfield">hostname</code> starts with + <code class="literal">old</code>. There are two possible commands to do that. One + is: + +</p><pre class="programlisting"> +DELETE FROM computer WHERE hostname >= 'old' + AND hostname < 'ole' +</pre><p> + + The command added by the rule will be: + +</p><pre class="programlisting"> +DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole' + AND software.hostname = computer.hostname; +</pre><p> + + with the plan + +</p><pre class="literallayout"> +Hash Join + -> Seq Scan on software + -> Hash + -> 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 + -> Index Scan using comp_hostidx on computer + -> Index Scan using soft_hostidx on software +</pre><p> + + This shows, that the planner does not realize that the + qualification for <code class="structfield">hostname</code> in + <code class="literal">computer</code> could also be used for an index scan on + <code class="literal">software</code> when there are multiple qualification + expressions combined with <code class="literal">AND</code>, 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 + <code class="literal">computer</code> and 2000 index scans over + <code class="literal">software</code>. The rule implementation will do it with two + commands that use indexes. And it depends on the overall size of + the table <code class="literal">software</code> 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 + <code class="literal">computer</code>. 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 <code class="literal">computer</code>: + +</p><pre class="programlisting"> +Nestloop + -> Index Scan using comp_manufidx on computer + -> 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 /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules-status.html" title="41.6. Rules and Command Status">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xplang.html" title="Chapter 42. Procedural Languages">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.6. Rules and Command Status </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 42. Procedural Languages</td></tr></table></div></body></html>
\ No newline at end of file |