codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 16.3 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 <a href="#RULES-TRIGGERS" class="id_link">#</a></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>3 Many things that can be done using triggers can also be4 implemented using the <span class="productname">PostgreSQL</span>5 rule system. One of the things that cannot be implemented by6 rules are some kinds of constraints, especially foreign keys. It is possible7 to place a qualified rule that rewrites a command to <code class="literal">NOTHING</code>8 if the value of a column does not appear in another table.9 But then the data is silently thrown away and that's10 not a good idea. If checks for valid values are required,11 and in the case of an invalid value an error message should12 be generated, it must be done by a trigger.13</p><p>14 In this chapter, we focused on using rules to update views. All of15 the update rule examples in this chapter can also be implemented16 using <code class="literal">INSTEAD OF</code> triggers on the views. Writing such17 triggers is often easier than writing rules, particularly if complex18 logic is required to perform the update.19</p><p>20 For the things that can be implemented by both, which is best21 depends on the usage of the database.22 A trigger is fired once for each affected row. A rule modifies23 the query or generates an additional query. So if many24 rows are affected in one statement, a rule issuing one extra25 command is likely to be faster than a trigger that is26 called for every single row and must re-determine what to do27 many times. However, the trigger approach is conceptually far28 simpler than the rule approach, and is easier for novices to get right.29</p><p>30 Here we show an example of how the choice of rules versus triggers31 plays out in one situation. There are two tables:32 33</p><pre class="programlisting">34CREATE TABLE computer (35 hostname text, -- indexed36 manufacturer text -- indexed37);38 39CREATE TABLE software (40 software text, -- indexed41 hostname text -- indexed42);43</pre><p>44 45 Both tables have many thousands of rows and the indexes on46 <code class="structfield">hostname</code> are unique. The rule or trigger should47 implement a constraint that deletes rows from <code class="literal">software</code>48 that reference a deleted computer. The trigger would use this command:49 50</p><pre class="programlisting">51DELETE FROM software WHERE hostname = $1;52</pre><p>53 54 Since the trigger is called for each individual row deleted from55 <code class="literal">computer</code>, it can prepare and save the plan for this56 command and pass the <code class="structfield">hostname</code> value in the57 parameter. The rule would be written as:58 59</p><pre class="programlisting">60CREATE RULE computer_del AS ON DELETE TO computer61 DO DELETE FROM software WHERE hostname = OLD.hostname;62</pre><p>63 </p><p>64 Now we look at different types of deletes. In the case of a:65 66</p><pre class="programlisting">67DELETE FROM computer WHERE hostname = 'mypc.local.net';68</pre><p>69 70 the table <code class="literal">computer</code> is scanned by index (fast), and the71 command issued by the trigger would also use an index scan (also fast).72 The extra command from the rule would be:73 74</p><pre class="programlisting">75DELETE FROM software WHERE computer.hostname = 'mypc.local.net'76 AND software.hostname = computer.hostname;77</pre><p>78 79 Since there are appropriate indexes set up, the planner80 will create a plan of81 82</p><pre class="literallayout">83Nestloop84 -> Index Scan using comp_hostidx on computer85 -> Index Scan using soft_hostidx on software86</pre><p>87 88 So there would be not that much difference in speed between89 the trigger and the rule implementation.90 </p><p>91 With the next delete we want to get rid of all the 2000 computers92 where the <code class="structfield">hostname</code> starts with93 <code class="literal">old</code>. There are two possible commands to do that. One94 is:95 96</p><pre class="programlisting">97DELETE FROM computer WHERE hostname >= 'old'98 AND hostname < 'ole'99</pre><p>100 101 The command added by the rule will be:102 103</p><pre class="programlisting">104DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'105 AND software.hostname = computer.hostname;106</pre><p>107 108 with the plan109 110</p><pre class="literallayout">111Hash Join112 -> Seq Scan on software113 -> Hash114 -> Index Scan using comp_hostidx on computer115</pre><p>116 117 The other possible command is:118 119</p><pre class="programlisting">120DELETE FROM computer WHERE hostname ~ '^old';121</pre><p>122 123 which results in the following executing plan for the command124 added by the rule:125 126</p><pre class="literallayout">127Nestloop128 -> Index Scan using comp_hostidx on computer129 -> Index Scan using soft_hostidx on software130</pre><p>131 132 This shows, that the planner does not realize that the133 qualification for <code class="structfield">hostname</code> in134 <code class="literal">computer</code> could also be used for an index scan on135 <code class="literal">software</code> when there are multiple qualification136 expressions combined with <code class="literal">AND</code>, which is what it does137 in the regular-expression version of the command. The trigger will138 get invoked once for each of the 2000 old computers that have to be139 deleted, and that will result in one index scan over140 <code class="literal">computer</code> and 2000 index scans over141 <code class="literal">software</code>. The rule implementation will do it with two142 commands that use indexes. And it depends on the overall size of143 the table <code class="literal">software</code> whether the rule will still be faster in the144 sequential scan situation. 2000 command executions from the trigger over the SPI145 manager take some time, even if all the index blocks will soon be in the cache.146</p><p>147 The last command we look at is:148 149</p><pre class="programlisting">150DELETE FROM computer WHERE manufacturer = 'bim';151</pre><p>152 153 Again this could result in many rows to be deleted from154 <code class="literal">computer</code>. So the trigger will again run many commands155 through the executor. The command generated by the rule will be:156 157</p><pre class="programlisting">158DELETE FROM software WHERE computer.manufacturer = 'bim'159 AND software.hostname = computer.hostname;160</pre><p>161 162 The plan for that command will again be the nested loop over two163 index scans, only using a different index on <code class="literal">computer</code>:164 165</p><pre class="programlisting">166Nestloop167 -> Index Scan using comp_manufidx on computer168 -> Index Scan using soft_hostidx on software169</pre><p>170 171 In any of these cases, the extra commands from the rule system172 will be more or less independent from the number of affected rows173 in a command.174</p><p>175 The summary is, rules will only be significantly slower than176 triggers if their actions result in large and badly qualified177 joins, a situation where the planner fails.178</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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 42. Procedural Languages</td></tr></table></div></body></html>