Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
rules-update.html750 linesDownload Raw Back to html
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.4. Rules on INSERT, UPDATE, and DELETE</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-materializedviews.html" title="41.3. Materialized Views" /><link rel="next" href="rules-privileges.html" title="41.5. Rules and Privileges" /></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.4. Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-materializedviews.html" title="41.3. Materialized Views">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="rules-privileges.html" title="41.5. Rules and Privileges">Next</a></td></tr></table><hr /></div><div class="sect1" id="RULES-UPDATE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">41.4. Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code> <a href="#RULES-UPDATE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="rules-update.html#RULES-UPDATE-HOW">41.4.1. How Update Rules Work</a></span></dt><dt><span class="sect2"><a href="rules-update.html#RULES-UPDATE-VIEWS">41.4.2. Cooperation with Views</a></span></dt></dl></div><a id="id-1.8.6.9.2" class="indexterm"></a><a id="id-1.8.6.9.3" class="indexterm"></a><a id="id-1.8.6.9.4" class="indexterm"></a><p>3    Rules that are defined on <code class="command">INSERT</code>, <code class="command">UPDATE</code>,4    and <code class="command">DELETE</code> are significantly different from the view rules5    described in the previous sections. First, their <code class="command">CREATE6    RULE</code> command allows more:7 8    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>9            They are allowed to have no action.10        </p></li><li class="listitem"><p>11            They can have multiple actions.12        </p></li><li class="listitem"><p>13            They can be <code class="literal">INSTEAD</code> or <code class="literal">ALSO</code> (the default).14        </p></li><li class="listitem"><p>15            The pseudorelations <code class="literal">NEW</code> and <code class="literal">OLD</code> become useful.16        </p></li><li class="listitem"><p>17            They can have rule qualifications.18        </p></li></ul></div><p>19 20    Second, they don't modify the query tree in place. Instead they21    create zero or more new query trees and can throw away the22    original one.23</p><div class="caution"><h3 class="title">Caution</h3><p>24  In many cases, tasks that could be performed by rules25  on <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> are better done26  with triggers.  Triggers are notationally a bit more complicated, but their27  semantics are much simpler to understand.  Rules tend to have surprising28  results when the original query contains volatile functions: volatile29  functions may get executed more times than expected in the process of30  carrying out the rules.31 </p><p>32  Also, there are some cases that are not supported by these types of rules at33  all, notably including <code class="literal">WITH</code> clauses in the original query and34  multiple-assignment sub-<code class="literal">SELECT</code>s in the <code class="literal">SET</code> list35  of <code class="command">UPDATE</code> queries.  This is because copying these constructs36  into a rule query would result in multiple evaluations of the sub-query,37  contrary to the express intent of the query's author.38 </p></div><div class="sect2" id="RULES-UPDATE-HOW"><div class="titlepage"><div><div><h3 class="title">41.4.1. How Update Rules Work <a href="#RULES-UPDATE-HOW" class="id_link">#</a></h3></div></div></div><p>39    Keep the syntax:40 41</p><pre class="programlisting">42CREATE [ OR REPLACE ] RULE <em class="replaceable"><code>name</code></em> AS ON <em class="replaceable"><code>event</code></em>43    TO <em class="replaceable"><code>table</code></em> [ WHERE <em class="replaceable"><code>condition</code></em> ]44    DO [ ALSO | INSTEAD ] { NOTHING | <em class="replaceable"><code>command</code></em> | ( <em class="replaceable"><code>command</code></em> ; <em class="replaceable"><code>command</code></em> ... ) }45</pre><p>46 47    in mind.48    In the following, <em class="firstterm">update rules</em> means rules that are defined49    on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>.50</p><p>51    Update rules get applied by the rule system when the result52    relation and the command type of a query tree are equal to the53    object and event given in the <code class="command">CREATE RULE</code> command.54    For update rules, the rule system creates a list of query trees.55    Initially the query-tree list is empty.56    There can be zero (<code class="literal">NOTHING</code> key word), one, or multiple actions.57    To simplify, we will look at a rule with one action. This rule58    can have a qualification or not and it can be <code class="literal">INSTEAD</code> or59    <code class="literal">ALSO</code> (the default).60</p><p>61    What is a rule qualification? It is a restriction that tells62    when the actions of the rule should be done and when not. This63    qualification can only reference the pseudorelations <code class="literal">NEW</code> and/or <code class="literal">OLD</code>,64    which basically represent the relation that was given as object (but with a65    special meaning).66</p><p>67    So we have three cases that produce the following query trees for68    a one-action rule.69 70    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">No qualification, with either <code class="literal">ALSO</code> or71      <code class="literal">INSTEAD</code></span></dt><dd><p>72        the query tree from the rule action with the original query73        tree's qualification added74       </p></dd><dt><span class="term">Qualification given and <code class="literal">ALSO</code></span></dt><dd><p>75        the query tree from the rule action with the rule76        qualification and the original query tree's qualification77        added78       </p></dd><dt><span class="term">Qualification given and <code class="literal">INSTEAD</code></span></dt><dd><p>79        the query tree from the rule action with the rule80        qualification and the original query tree's qualification; and81        the original query tree with the negated rule qualification82        added83       </p></dd></dl></div><p>84 85    Finally, if the rule is <code class="literal">ALSO</code>, the unchanged original query tree is86    added to the list. Since only qualified <code class="literal">INSTEAD</code> rules already add the87    original query tree, we end up with either one or two output query trees88    for a rule with one action.89</p><p>90    For <code class="literal">ON INSERT</code> rules, the original query (if not suppressed by <code class="literal">INSTEAD</code>)91    is done before any actions added by rules.  This allows the actions to92    see the inserted row(s).  But for <code class="literal">ON UPDATE</code> and <code class="literal">ON93    DELETE</code> rules, the original query is done after the actions added by rules.94    This ensures that the actions can see the to-be-updated or to-be-deleted95    rows; otherwise, the actions might do nothing because they find no rows96    matching their qualifications.97</p><p>98    The query trees generated from rule actions are thrown into the99    rewrite system again, and maybe more rules get applied resulting100    in additional or fewer query trees.101    So a rule's actions must have either a different102    command type or a different result relation than the rule itself is103    on, otherwise this recursive process will end up in an infinite loop.104    (Recursive expansion of a rule will be detected and reported as an105    error.)106</p><p>107    The query trees found in the actions of the108    <code class="structname">pg_rewrite</code> system catalog are only109    templates. Since they can reference the range-table entries for110    <code class="literal">NEW</code> and <code class="literal">OLD</code>, some substitutions have to be made before they can be111    used. For any reference to <code class="literal">NEW</code>, the target list of the original112    query is searched for a corresponding entry. If found, that113    entry's expression replaces the reference. Otherwise, <code class="literal">NEW</code> means the114    same as <code class="literal">OLD</code> (for an <code class="command">UPDATE</code>) or is replaced by115    a null value (for an <code class="command">INSERT</code>). Any reference to <code class="literal">OLD</code> is116    replaced by a reference to the range-table entry that is the117    result relation.118</p><p>119    After the system is done applying update rules, it applies view rules to the120    produced query tree(s).  Views cannot insert new update actions so121    there is no need to apply update rules to the output of view rewriting.122</p><div class="sect3" id="RULES-UPDATE-HOW-FIRST"><div class="titlepage"><div><div><h4 class="title">41.4.1.1. A First Rule Step by Step <a href="#RULES-UPDATE-HOW-FIRST" class="id_link">#</a></h4></div></div></div><p>123    Say we want to trace changes to the <code class="literal">sl_avail</code> column in the124    <code class="literal">shoelace_data</code> relation. So we set up a log table125    and a rule that conditionally writes a log entry when an126    <code class="command">UPDATE</code> is performed on127    <code class="literal">shoelace_data</code>.128 129</p><pre class="programlisting">130CREATE TABLE shoelace_log (131    sl_name    text,          -- shoelace changed132    sl_avail   integer,       -- new available value133    log_who    text,          -- who did it134    log_when   timestamp      -- when135);136 137CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data138    WHERE NEW.sl_avail &lt;&gt; OLD.sl_avail139    DO INSERT INTO shoelace_log VALUES (140                                    NEW.sl_name,141                                    NEW.sl_avail,142                                    current_user,143                                    current_timestamp144                                );145</pre><p>146</p><p>147    Now someone does:148 149</p><pre class="programlisting">150UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';151</pre><p>152 153    and we look at the log table:154 155</p><pre class="programlisting">156SELECT * FROM shoelace_log;157 158 sl_name | sl_avail | log_who | log_when159---------+----------+---------+----------------------------------160 sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST161(1 row)162</pre><p>163   </p><p>164    That's what we expected. What happened in the background is the following.165    The parser created the query tree:166 167</p><pre class="programlisting">168UPDATE shoelace_data SET sl_avail = 6169  FROM shoelace_data shoelace_data170 WHERE shoelace_data.sl_name = 'sl7';171</pre><p>172 173    There is a rule <code class="literal">log_shoelace</code> that is <code class="literal">ON UPDATE</code> with the rule174    qualification expression:175 176</p><pre class="programlisting">177NEW.sl_avail &lt;&gt; OLD.sl_avail178</pre><p>179 180    and the action:181 182</p><pre class="programlisting">183INSERT INTO shoelace_log VALUES (184       new.sl_name, new.sl_avail,185       current_user, current_timestamp )186  FROM shoelace_data new, shoelace_data old;187</pre><p>188 189    (This looks a little strange since you cannot normally write190    <code class="literal">INSERT ... VALUES ... FROM</code>.  The <code class="literal">FROM</code>191    clause here is just to indicate that there are range-table entries192    in the query tree for <code class="literal">new</code> and <code class="literal">old</code>.193    These are needed so that they can be referenced by variables in194    the <code class="command">INSERT</code> command's query tree.)195</p><p>196    The rule is a qualified <code class="literal">ALSO</code> rule, so the rule system197    has to return two query trees: the modified rule action and the original198    query tree. In step 1, the range table of the original query is199    incorporated into the rule's action query tree. This results in:200 201</p><pre class="programlisting">202INSERT INTO shoelace_log VALUES (203       new.sl_name, new.sl_avail,204       current_user, current_timestamp )205  FROM shoelace_data new, shoelace_data old,206       <span class="emphasis"><strong>shoelace_data shoelace_data</strong></span>;207</pre><p>208 209    In step 2, the rule qualification is added to it, so the result set210    is restricted to rows where <code class="literal">sl_avail</code> changes:211 212</p><pre class="programlisting">213INSERT INTO shoelace_log VALUES (214       new.sl_name, new.sl_avail,215       current_user, current_timestamp )216  FROM shoelace_data new, shoelace_data old,217       shoelace_data shoelace_data218 <span class="emphasis"><strong>WHERE new.sl_avail &lt;&gt; old.sl_avail</strong></span>;219</pre><p>220 221    (This looks even stranger, since <code class="literal">INSERT ... VALUES</code> doesn't have222    a <code class="literal">WHERE</code> clause either, but the planner and executor will have no223    difficulty with it.  They need to support this same functionality224    anyway for <code class="literal">INSERT ... SELECT</code>.)225   </p><p>226    In step 3, the original query tree's qualification is added,227    restricting the result set further to only the rows that would have been touched228    by the original query:229 230</p><pre class="programlisting">231INSERT INTO shoelace_log VALUES (232       new.sl_name, new.sl_avail,233       current_user, current_timestamp )234  FROM shoelace_data new, shoelace_data old,235       shoelace_data shoelace_data236 WHERE new.sl_avail &lt;&gt; old.sl_avail237   <span class="emphasis"><strong>AND shoelace_data.sl_name = 'sl7'</strong></span>;238</pre><p>239   </p><p>240    Step 4 replaces references to <code class="literal">NEW</code> by the target list entries from the241    original query tree or by the matching variable references242    from the result relation:243 244</p><pre class="programlisting">245INSERT INTO shoelace_log VALUES (246       <span class="emphasis"><strong>shoelace_data.sl_name</strong></span>, <span class="emphasis"><strong>6</strong></span>,247       current_user, current_timestamp )248  FROM shoelace_data new, shoelace_data old,249       shoelace_data shoelace_data250 WHERE <span class="emphasis"><strong>6</strong></span> &lt;&gt; old.sl_avail251   AND shoelace_data.sl_name = 'sl7';252</pre><p>253 254   </p><p>255    Step 5 changes <code class="literal">OLD</code> references into result relation references:256 257</p><pre class="programlisting">258INSERT INTO shoelace_log VALUES (259       shoelace_data.sl_name, 6,260       current_user, current_timestamp )261  FROM shoelace_data new, shoelace_data old,262       shoelace_data shoelace_data263 WHERE 6 &lt;&gt; <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span>264   AND shoelace_data.sl_name = 'sl7';265</pre><p>266   </p><p>267    That's it.  Since the rule is <code class="literal">ALSO</code>, we also output the268    original query tree.  In short, the output from the rule system269    is a list of two query trees that correspond to these statements:270 271</p><pre class="programlisting">272INSERT INTO shoelace_log VALUES (273       shoelace_data.sl_name, 6,274       current_user, current_timestamp )275  FROM shoelace_data276 WHERE 6 &lt;&gt; shoelace_data.sl_avail277   AND shoelace_data.sl_name = 'sl7';278 279UPDATE shoelace_data SET sl_avail = 6280 WHERE sl_name = 'sl7';281</pre><p>282 283    These are executed in this order, and that is exactly what284    the rule was meant to do.285   </p><p>286    The substitutions and the added qualifications287    ensure that, if the original query would be, say:288 289</p><pre class="programlisting">290UPDATE shoelace_data SET sl_color = 'green'291 WHERE sl_name = 'sl7';292</pre><p>293 294    no log entry would get written.  In that case, the original query295    tree does not contain a target list entry for296    <code class="literal">sl_avail</code>, so <code class="literal">NEW.sl_avail</code> will get297    replaced by <code class="literal">shoelace_data.sl_avail</code>.  Thus, the extra298    command generated by the rule is:299 300</p><pre class="programlisting">301INSERT INTO shoelace_log VALUES (302       shoelace_data.sl_name, <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span>,303       current_user, current_timestamp )304  FROM shoelace_data305 WHERE <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span> &lt;&gt; shoelace_data.sl_avail306   AND shoelace_data.sl_name = 'sl7';307</pre><p>308 309    and that qualification will never be true.310   </p><p>311    It will also work if the original query modifies multiple rows. So312    if someone issued the command:313 314</p><pre class="programlisting">315UPDATE shoelace_data SET sl_avail = 0316 WHERE sl_color = 'black';317</pre><p>318 319    four rows in fact get updated (<code class="literal">sl1</code>, <code class="literal">sl2</code>, <code class="literal">sl3</code>, and <code class="literal">sl4</code>).320    But <code class="literal">sl3</code> already has <code class="literal">sl_avail = 0</code>.   In this case, the original321    query trees qualification is different and that results322    in the extra query tree:323 324</p><pre class="programlisting">325INSERT INTO shoelace_log326SELECT shoelace_data.sl_name, 0,327       current_user, current_timestamp328  FROM shoelace_data329 WHERE 0 &lt;&gt; shoelace_data.sl_avail330   AND <span class="emphasis"><strong>shoelace_data.sl_color = 'black'</strong></span>;331</pre><p>332 333    being generated by the rule.  This query tree will surely insert334    three new log entries. And that's absolutely correct.335</p><p>336    Here we can see why it is important that the original query tree337    is executed last.  If the <code class="command">UPDATE</code> had been338    executed first, all the rows would have already been set to zero, so the339    logging <code class="command">INSERT</code> would not find any row where340    <code class="literal">0 &lt;&gt; shoelace_data.sl_avail</code>.341</p></div></div><div class="sect2" id="RULES-UPDATE-VIEWS"><div class="titlepage"><div><div><h3 class="title">41.4.2. Cooperation with Views <a href="#RULES-UPDATE-VIEWS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.6.9.8.2" class="indexterm"></a><p>342    A simple way to protect view relations from the mentioned343    possibility that someone can try to run <code class="command">INSERT</code>,344    <code class="command">UPDATE</code>, or <code class="command">DELETE</code> on them is345    to let those query trees get thrown away.  So we could create the rules:346 347</p><pre class="programlisting">348CREATE RULE shoe_ins_protect AS ON INSERT TO shoe349    DO INSTEAD NOTHING;350CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe351    DO INSTEAD NOTHING;352CREATE RULE shoe_del_protect AS ON DELETE TO shoe353    DO INSTEAD NOTHING;354</pre><p>355 356    If someone now tries to do any of these operations on the view357    relation <code class="literal">shoe</code>, the rule system will358    apply these rules. Since the rules have359    no actions and are <code class="literal">INSTEAD</code>, the resulting list of360    query trees will be empty and the whole query will become361    nothing because there is nothing left to be optimized or362    executed after the rule system is done with it.363</p><p>364    A more sophisticated way to use the rule system is to365    create rules that rewrite the query tree into one that366    does the right operation on the real tables. To do that367    on the <code class="literal">shoelace</code> view, we create368    the following rules:369 370</p><pre class="programlisting">371CREATE RULE shoelace_ins AS ON INSERT TO shoelace372    DO INSTEAD373    INSERT INTO shoelace_data VALUES (374           NEW.sl_name,375           NEW.sl_avail,376           NEW.sl_color,377           NEW.sl_len,378           NEW.sl_unit379    );380 381CREATE RULE shoelace_upd AS ON UPDATE TO shoelace382    DO INSTEAD383    UPDATE shoelace_data384       SET sl_name = NEW.sl_name,385           sl_avail = NEW.sl_avail,386           sl_color = NEW.sl_color,387           sl_len = NEW.sl_len,388           sl_unit = NEW.sl_unit389     WHERE sl_name = OLD.sl_name;390 391CREATE RULE shoelace_del AS ON DELETE TO shoelace392    DO INSTEAD393    DELETE FROM shoelace_data394     WHERE sl_name = OLD.sl_name;395</pre><p>396   </p><p>397    If you want to support <code class="literal">RETURNING</code> queries on the view,398    you need to make the rules include <code class="literal">RETURNING</code> clauses that399    compute the view rows.  This is usually pretty trivial for views on a400    single table, but it's a bit tedious for join views such as401    <code class="literal">shoelace</code>.  An example for the insert case is:402 403</p><pre class="programlisting">404CREATE RULE shoelace_ins AS ON INSERT TO shoelace405    DO INSTEAD406    INSERT INTO shoelace_data VALUES (407           NEW.sl_name,408           NEW.sl_avail,409           NEW.sl_color,410           NEW.sl_len,411           NEW.sl_unit412    )413    RETURNING414           shoelace_data.*,415           (SELECT shoelace_data.sl_len * u.un_fact416            FROM unit u WHERE shoelace_data.sl_unit = u.un_name);417</pre><p>418 419    Note that this one rule supports both <code class="command">INSERT</code> and420    <code class="command">INSERT RETURNING</code> queries on the view — the421    <code class="literal">RETURNING</code> clause is simply ignored for <code class="command">INSERT</code>.422   </p><p>423    Now assume that once in a while, a pack of shoelaces arrives at424    the shop and a big parts list along with it.  But you don't want425    to manually update the <code class="literal">shoelace</code> view every426    time.  Instead we set up two little tables: one where you can427    insert the items from the part list, and one with a special428    trick. The creation commands for these are:429 430</p><pre class="programlisting">431CREATE TABLE shoelace_arrive (432    arr_name    text,433    arr_quant   integer434);435 436CREATE TABLE shoelace_ok (437    ok_name     text,438    ok_quant    integer439);440 441CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok442    DO INSTEAD443    UPDATE shoelace444       SET sl_avail = sl_avail + NEW.ok_quant445     WHERE sl_name = NEW.ok_name;446</pre><p>447 448    Now you can fill the table <code class="literal">shoelace_arrive</code> with449    the data from the parts list:450 451</p><pre class="programlisting">452SELECT * FROM shoelace_arrive;453 454 arr_name | arr_quant455----------+-----------456 sl3      |        10457 sl6      |        20458 sl8      |        20459(3 rows)460</pre><p>461 462    Take a quick look at the current data:463 464</p><pre class="programlisting">465SELECT * FROM shoelace;466 467 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm468----------+----------+----------+--------+---------+-----------469 sl1      |        5 | black    |     80 | cm      |        80470 sl2      |        6 | black    |    100 | cm      |       100471 sl7      |        6 | brown    |     60 | cm      |        60472 sl3      |        0 | black    |     35 | inch    |      88.9473 sl4      |        8 | black    |     40 | inch    |     101.6474 sl8      |        1 | brown    |     40 | inch    |     101.6475 sl5      |        4 | brown    |      1 | m       |       100476 sl6      |        0 | brown    |    0.9 | m       |        90477(8 rows)478</pre><p>479 480    Now move the arrived shoelaces in:481 482</p><pre class="programlisting">483INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;484</pre><p>485 486    and check the results:487 488</p><pre class="programlisting">489SELECT * FROM shoelace ORDER BY sl_name;490 491 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm492----------+----------+----------+--------+---------+-----------493 sl1      |        5 | black    |     80 | cm      |        80494 sl2      |        6 | black    |    100 | cm      |       100495 sl7      |        6 | brown    |     60 | cm      |        60496 sl4      |        8 | black    |     40 | inch    |     101.6497 sl3      |       10 | black    |     35 | inch    |      88.9498 sl8      |       21 | brown    |     40 | inch    |     101.6499 sl5      |        4 | brown    |      1 | m       |       100500 sl6      |       20 | brown    |    0.9 | m       |        90501(8 rows)502 503SELECT * FROM shoelace_log;504 505 sl_name | sl_avail | log_who| log_when506---------+----------+--------+----------------------------------507 sl7     |        6 | Al     | Tue Oct 20 19:14:45 1998 MET DST508 sl3     |       10 | Al     | Tue Oct 20 19:25:16 1998 MET DST509 sl6     |       20 | Al     | Tue Oct 20 19:25:16 1998 MET DST510 sl8     |       21 | Al     | Tue Oct 20 19:25:16 1998 MET DST511(4 rows)512</pre><p>513   </p><p>514    It's a long way from the one <code class="literal">INSERT ... SELECT</code>515    to these results. And the description of the query-tree516    transformation will be the last in this chapter.  First, there is517    the parser's output:518 519</p><pre class="programlisting">520INSERT INTO shoelace_ok521SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant522  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;523</pre><p>524 525    Now the first rule <code class="literal">shoelace_ok_ins</code> is applied and turns this526    into:527 528</p><pre class="programlisting">529UPDATE shoelace530   SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant531  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,532       shoelace_ok old, shoelace_ok new,533       shoelace shoelace534 WHERE shoelace.sl_name = shoelace_arrive.arr_name;535</pre><p>536 537    and throws away the original <code class="command">INSERT</code> on538    <code class="literal">shoelace_ok</code>.  This rewritten query is passed to539    the rule system again, and the second applied rule540    <code class="literal">shoelace_upd</code> produces:541 542</p><pre class="programlisting">543UPDATE shoelace_data544   SET sl_name = shoelace.sl_name,545       sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,546       sl_color = shoelace.sl_color,547       sl_len = shoelace.sl_len,548       sl_unit = shoelace.sl_unit549  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,550       shoelace_ok old, shoelace_ok new,551       shoelace shoelace, shoelace old,552       shoelace new, shoelace_data shoelace_data553 WHERE shoelace.sl_name = shoelace_arrive.arr_name554   AND shoelace_data.sl_name = shoelace.sl_name;555</pre><p>556 557    Again it's an <code class="literal">INSTEAD</code> rule and the previous query tree is trashed.558    Note that this query still uses the view <code class="literal">shoelace</code>.559    But the rule system isn't finished with this step, so it continues560    and applies the <code class="literal">_RETURN</code> rule on it, and we get:561 562</p><pre class="programlisting">563UPDATE shoelace_data564   SET sl_name = s.sl_name,565       sl_avail = s.sl_avail + shoelace_arrive.arr_quant,566       sl_color = s.sl_color,567       sl_len = s.sl_len,568       sl_unit = s.sl_unit569  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,570       shoelace_ok old, shoelace_ok new,571       shoelace shoelace, shoelace old,572       shoelace new, shoelace_data shoelace_data,573       shoelace old, shoelace new,574       shoelace_data s, unit u575 WHERE s.sl_name = shoelace_arrive.arr_name576   AND shoelace_data.sl_name = s.sl_name;577</pre><p>578 579    Finally, the rule <code class="literal">log_shoelace</code> gets applied,580    producing the extra query tree:581 582</p><pre class="programlisting">583INSERT INTO shoelace_log584SELECT s.sl_name,585       s.sl_avail + shoelace_arrive.arr_quant,586       current_user,587       current_timestamp588  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,589       shoelace_ok old, shoelace_ok new,590       shoelace shoelace, shoelace old,591       shoelace new, shoelace_data shoelace_data,592       shoelace old, shoelace new,593       shoelace_data s, unit u,594       shoelace_data old, shoelace_data new595       shoelace_log shoelace_log596 WHERE s.sl_name = shoelace_arrive.arr_name597   AND shoelace_data.sl_name = s.sl_name598   AND (s.sl_avail + shoelace_arrive.arr_quant) &lt;&gt; s.sl_avail;599</pre><p>600 601    After that the rule system runs out of rules and returns the602    generated query trees.603   </p><p>604    So we end up with two final query trees that are equivalent to the605    <acronym class="acronym">SQL</acronym> statements:606 607</p><pre class="programlisting">608INSERT INTO shoelace_log609SELECT s.sl_name,610       s.sl_avail + shoelace_arrive.arr_quant,611       current_user,612       current_timestamp613  FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,614       shoelace_data s615 WHERE s.sl_name = shoelace_arrive.arr_name616   AND shoelace_data.sl_name = s.sl_name617   AND s.sl_avail + shoelace_arrive.arr_quant &lt;&gt; s.sl_avail;618 619UPDATE shoelace_data620   SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant621  FROM shoelace_arrive shoelace_arrive,622       shoelace_data shoelace_data,623       shoelace_data s624 WHERE s.sl_name = shoelace_arrive.sl_name625   AND shoelace_data.sl_name = s.sl_name;626</pre><p>627 628    The result is that data coming from one relation inserted into another,629    changed into updates on a third, changed into updating630    a fourth plus logging that final update in a fifth631    gets reduced into two queries.632</p><p>633    There is a little detail that's a bit ugly. Looking at the two634    queries, it turns out that the <code class="literal">shoelace_data</code>635    relation appears twice in the range table where it could636    definitely be reduced to one. The planner does not handle it and637    so the execution plan for the rule systems output of the638    <code class="command">INSERT</code> will be639 640</p><pre class="literallayout">641Nested Loop642  -&gt;  Merge Join643        -&gt;  Seq Scan644              -&gt;  Sort645                    -&gt;  Seq Scan on s646        -&gt;  Seq Scan647              -&gt;  Sort648                    -&gt;  Seq Scan on shoelace_arrive649  -&gt;  Seq Scan on shoelace_data650</pre><p>651 652    while omitting the extra range table entry would result in a653 654</p><pre class="literallayout">655Merge Join656  -&gt;  Seq Scan657        -&gt;  Sort658              -&gt;  Seq Scan on s659  -&gt;  Seq Scan660        -&gt;  Sort661              -&gt;  Seq Scan on shoelace_arrive662</pre><p>663 664    which produces exactly the same entries in the log table.  Thus,665    the rule system caused one extra scan on the table666    <code class="literal">shoelace_data</code> that is absolutely not667    necessary. And the same redundant scan is done once more in the668    <code class="command">UPDATE</code>. But it was a really hard job to make669    that all possible at all.670</p><p>671    Now we make a final demonstration of the672    <span class="productname">PostgreSQL</span> rule system and its power.673    Say you add some shoelaces with extraordinary colors to your674    database:675 676</p><pre class="programlisting">677INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);678INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);679</pre><p>680 681    We would like to make a view to check which682    <code class="literal">shoelace</code> entries do not fit any shoe in color.683    The view for this is:684 685</p><pre class="programlisting">686CREATE VIEW shoelace_mismatch AS687    SELECT * FROM shoelace WHERE NOT EXISTS688        (SELECT shoename FROM shoe WHERE slcolor = sl_color);689</pre><p>690 691    Its output is:692 693</p><pre class="programlisting">694SELECT * FROM shoelace_mismatch;695 696 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm697---------+----------+----------+--------+---------+-----------698 sl9     |        0 | pink     |     35 | inch    |      88.9699 sl10    |     1000 | magenta  |     40 | inch    |     101.6700</pre><p>701   </p><p>702    Now we want to set it up so that mismatching shoelaces that are703    not in stock are deleted from the database.704    To make it a little harder for <span class="productname">PostgreSQL</span>,705    we don't delete it directly. Instead we create one more view:706 707</p><pre class="programlisting">708CREATE VIEW shoelace_can_delete AS709    SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;710</pre><p>711 712    and do it this way:713 714</p><pre class="programlisting">715DELETE FROM shoelace WHERE EXISTS716    (SELECT * FROM shoelace_can_delete717             WHERE sl_name = shoelace.sl_name);718</pre><p>719 720    The results are:721 722</p><pre class="programlisting">723SELECT * FROM shoelace;724 725 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm726---------+----------+----------+--------+---------+-----------727 sl1     |        5 | black    |     80 | cm      |        80728 sl2     |        6 | black    |    100 | cm      |       100729 sl7     |        6 | brown    |     60 | cm      |        60730 sl4     |        8 | black    |     40 | inch    |     101.6731 sl3     |       10 | black    |     35 | inch    |      88.9732 sl8     |       21 | brown    |     40 | inch    |     101.6733 sl10    |     1000 | magenta  |     40 | inch    |     101.6734 sl5     |        4 | brown    |      1 | m       |       100735 sl6     |       20 | brown    |    0.9 | m       |        90736(9 rows)737</pre><p>738   </p><p>739    A <code class="command">DELETE</code> on a view, with a subquery qualification that740    in total uses 4 nesting/joined views, where one of them741    itself has a subquery qualification containing a view742    and where calculated view columns are used,743    gets rewritten into744    one single query tree that deletes the requested data745    from a real table.746</p><p>747    There are probably only a few situations out in the real world748    where such a construct is necessary. But it makes you feel749    comfortable that it works.750</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules-materializedviews.html" title="41.3. Materialized Views">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="rules-privileges.html" title="41.5. Rules and Privileges">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.3. Materialized Views </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"> 41.5. Rules and Privileges</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai