diff options
Diffstat (limited to '')
| -rw-r--r-- | doc/src/sgml/html/rules-update.html | 750 |
1 files changed, 750 insertions, 0 deletions
diff --git a/doc/src/sgml/html/rules-update.html b/doc/src/sgml/html/rules-update.html new file mode 100644 index 0000000..647d8c6 --- /dev/null +++ b/doc/src/sgml/html/rules-update.html @@ -0,0 +1,750 @@ +<?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.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.2 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> + Rules that are defined on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, + and <code class="command">DELETE</code> are significantly different from the view rules + described in the previous sections. First, their <code class="command">CREATE + RULE</code> command allows more: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + They are allowed to have no action. + </p></li><li class="listitem"><p> + They can have multiple actions. + </p></li><li class="listitem"><p> + They can be <code class="literal">INSTEAD</code> or <code class="literal">ALSO</code> (the default). + </p></li><li class="listitem"><p> + The pseudorelations <code class="literal">NEW</code> and <code class="literal">OLD</code> become useful. + </p></li><li class="listitem"><p> + They can have rule qualifications. + </p></li></ul></div><p> + + Second, they don't modify the query tree in place. Instead they + create zero or more new query trees and can throw away the + original one. +</p><div class="caution"><h3 class="title">Caution</h3><p> + In many cases, tasks that could be performed by rules + on <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> are better done + with triggers. Triggers are notationally a bit more complicated, but their + semantics are much simpler to understand. Rules tend to have surprising + results when the original query contains volatile functions: volatile + functions may get executed more times than expected in the process of + carrying out the rules. + </p><p> + Also, there are some cases that are not supported by these types of rules at + all, notably including <code class="literal">WITH</code> clauses in the original query and + multiple-assignment sub-<code class="literal">SELECT</code>s in the <code class="literal">SET</code> list + of <code class="command">UPDATE</code> queries. This is because copying these constructs + into a rule query would result in multiple evaluations of the sub-query, + contrary to the express intent of the query's author. + </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> + Keep the syntax: + +</p><pre class="programlisting"> +CREATE [ OR REPLACE ] RULE <em class="replaceable"><code>name</code></em> AS ON <em class="replaceable"><code>event</code></em> + TO <em class="replaceable"><code>table</code></em> [ WHERE <em class="replaceable"><code>condition</code></em> ] + 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> ... ) } +</pre><p> + + in mind. + In the following, <em class="firstterm">update rules</em> means rules that are defined + on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>. +</p><p> + Update rules get applied by the rule system when the result + relation and the command type of a query tree are equal to the + object and event given in the <code class="command">CREATE RULE</code> command. + For update rules, the rule system creates a list of query trees. + Initially the query-tree list is empty. + There can be zero (<code class="literal">NOTHING</code> key word), one, or multiple actions. + To simplify, we will look at a rule with one action. This rule + can have a qualification or not and it can be <code class="literal">INSTEAD</code> or + <code class="literal">ALSO</code> (the default). +</p><p> + What is a rule qualification? It is a restriction that tells + when the actions of the rule should be done and when not. This + qualification can only reference the pseudorelations <code class="literal">NEW</code> and/or <code class="literal">OLD</code>, + which basically represent the relation that was given as object (but with a + special meaning). +</p><p> + So we have three cases that produce the following query trees for + a one-action rule. + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">No qualification, with either <code class="literal">ALSO</code> or + <code class="literal">INSTEAD</code></span></dt><dd><p> + the query tree from the rule action with the original query + tree's qualification added + </p></dd><dt><span class="term">Qualification given and <code class="literal">ALSO</code></span></dt><dd><p> + the query tree from the rule action with the rule + qualification and the original query tree's qualification + added + </p></dd><dt><span class="term">Qualification given and <code class="literal">INSTEAD</code></span></dt><dd><p> + the query tree from the rule action with the rule + qualification and the original query tree's qualification; and + the original query tree with the negated rule qualification + added + </p></dd></dl></div><p> + + Finally, if the rule is <code class="literal">ALSO</code>, the unchanged original query tree is + added to the list. Since only qualified <code class="literal">INSTEAD</code> rules already add the + original query tree, we end up with either one or two output query trees + for a rule with one action. +</p><p> + For <code class="literal">ON INSERT</code> rules, the original query (if not suppressed by <code class="literal">INSTEAD</code>) + is done before any actions added by rules. This allows the actions to + see the inserted row(s). But for <code class="literal">ON UPDATE</code> and <code class="literal">ON + DELETE</code> rules, the original query is done after the actions added by rules. + This ensures that the actions can see the to-be-updated or to-be-deleted + rows; otherwise, the actions might do nothing because they find no rows + matching their qualifications. +</p><p> + The query trees generated from rule actions are thrown into the + rewrite system again, and maybe more rules get applied resulting + in additional or fewer query trees. + So a rule's actions must have either a different + command type or a different result relation than the rule itself is + on, otherwise this recursive process will end up in an infinite loop. + (Recursive expansion of a rule will be detected and reported as an + error.) +</p><p> + The query trees found in the actions of the + <code class="structname">pg_rewrite</code> system catalog are only + templates. Since they can reference the range-table entries for + <code class="literal">NEW</code> and <code class="literal">OLD</code>, some substitutions have to be made before they can be + used. For any reference to <code class="literal">NEW</code>, the target list of the original + query is searched for a corresponding entry. If found, that + entry's expression replaces the reference. Otherwise, <code class="literal">NEW</code> means the + same as <code class="literal">OLD</code> (for an <code class="command">UPDATE</code>) or is replaced by + a null value (for an <code class="command">INSERT</code>). Any reference to <code class="literal">OLD</code> is + replaced by a reference to the range-table entry that is the + result relation. +</p><p> + After the system is done applying update rules, it applies view rules to the + produced query tree(s). Views cannot insert new update actions so + there is no need to apply update rules to the output of view rewriting. +</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> + Say we want to trace changes to the <code class="literal">sl_avail</code> column in the + <code class="literal">shoelace_data</code> relation. So we set up a log table + and a rule that conditionally writes a log entry when an + <code class="command">UPDATE</code> is performed on + <code class="literal">shoelace_data</code>. + +</p><pre class="programlisting"> +CREATE TABLE shoelace_log ( + sl_name text, -- shoelace changed + sl_avail integer, -- new available value + log_who text, -- who did it + log_when timestamp -- when +); + +CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data + WHERE NEW.sl_avail <> OLD.sl_avail + DO INSERT INTO shoelace_log VALUES ( + NEW.sl_name, + NEW.sl_avail, + current_user, + current_timestamp + ); +</pre><p> +</p><p> + Now someone does: + +</p><pre class="programlisting"> +UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; +</pre><p> + + and we look at the log table: + +</p><pre class="programlisting"> +SELECT * FROM shoelace_log; + + sl_name | sl_avail | log_who | log_when +---------+----------+---------+---------------------------------- + sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST +(1 row) +</pre><p> + </p><p> + That's what we expected. What happened in the background is the following. + The parser created the query tree: + +</p><pre class="programlisting"> +UPDATE shoelace_data SET sl_avail = 6 + FROM shoelace_data shoelace_data + WHERE shoelace_data.sl_name = 'sl7'; +</pre><p> + + There is a rule <code class="literal">log_shoelace</code> that is <code class="literal">ON UPDATE</code> with the rule + qualification expression: + +</p><pre class="programlisting"> +NEW.sl_avail <> OLD.sl_avail +</pre><p> + + and the action: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + new.sl_name, new.sl_avail, + current_user, current_timestamp ) + FROM shoelace_data new, shoelace_data old; +</pre><p> + + (This looks a little strange since you cannot normally write + <code class="literal">INSERT ... VALUES ... FROM</code>. The <code class="literal">FROM</code> + clause here is just to indicate that there are range-table entries + in the query tree for <code class="literal">new</code> and <code class="literal">old</code>. + These are needed so that they can be referenced by variables in + the <code class="command">INSERT</code> command's query tree.) +</p><p> + The rule is a qualified <code class="literal">ALSO</code> rule, so the rule system + has to return two query trees: the modified rule action and the original + query tree. In step 1, the range table of the original query is + incorporated into the rule's action query tree. This results in: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + new.sl_name, new.sl_avail, + current_user, current_timestamp ) + FROM shoelace_data new, shoelace_data old, + <span class="emphasis"><strong>shoelace_data shoelace_data</strong></span>; +</pre><p> + + In step 2, the rule qualification is added to it, so the result set + is restricted to rows where <code class="literal">sl_avail</code> changes: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + new.sl_name, new.sl_avail, + current_user, current_timestamp ) + FROM shoelace_data new, shoelace_data old, + shoelace_data shoelace_data + <span class="emphasis"><strong>WHERE new.sl_avail <> old.sl_avail</strong></span>; +</pre><p> + + (This looks even stranger, since <code class="literal">INSERT ... VALUES</code> doesn't have + a <code class="literal">WHERE</code> clause either, but the planner and executor will have no + difficulty with it. They need to support this same functionality + anyway for <code class="literal">INSERT ... SELECT</code>.) + </p><p> + In step 3, the original query tree's qualification is added, + restricting the result set further to only the rows that would have been touched + by the original query: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + new.sl_name, new.sl_avail, + current_user, current_timestamp ) + FROM shoelace_data new, shoelace_data old, + shoelace_data shoelace_data + WHERE new.sl_avail <> old.sl_avail + <span class="emphasis"><strong>AND shoelace_data.sl_name = 'sl7'</strong></span>; +</pre><p> + </p><p> + Step 4 replaces references to <code class="literal">NEW</code> by the target list entries from the + original query tree or by the matching variable references + from the result relation: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + <span class="emphasis"><strong>shoelace_data.sl_name</strong></span>, <span class="emphasis"><strong>6</strong></span>, + current_user, current_timestamp ) + FROM shoelace_data new, shoelace_data old, + shoelace_data shoelace_data + WHERE <span class="emphasis"><strong>6</strong></span> <> old.sl_avail + AND shoelace_data.sl_name = 'sl7'; +</pre><p> + + </p><p> + Step 5 changes <code class="literal">OLD</code> references into result relation references: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + shoelace_data.sl_name, 6, + current_user, current_timestamp ) + FROM shoelace_data new, shoelace_data old, + shoelace_data shoelace_data + WHERE 6 <> <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span> + AND shoelace_data.sl_name = 'sl7'; +</pre><p> + </p><p> + That's it. Since the rule is <code class="literal">ALSO</code>, we also output the + original query tree. In short, the output from the rule system + is a list of two query trees that correspond to these statements: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + shoelace_data.sl_name, 6, + current_user, current_timestamp ) + FROM shoelace_data + WHERE 6 <> shoelace_data.sl_avail + AND shoelace_data.sl_name = 'sl7'; + +UPDATE shoelace_data SET sl_avail = 6 + WHERE sl_name = 'sl7'; +</pre><p> + + These are executed in this order, and that is exactly what + the rule was meant to do. + </p><p> + The substitutions and the added qualifications + ensure that, if the original query would be, say: + +</p><pre class="programlisting"> +UPDATE shoelace_data SET sl_color = 'green' + WHERE sl_name = 'sl7'; +</pre><p> + + no log entry would get written. In that case, the original query + tree does not contain a target list entry for + <code class="literal">sl_avail</code>, so <code class="literal">NEW.sl_avail</code> will get + replaced by <code class="literal">shoelace_data.sl_avail</code>. Thus, the extra + command generated by the rule is: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log VALUES ( + shoelace_data.sl_name, <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span>, + current_user, current_timestamp ) + FROM shoelace_data + WHERE <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span> <> shoelace_data.sl_avail + AND shoelace_data.sl_name = 'sl7'; +</pre><p> + + and that qualification will never be true. + </p><p> + It will also work if the original query modifies multiple rows. So + if someone issued the command: + +</p><pre class="programlisting"> +UPDATE shoelace_data SET sl_avail = 0 + WHERE sl_color = 'black'; +</pre><p> + + 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>). + But <code class="literal">sl3</code> already has <code class="literal">sl_avail = 0</code>. In this case, the original + query trees qualification is different and that results + in the extra query tree: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log +SELECT shoelace_data.sl_name, 0, + current_user, current_timestamp + FROM shoelace_data + WHERE 0 <> shoelace_data.sl_avail + AND <span class="emphasis"><strong>shoelace_data.sl_color = 'black'</strong></span>; +</pre><p> + + being generated by the rule. This query tree will surely insert + three new log entries. And that's absolutely correct. +</p><p> + Here we can see why it is important that the original query tree + is executed last. If the <code class="command">UPDATE</code> had been + executed first, all the rows would have already been set to zero, so the + logging <code class="command">INSERT</code> would not find any row where + <code class="literal">0 <> shoelace_data.sl_avail</code>. +</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> + A simple way to protect view relations from the mentioned + possibility that someone can try to run <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, or <code class="command">DELETE</code> on them is + to let those query trees get thrown away. So we could create the rules: + +</p><pre class="programlisting"> +CREATE RULE shoe_ins_protect AS ON INSERT TO shoe + DO INSTEAD NOTHING; +CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe + DO INSTEAD NOTHING; +CREATE RULE shoe_del_protect AS ON DELETE TO shoe + DO INSTEAD NOTHING; +</pre><p> + + If someone now tries to do any of these operations on the view + relation <code class="literal">shoe</code>, the rule system will + apply these rules. Since the rules have + no actions and are <code class="literal">INSTEAD</code>, the resulting list of + query trees will be empty and the whole query will become + nothing because there is nothing left to be optimized or + executed after the rule system is done with it. +</p><p> + A more sophisticated way to use the rule system is to + create rules that rewrite the query tree into one that + does the right operation on the real tables. To do that + on the <code class="literal">shoelace</code> view, we create + the following rules: + +</p><pre class="programlisting"> +CREATE RULE shoelace_ins AS ON INSERT TO shoelace + DO INSTEAD + INSERT INTO shoelace_data VALUES ( + NEW.sl_name, + NEW.sl_avail, + NEW.sl_color, + NEW.sl_len, + NEW.sl_unit + ); + +CREATE RULE shoelace_upd AS ON UPDATE TO shoelace + DO INSTEAD + UPDATE shoelace_data + SET sl_name = NEW.sl_name, + sl_avail = NEW.sl_avail, + sl_color = NEW.sl_color, + sl_len = NEW.sl_len, + sl_unit = NEW.sl_unit + WHERE sl_name = OLD.sl_name; + +CREATE RULE shoelace_del AS ON DELETE TO shoelace + DO INSTEAD + DELETE FROM shoelace_data + WHERE sl_name = OLD.sl_name; +</pre><p> + </p><p> + If you want to support <code class="literal">RETURNING</code> queries on the view, + you need to make the rules include <code class="literal">RETURNING</code> clauses that + compute the view rows. This is usually pretty trivial for views on a + single table, but it's a bit tedious for join views such as + <code class="literal">shoelace</code>. An example for the insert case is: + +</p><pre class="programlisting"> +CREATE RULE shoelace_ins AS ON INSERT TO shoelace + DO INSTEAD + INSERT INTO shoelace_data VALUES ( + NEW.sl_name, + NEW.sl_avail, + NEW.sl_color, + NEW.sl_len, + NEW.sl_unit + ) + RETURNING + shoelace_data.*, + (SELECT shoelace_data.sl_len * u.un_fact + FROM unit u WHERE shoelace_data.sl_unit = u.un_name); +</pre><p> + + Note that this one rule supports both <code class="command">INSERT</code> and + <code class="command">INSERT RETURNING</code> queries on the view — the + <code class="literal">RETURNING</code> clause is simply ignored for <code class="command">INSERT</code>. + </p><p> + Now assume that once in a while, a pack of shoelaces arrives at + the shop and a big parts list along with it. But you don't want + to manually update the <code class="literal">shoelace</code> view every + time. Instead we set up two little tables: one where you can + insert the items from the part list, and one with a special + trick. The creation commands for these are: + +</p><pre class="programlisting"> +CREATE TABLE shoelace_arrive ( + arr_name text, + arr_quant integer +); + +CREATE TABLE shoelace_ok ( + ok_name text, + ok_quant integer +); + +CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok + DO INSTEAD + UPDATE shoelace + SET sl_avail = sl_avail + NEW.ok_quant + WHERE sl_name = NEW.ok_name; +</pre><p> + + Now you can fill the table <code class="literal">shoelace_arrive</code> with + the data from the parts list: + +</p><pre class="programlisting"> +SELECT * FROM shoelace_arrive; + + arr_name | arr_quant +----------+----------- + sl3 | 10 + sl6 | 20 + sl8 | 20 +(3 rows) +</pre><p> + + Take a quick look at the current data: + +</p><pre class="programlisting"> +SELECT * FROM shoelace; + + sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm +----------+----------+----------+--------+---------+----------- + sl1 | 5 | black | 80 | cm | 80 + sl2 | 6 | black | 100 | cm | 100 + sl7 | 6 | brown | 60 | cm | 60 + sl3 | 0 | black | 35 | inch | 88.9 + sl4 | 8 | black | 40 | inch | 101.6 + sl8 | 1 | brown | 40 | inch | 101.6 + sl5 | 4 | brown | 1 | m | 100 + sl6 | 0 | brown | 0.9 | m | 90 +(8 rows) +</pre><p> + + Now move the arrived shoelaces in: + +</p><pre class="programlisting"> +INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive; +</pre><p> + + and check the results: + +</p><pre class="programlisting"> +SELECT * FROM shoelace ORDER BY sl_name; + + sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm +----------+----------+----------+--------+---------+----------- + sl1 | 5 | black | 80 | cm | 80 + sl2 | 6 | black | 100 | cm | 100 + sl7 | 6 | brown | 60 | cm | 60 + sl4 | 8 | black | 40 | inch | 101.6 + sl3 | 10 | black | 35 | inch | 88.9 + sl8 | 21 | brown | 40 | inch | 101.6 + sl5 | 4 | brown | 1 | m | 100 + sl6 | 20 | brown | 0.9 | m | 90 +(8 rows) + +SELECT * FROM shoelace_log; + + sl_name | sl_avail | log_who| log_when +---------+----------+--------+---------------------------------- + sl7 | 6 | Al | Tue Oct 20 19:14:45 1998 MET DST + sl3 | 10 | Al | Tue Oct 20 19:25:16 1998 MET DST + sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST + sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST +(4 rows) +</pre><p> + </p><p> + It's a long way from the one <code class="literal">INSERT ... SELECT</code> + to these results. And the description of the query-tree + transformation will be the last in this chapter. First, there is + the parser's output: + +</p><pre class="programlisting"> +INSERT INTO shoelace_ok +SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant + FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok; +</pre><p> + + Now the first rule <code class="literal">shoelace_ok_ins</code> is applied and turns this + into: + +</p><pre class="programlisting"> +UPDATE shoelace + SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant + FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, + shoelace_ok old, shoelace_ok new, + shoelace shoelace + WHERE shoelace.sl_name = shoelace_arrive.arr_name; +</pre><p> + + and throws away the original <code class="command">INSERT</code> on + <code class="literal">shoelace_ok</code>. This rewritten query is passed to + the rule system again, and the second applied rule + <code class="literal">shoelace_upd</code> produces: + +</p><pre class="programlisting"> +UPDATE shoelace_data + SET sl_name = shoelace.sl_name, + sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant, + sl_color = shoelace.sl_color, + sl_len = shoelace.sl_len, + sl_unit = shoelace.sl_unit + FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, + shoelace_ok old, shoelace_ok new, + shoelace shoelace, shoelace old, + shoelace new, shoelace_data shoelace_data + WHERE shoelace.sl_name = shoelace_arrive.arr_name + AND shoelace_data.sl_name = shoelace.sl_name; +</pre><p> + + Again it's an <code class="literal">INSTEAD</code> rule and the previous query tree is trashed. + Note that this query still uses the view <code class="literal">shoelace</code>. + But the rule system isn't finished with this step, so it continues + and applies the <code class="literal">_RETURN</code> rule on it, and we get: + +</p><pre class="programlisting"> +UPDATE shoelace_data + SET sl_name = s.sl_name, + sl_avail = s.sl_avail + shoelace_arrive.arr_quant, + sl_color = s.sl_color, + sl_len = s.sl_len, + sl_unit = s.sl_unit + FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, + shoelace_ok old, shoelace_ok new, + shoelace shoelace, shoelace old, + shoelace new, shoelace_data shoelace_data, + shoelace old, shoelace new, + shoelace_data s, unit u + WHERE s.sl_name = shoelace_arrive.arr_name + AND shoelace_data.sl_name = s.sl_name; +</pre><p> + + Finally, the rule <code class="literal">log_shoelace</code> gets applied, + producing the extra query tree: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log +SELECT s.sl_name, + s.sl_avail + shoelace_arrive.arr_quant, + current_user, + current_timestamp + FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, + shoelace_ok old, shoelace_ok new, + shoelace shoelace, shoelace old, + shoelace new, shoelace_data shoelace_data, + shoelace old, shoelace new, + shoelace_data s, unit u, + shoelace_data old, shoelace_data new + shoelace_log shoelace_log + WHERE s.sl_name = shoelace_arrive.arr_name + AND shoelace_data.sl_name = s.sl_name + AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail; +</pre><p> + + After that the rule system runs out of rules and returns the + generated query trees. + </p><p> + So we end up with two final query trees that are equivalent to the + <acronym class="acronym">SQL</acronym> statements: + +</p><pre class="programlisting"> +INSERT INTO shoelace_log +SELECT s.sl_name, + s.sl_avail + shoelace_arrive.arr_quant, + current_user, + current_timestamp + FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data, + shoelace_data s + WHERE s.sl_name = shoelace_arrive.arr_name + AND shoelace_data.sl_name = s.sl_name + AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail; + +UPDATE shoelace_data + SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant + FROM shoelace_arrive shoelace_arrive, + shoelace_data shoelace_data, + shoelace_data s + WHERE s.sl_name = shoelace_arrive.sl_name + AND shoelace_data.sl_name = s.sl_name; +</pre><p> + + The result is that data coming from one relation inserted into another, + changed into updates on a third, changed into updating + a fourth plus logging that final update in a fifth + gets reduced into two queries. +</p><p> + There is a little detail that's a bit ugly. Looking at the two + queries, it turns out that the <code class="literal">shoelace_data</code> + relation appears twice in the range table where it could + definitely be reduced to one. The planner does not handle it and + so the execution plan for the rule systems output of the + <code class="command">INSERT</code> will be + +</p><pre class="literallayout"> +Nested Loop + -> Merge Join + -> Seq Scan + -> Sort + -> Seq Scan on s + -> Seq Scan + -> Sort + -> Seq Scan on shoelace_arrive + -> Seq Scan on shoelace_data +</pre><p> + + while omitting the extra range table entry would result in a + +</p><pre class="literallayout"> +Merge Join + -> Seq Scan + -> Sort + -> Seq Scan on s + -> Seq Scan + -> Sort + -> Seq Scan on shoelace_arrive +</pre><p> + + which produces exactly the same entries in the log table. Thus, + the rule system caused one extra scan on the table + <code class="literal">shoelace_data</code> that is absolutely not + necessary. And the same redundant scan is done once more in the + <code class="command">UPDATE</code>. But it was a really hard job to make + that all possible at all. +</p><p> + Now we make a final demonstration of the + <span class="productname">PostgreSQL</span> rule system and its power. + Say you add some shoelaces with extraordinary colors to your + database: + +</p><pre class="programlisting"> +INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0); +INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); +</pre><p> + + We would like to make a view to check which + <code class="literal">shoelace</code> entries do not fit any shoe in color. + The view for this is: + +</p><pre class="programlisting"> +CREATE VIEW shoelace_mismatch AS + SELECT * FROM shoelace WHERE NOT EXISTS + (SELECT shoename FROM shoe WHERE slcolor = sl_color); +</pre><p> + + Its output is: + +</p><pre class="programlisting"> +SELECT * FROM shoelace_mismatch; + + sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm +---------+----------+----------+--------+---------+----------- + sl9 | 0 | pink | 35 | inch | 88.9 + sl10 | 1000 | magenta | 40 | inch | 101.6 +</pre><p> + </p><p> + Now we want to set it up so that mismatching shoelaces that are + not in stock are deleted from the database. + To make it a little harder for <span class="productname">PostgreSQL</span>, + we don't delete it directly. Instead we create one more view: + +</p><pre class="programlisting"> +CREATE VIEW shoelace_can_delete AS + SELECT * FROM shoelace_mismatch WHERE sl_avail = 0; +</pre><p> + + and do it this way: + +</p><pre class="programlisting"> +DELETE FROM shoelace WHERE EXISTS + (SELECT * FROM shoelace_can_delete + WHERE sl_name = shoelace.sl_name); +</pre><p> + + The results are: + +</p><pre class="programlisting"> +SELECT * FROM shoelace; + + sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm +---------+----------+----------+--------+---------+----------- + sl1 | 5 | black | 80 | cm | 80 + sl2 | 6 | black | 100 | cm | 100 + sl7 | 6 | brown | 60 | cm | 60 + sl4 | 8 | black | 40 | inch | 101.6 + sl3 | 10 | black | 35 | inch | 88.9 + sl8 | 21 | brown | 40 | inch | 101.6 + sl10 | 1000 | magenta | 40 | inch | 101.6 + sl5 | 4 | brown | 1 | m | 100 + sl6 | 20 | brown | 0.9 | m | 90 +(9 rows) +</pre><p> + </p><p> + A <code class="command">DELETE</code> on a view, with a subquery qualification that + in total uses 4 nesting/joined views, where one of them + itself has a subquery qualification containing a view + and where calculated view columns are used, + gets rewritten into + one single query tree that deletes the requested data + from a real table. +</p><p> + There are probably only a few situations out in the real world + where such a construct is necessary. But it makes you feel + comfortable that it works. +</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.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.5. Rules and Privileges</td></tr></table></div></body></html>
\ No newline at end of file |