Debian Codegraph @

doc/src/sgml/rules.sgml

raw
1 <!-- doc/src/sgml/rules.sgml -->
2
3 <chapter id="rules">
4 <title>The Rule System</title>
5
6 <indexterm zone="rules">
7 <primary>rule</primary>
8 </indexterm>
9
10 <para>
11 This chapter discusses the rule system in
12 <productname>PostgreSQL</productname>. Production rule systems
13 are conceptually simple, but there are many subtle points
14 involved in actually using them.
15 </para>
16
17 <para>
18 Some other database systems define active database rules, which
19 are usually stored procedures and triggers. In
20 <productname>PostgreSQL</productname>, these can be implemented
21 using functions and triggers as well.
22 </para>
23
24 <para>
25 The rule system (more precisely speaking, the query rewrite rule
26 system) is totally different from stored procedures and triggers.
27 It modifies queries to take rules into consideration, and then
28 passes the modified query to the query planner for planning and
29 execution. It is very powerful, and can be used for many things
30 such as query language procedures, views, and versions. The
31 theoretical foundations and the power of this rule system are
32 also discussed in <xref linkend="ston90b"/> and <xref
33 linkend="ong90"/>.
34 </para>
35
36 <sect1 id="querytree">
37 <title>The Query Tree</title>
38
39 <indexterm zone="querytree">
40 <primary>query tree</primary>
41 </indexterm>
42
43 <para>
44 To understand how the rule system works it is necessary to know
45 when it is invoked and what its input and results are.
46 </para>
47
48 <para>
49 The rule system is located between the parser and the planner.
50 It takes the output of the parser, one query tree, and the user-defined
51 rewrite rules, which are also
52 query trees with some extra information, and creates zero or more
53 query trees as result. So its input and output are always things
54 the parser itself could have produced and thus, anything it sees
55 is basically representable as an <acronym>SQL</acronym> statement.
56 </para>
57
58 <para>
59 Now what is a query tree? It is an internal representation of an
60 <acronym>SQL</acronym> statement where the single parts that it is
61 built from are stored separately. These query trees can be shown
62 in the server log if you set the configuration parameters
63 <varname>debug_print_parse</varname>,
64 <varname>debug_print_rewritten</varname>, or
65 <varname>debug_print_plan</varname>. The rule actions are also
66 stored as query trees, in the system catalog
67 <structname>pg_rewrite</structname>. They are not formatted like
68 the log output, but they contain exactly the same information.
69 </para>
70
71 <para>
72 Reading a raw query tree requires some experience. But since
73 <acronym>SQL</acronym> representations of query trees are
74 sufficient to understand the rule system, this chapter will not
75 teach how to read them.
76 </para>
77
78 <para>
79 When reading the <acronym>SQL</acronym> representations of the
80 query trees in this chapter it is necessary to be able to identify
81 the parts the statement is broken into when it is in the query tree
82 structure. The parts of a query tree are
83
84 <variablelist>
85 <varlistentry>
86 <term>
87 the command type
88 </term>
89 <listitem>
90 <para>
91 This is a simple value telling which command
92 (<command>SELECT</command>, <command>INSERT</command>,
93 <command>UPDATE</command>, <command>DELETE</command>) produced
94 the query tree.
95 </para>
96 </listitem>
97 </varlistentry>
98
99 <varlistentry>
100 <term>
101 the range table
102 <indexterm><primary>range table</primary></indexterm>
103 </term>
104 <listitem>
105 <para>
106 The range table is a list of relations that are used in the query.
107 In a <command>SELECT</command> statement these are the relations given after
108 the <literal>FROM</literal> key word.
109 </para>
110
111 <para>
112 Every range table entry identifies a table or view and tells
113 by which name it is called in the other parts of the query.
114 In the query tree, the range table entries are referenced by
115 number rather than by name, so here it doesn't matter if there
116 are duplicate names as it would in an <acronym>SQL</acronym>
117 statement. This can happen after the range tables of rules
118 have been merged in. The examples in this chapter will not have
119 this situation.
120 </para>
121 </listitem>
122 </varlistentry>
123
124 <varlistentry>
125 <term>
126 the result relation
127 </term>
128 <listitem>
129 <para>
130 This is an index into the range table that identifies the
131 relation where the results of the query go.
132 </para>
133
134 <para>
135 <command>SELECT</command> queries don't have a result
136 relation. (The special case of <command>SELECT INTO</command> is
137 mostly identical to <command>CREATE TABLE</command> followed by
138 <literal>INSERT ... SELECT</literal>, and is not discussed
139 separately here.)
140 </para>
141
142 <para>
143 For <command>INSERT</command>, <command>UPDATE</command>, and
144 <command>DELETE</command> commands, the result relation is the table
145 (or view!) where the changes are to take effect.
146 </para>
147 </listitem>
148 </varlistentry>
149
150 <varlistentry>
151 <term>
152 the target list
153 <indexterm><primary>target list</primary></indexterm>
154 </term>
155 <listitem>
156 <para>
157 The target list is a list of expressions that define the
158 result of the query. In the case of a
159 <command>SELECT</command>, these expressions are the ones that
160 build the final output of the query. They correspond to the
161 expressions between the key words <command>SELECT</command>
162 and <command>FROM</command>. (<literal>*</literal> is just an
163 abbreviation for all the column names of a relation. It is
164 expanded by the parser into the individual columns, so the
165 rule system never sees it.)
166 </para>
167
168 <para>
169 <command>DELETE</command> commands don't need a normal target list
170 because they don't produce any result. Instead, the planner
171 adds a special <acronym>CTID</acronym> entry to the empty target list,
172 to allow the executor to find the row to be deleted.
173 (<acronym>CTID</acronym> is added when the result relation is an ordinary
174 table. If it is a view, a whole-row variable is added instead, by
175 the rule system, as described in <xref linkend="rules-views-update"/>.)
176 </para>
177
178 <para>
179 For <command>INSERT</command> commands, the target list describes
180 the new rows that should go into the result relation. It consists of the
181 expressions in the <literal>VALUES</literal> clause or the ones from the
182 <command>SELECT</command> clause in <literal>INSERT
183 ... SELECT</literal>. The first step of the rewrite process adds
184 target list entries for any columns that were not assigned to by
185 the original command but have defaults. Any remaining columns (with
186 neither a given value nor a default) will be filled in by the
187 planner with a constant null expression.
188 </para>
189
190 <para>
191 For <command>UPDATE</command> commands, the target list
192 describes the new rows that should replace the old ones. In the
193 rule system, it contains just the expressions from the <literal>SET
194 column = expression</literal> part of the command. The planner will
195 handle missing columns by inserting expressions that copy the values
196 from the old row into the new one. Just as for <command>DELETE</command>,
197 a <acronym>CTID</acronym> or whole-row variable is added so that
198 the executor can identify the old row to be updated.
199 </para>
200
201 <para>
202 Every entry in the target list contains an expression that can
203 be a constant value, a variable pointing to a column of one
204 of the relations in the range table, a parameter, or an expression
205 tree made of function calls, constants, variables, operators, etc.
206 </para>
207 </listitem>
208 </varlistentry>
209
210 <varlistentry>
211 <term>
212 the qualification
213 </term>
214 <listitem>
215 <para>
216 The query's qualification is an expression much like one of
217 those contained in the target list entries. The result value of
218 this expression is a Boolean that tells whether the operation
219 (<command>INSERT</command>, <command>UPDATE</command>,
220 <command>DELETE</command>, or <command>SELECT</command>) for the
221 final result row should be executed or not. It corresponds to the <literal>WHERE</literal> clause
222 of an <acronym>SQL</acronym> statement.
223 </para>
224 </listitem>
225 </varlistentry>
226
227 <varlistentry>
228 <term>
229 the join tree
230 </term>
231 <listitem>
232 <para>
233 The query's join tree shows the structure of the <literal>FROM</literal> clause.
234 For a simple query like <literal>SELECT ... FROM a, b, c</literal>, the join tree is just
235 a list of the <literal>FROM</literal> items, because we are allowed to join them in
236 any order. But when <literal>JOIN</literal> expressions, particularly outer joins,
237 are used, we have to join in the order shown by the joins.
238 In that case, the join tree shows the structure of the <literal>JOIN</literal> expressions. The
239 restrictions associated with particular <literal>JOIN</literal> clauses (from <literal>ON</literal> or
240 <literal>USING</literal> expressions) are stored as qualification expressions attached
241 to those join-tree nodes. It turns out to be convenient to store
242 the top-level <literal>WHERE</literal> expression as a qualification attached to the
243 top-level join-tree item, too. So really the join tree represents
244 both the <literal>FROM</literal> and <literal>WHERE</literal> clauses of a <command>SELECT</command>.
245 </para>
246 </listitem>
247 </varlistentry>
248
249 <varlistentry>
250 <term>
251 the others
252 </term>
253 <listitem>
254 <para>
255 The other parts of the query tree like the <literal>ORDER BY</literal>
256 clause aren't of interest here. The rule system
257 substitutes some entries there while applying rules, but that
258 doesn't have much to do with the fundamentals of the rule
259 system.
260 </para>
261 </listitem>
262 </varlistentry>
263
264 </variablelist>
265 </para>
266 </sect1>
267
268 <sect1 id="rules-views">
269 <title>Views and the Rule System</title>
270
271 <indexterm zone="rules-views">
272 <primary>rule</primary>
273 <secondary>and views</secondary>
274 </indexterm>
275
276 <indexterm zone="rules-views">
277 <primary>view</primary>
278 <secondary>implementation through rules</secondary>
279 </indexterm>
280
281 <para>
282 Views in <productname>PostgreSQL</productname> are implemented
283 using the rule system. A view is basically an empty table (having no
284 actual storage) with an <literal>ON SELECT DO INSTEAD</literal> rule.
285 Conventionally, that rule is named <literal>_RETURN</literal>.
286 So a view like
287
288 <programlisting>
289 CREATE VIEW myview AS SELECT * FROM mytab;
290 </programlisting>
291
292 is very nearly the same thing as
293
294 <programlisting>
295 CREATE TABLE myview (<replaceable>same column list as mytab</replaceable>);
296 CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
297 SELECT * FROM mytab;
298 </programlisting>
299
300 although you can't actually write that, because tables are not
301 allowed to have <literal>ON SELECT</literal> rules.
302 </para>
303
304 <para>
305 A view can also have other kinds of <literal>DO INSTEAD</literal>
306 rules, allowing <command>INSERT</command>, <command>UPDATE</command>,
307 or <command>DELETE</command> commands to be performed on the view
308 despite its lack of underlying storage.
309 This is discussed further below, in
310 <xref linkend="rules-views-update"/>.
311 </para>
312
313 <sect2 id="rules-select">
314 <title>How <command>SELECT</command> Rules Work</title>
315
316 <indexterm zone="rules-select">
317 <primary>rule</primary>
318 <secondary sortas="SELECT">for SELECT</secondary>
319 </indexterm>
320
321 <para>
322 Rules <literal>ON SELECT</literal> are applied to all queries as the last step, even
323 if the command given is an <command>INSERT</command>,
324 <command>UPDATE</command> or <command>DELETE</command>. And they
325 have different semantics from rules on the other command types in that they modify the
326 query tree in place instead of creating a new one. So
327 <command>SELECT</command> rules are described first.
328 </para>
329
330 <para>
331 Currently, there can be only one action in an <literal>ON SELECT</literal> rule, and it must
332 be an unconditional <command>SELECT</command> action that is <literal>INSTEAD</literal>. This restriction was
333 required to make rules safe enough to open them for ordinary users, and
334 it restricts <literal>ON SELECT</literal> rules to act like views.
335 </para>
336
337 <para>
338 The examples for this chapter are two join views that do some
339 calculations and some more views using them in turn. One of the
340 two first views is customized later by adding rules for
341 <command>INSERT</command>, <command>UPDATE</command>, and
342 <command>DELETE</command> operations so that the final result will
343 be a view that behaves like a real table with some magic
344 functionality. This is not such a simple example to start from and
345 this makes things harder to get into. But it's better to have one
346 example that covers all the points discussed step by step rather
347 than having many different ones that might mix up in mind.
348 </para>
349
350 <para>
351 The real tables we need in the first two rule system descriptions
352 are these:
353
354 <programlisting>
355 CREATE TABLE shoe_data (
356 shoename text, -- primary key
357 sh_avail integer, -- available number of pairs
358 slcolor text, -- preferred shoelace color
359 slminlen real, -- minimum shoelace length
360 slmaxlen real, -- maximum shoelace length
361 slunit text -- length unit
362 );
363
364 CREATE TABLE shoelace_data (
365 sl_name text, -- primary key
366 sl_avail integer, -- available number of pairs
367 sl_color text, -- shoelace color
368 sl_len real, -- shoelace length
369 sl_unit text -- length unit
370 );
371
372 CREATE TABLE unit (
373 un_name text, -- primary key
374 un_fact real -- factor to transform to cm
375 );
376 </programlisting>
377
378 As you can see, they represent shoe-store data.
379 </para>
380
381 <para>
382 The views are created as:
383
384 <programlisting>
385 CREATE VIEW shoe AS
386 SELECT sh.shoename,
387 sh.sh_avail,
388 sh.slcolor,
389 sh.slminlen,
390 sh.slminlen * un.un_fact AS slminlen_cm,
391 sh.slmaxlen,
392 sh.slmaxlen * un.un_fact AS slmaxlen_cm,
393 sh.slunit
394 FROM shoe_data sh, unit un
395 WHERE sh.slunit = un.un_name;
396
397 CREATE VIEW shoelace AS
398 SELECT s.sl_name,
399 s.sl_avail,
400 s.sl_color,
401 s.sl_len,
402 s.sl_unit,
403 s.sl_len * u.un_fact AS sl_len_cm
404 FROM shoelace_data s, unit u
405 WHERE s.sl_unit = u.un_name;
406
407 CREATE VIEW shoe_ready AS
408 SELECT rsh.shoename,
409 rsh.sh_avail,
410 rsl.sl_name,
411 rsl.sl_avail,
412 least(rsh.sh_avail, rsl.sl_avail) AS total_avail
413 FROM shoe rsh, shoelace rsl
414 WHERE rsl.sl_color = rsh.slcolor
415 AND rsl.sl_len_cm &gt;= rsh.slminlen_cm
416 AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm;
417 </programlisting>
418
419 The <command>CREATE VIEW</command> command for the
420 <literal>shoelace</literal> view (which is the simplest one we
421 have) will create a relation <literal>shoelace</literal> and an entry in
422 <structname>pg_rewrite</structname> that tells that there is a
423 rewrite rule that must be applied whenever the relation <literal>shoelace</literal>
424 is referenced in a query's range table. The rule has no rule
425 qualification (discussed later, with the non-<command>SELECT</command> rules, since
426 <command>SELECT</command> rules currently cannot have them) and it is <literal>INSTEAD</literal>. Note
427 that rule qualifications are not the same as query qualifications.
428 The action of our rule has a query qualification.
429 The action of the rule is one query tree that is a copy of the
430 <command>SELECT</command> statement in the view creation command.
431 </para>
432
433 <note>
434 <para>
435 The two extra range
436 table entries for <literal>NEW</literal> and <literal>OLD</literal> that you can see in
437 the <structname>pg_rewrite</structname> entry aren't of interest
438 for <command>SELECT</command> rules.
439 </para>
440 </note>
441
442 <para>
443 Now we populate <literal>unit</literal>, <literal>shoe_data</literal>
444 and <literal>shoelace_data</literal> and run a simple query on a view:
445
446 <programlisting>
447 INSERT INTO unit VALUES ('cm', 1.0);
448 INSERT INTO unit VALUES ('m', 100.0);
449 INSERT INTO unit VALUES ('inch', 2.54);
450
451 INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
452 INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
453 INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
454 INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
455
456 INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
457 INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
458 INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
459 INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
460 INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
461 INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
462 INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
463 INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
464
465 SELECT * FROM shoelace;
466
467 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
468 -----------+----------+----------+--------+---------+-----------
469 sl1 | 5 | black | 80 | cm | 80
470 sl2 | 6 | black | 100 | cm | 100
471 sl7 | 7 | brown | 60 | cm | 60
472 sl3 | 0 | black | 35 | inch | 88.9
473 sl4 | 8 | black | 40 | inch | 101.6
474 sl8 | 1 | brown | 40 | inch | 101.6
475 sl5 | 4 | brown | 1 | m | 100
476 sl6 | 0 | brown | 0.9 | m | 90
477 (8 rows)
478 </programlisting>
479 </para>
480
481 <para>
482 This is the simplest <command>SELECT</command> you can do on our
483 views, so we take this opportunity to explain the basics of view
484 rules. The <literal>SELECT * FROM shoelace</literal> was
485 interpreted by the parser and produced the query tree:
486
487 <programlisting>
488 SELECT shoelace.sl_name, shoelace.sl_avail,
489 shoelace.sl_color, shoelace.sl_len,
490 shoelace.sl_unit, shoelace.sl_len_cm
491 FROM shoelace shoelace;
492 </programlisting>
493
494 and this is given to the rule system. The rule system walks through the
495 range table and checks if there are rules
496 for any relation. When processing the range table entry for
497 <literal>shoelace</literal> (the only one up to now) it finds the
498 <literal>_RETURN</literal> rule with the query tree:
499
500 <programlisting>
501 SELECT s.sl_name, s.sl_avail,
502 s.sl_color, s.sl_len, s.sl_unit,
503 s.sl_len * u.un_fact AS sl_len_cm
504 FROM shoelace old, shoelace new,
505 shoelace_data s, unit u
506 WHERE s.sl_unit = u.un_name;
507 </programlisting>
508 </para>
509
510 <para>
511 To expand the view, the rewriter simply creates a subquery range-table
512 entry containing the rule's action query tree, and substitutes this
513 range table entry for the original one that referenced the view. The
514 resulting rewritten query tree is almost the same as if you had typed:
515
516 <programlisting>
517 SELECT shoelace.sl_name, shoelace.sl_avail,
518 shoelace.sl_color, shoelace.sl_len,
519 shoelace.sl_unit, shoelace.sl_len_cm
520 FROM (SELECT s.sl_name,
521 s.sl_avail,
522 s.sl_color,
523 s.sl_len,
524 s.sl_unit,
525 s.sl_len * u.un_fact AS sl_len_cm
526 FROM shoelace_data s, unit u
527 WHERE s.sl_unit = u.un_name) shoelace;
528 </programlisting>
529
530 There is one difference however: the subquery's range table has two
531 extra entries <literal>shoelace old</literal> and <literal>shoelace new</literal>. These entries don't
532 participate directly in the query, since they aren't referenced by
533 the subquery's join tree or target list. The rewriter uses them
534 to store the access privilege check information that was originally present
535 in the range-table entry that referenced the view. In this way, the
536 executor will still check that the user has proper privileges to access
537 the view, even though there's no direct use of the view in the rewritten
538 query.
539 </para>
540
541 <para>
542 That was the first rule applied. The rule system will continue checking
543 the remaining range-table entries in the top query (in this example there
544 are no more), and it will recursively check the range-table entries in
545 the added subquery to see if any of them reference views. (But it
546 won't expand <literal>old</literal> or <literal>new</literal> &mdash; otherwise we'd have infinite recursion!)
547 In this example, there are no rewrite rules for <literal>shoelace_data</literal> or <literal>unit</literal>,
548 so rewriting is complete and the above is the final result given to
549 the planner.
550 </para>
551
552 <para>
553 Now we want to write a query that finds out for which shoes currently in the store
554 we have the matching shoelaces (color and length) and where the
555 total number of exactly matching pairs is greater than or equal to two.
556
557 <programlisting>
558 SELECT * FROM shoe_ready WHERE total_avail &gt;= 2;
559
560 shoename | sh_avail | sl_name | sl_avail | total_avail
561 ----------+----------+---------+----------+-------------
562 sh1 | 2 | sl1 | 5 | 2
563 sh3 | 4 | sl7 | 7 | 4
564 (2 rows)
565 </programlisting>
566 </para>
567
568 <para>
569 The output of the parser this time is the query tree:
570
571 <programlisting>
572 SELECT shoe_ready.shoename, shoe_ready.sh_avail,
573 shoe_ready.sl_name, shoe_ready.sl_avail,
574 shoe_ready.total_avail
575 FROM shoe_ready shoe_ready
576 WHERE shoe_ready.total_avail &gt;= 2;
577 </programlisting>
578
579 The first rule applied will be the one for the
580 <literal>shoe_ready</literal> view and it results in the
581 query tree:
582
583 <programlisting>
584 SELECT shoe_ready.shoename, shoe_ready.sh_avail,
585 shoe_ready.sl_name, shoe_ready.sl_avail,
586 shoe_ready.total_avail
587 FROM (SELECT rsh.shoename,
588 rsh.sh_avail,
589 rsl.sl_name,
590 rsl.sl_avail,
591 least(rsh.sh_avail, rsl.sl_avail) AS total_avail
592 FROM shoe rsh, shoelace rsl
593 WHERE rsl.sl_color = rsh.slcolor
594 AND rsl.sl_len_cm &gt;= rsh.slminlen_cm
595 AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm) shoe_ready
596 WHERE shoe_ready.total_avail &gt;= 2;
597 </programlisting>
598
599 Similarly, the rules for <literal>shoe</literal> and
600 <literal>shoelace</literal> are substituted into the range table of
601 the subquery, leading to a three-level final query tree:
602
603 <programlisting>
604 SELECT shoe_ready.shoename, shoe_ready.sh_avail,
605 shoe_ready.sl_name, shoe_ready.sl_avail,
606 shoe_ready.total_avail
607 FROM (SELECT rsh.shoename,
608 rsh.sh_avail,
609 rsl.sl_name,
610 rsl.sl_avail,
611 least(rsh.sh_avail, rsl.sl_avail) AS total_avail
612 FROM (SELECT sh.shoename,
613 sh.sh_avail,
614 sh.slcolor,
615 sh.slminlen,
616 sh.slminlen * un.un_fact AS slminlen_cm,
617 sh.slmaxlen,
618 sh.slmaxlen * un.un_fact AS slmaxlen_cm,
619 sh.slunit
620 FROM shoe_data sh, unit un
621 WHERE sh.slunit = un.un_name) rsh,
622 (SELECT s.sl_name,
623 s.sl_avail,
624 s.sl_color,
625 s.sl_len,
626 s.sl_unit,
627 s.sl_len * u.un_fact AS sl_len_cm
628 FROM shoelace_data s, unit u
629 WHERE s.sl_unit = u.un_name) rsl
630 WHERE rsl.sl_color = rsh.slcolor
631 AND rsl.sl_len_cm &gt;= rsh.slminlen_cm
632 AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm) shoe_ready
633 WHERE shoe_ready.total_avail &gt;= 2;
634 </programlisting>
635 </para>
636
637 <para>
638 This might look inefficient, but the planner will collapse this into a
639 single-level query tree by <quote>pulling up</quote> the subqueries,
640 and then it will plan the joins just as if we'd written them out
641 manually. So collapsing the query tree is an optimization that the
642 rewrite system doesn't have to concern itself with.
643 </para>
644 </sect2>
645
646 <sect2 id="rules-views-non-select">
647 <title>View Rules in Non-<command>SELECT</command> Statements</title>
648
649 <para>
650 Two details of the query tree aren't touched in the description of
651 view rules above. These are the command type and the result relation.
652 In fact, the command type is not needed by view rules, but the result
653 relation may affect the way in which the query rewriter works, because
654 special care needs to be taken if the result relation is a view.
655 </para>
656
657 <para>
658 There are only a few differences between a query tree for a
659 <command>SELECT</command> and one for any other
660 command. Obviously, they have a different command type and for a
661 command other than a <command>SELECT</command>, the result
662 relation points to the range-table entry where the result should
663 go. Everything else is absolutely the same. So having two tables
664 <literal>t1</literal> and <literal>t2</literal> with columns <literal>a</literal> and
665 <literal>b</literal>, the query trees for the two statements:
666
667 <programlisting>
668 SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;
669
670 UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;
671 </programlisting>
672
673 are nearly identical. In particular:
674
675 <itemizedlist>
676 <listitem>
677 <para>
678 The range tables contain entries for the tables <literal>t1</literal> and <literal>t2</literal>.
679 </para>
680 </listitem>
681
682 <listitem>
683 <para>
684 The target lists contain one variable that points to column
685 <literal>b</literal> of the range table entry for table <literal>t2</literal>.
686 </para>
687 </listitem>
688
689 <listitem>
690 <para>
691 The qualification expressions compare the columns <literal>a</literal> of both
692 range-table entries for equality.
693 </para>
694 </listitem>
695
696 <listitem>
697 <para>
698 The join trees show a simple join between <literal>t1</literal> and <literal>t2</literal>.
699 </para>
700 </listitem>
701 </itemizedlist>
702 </para>
703
704 <para>
705 The consequence is, that both query trees result in similar
706 execution plans: They are both joins over the two tables. For the
707 <command>UPDATE</command> the missing columns from <literal>t1</literal> are added to
708 the target list by the planner and the final query tree will read
709 as:
710
711 <programlisting>
712 UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;
713 </programlisting>
714
715 and thus the executor run over the join will produce exactly the
716 same result set as:
717
718 <programlisting>
719 SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
720 </programlisting>
721
722 But there is a little problem in
723 <command>UPDATE</command>: the part of the executor plan that does
724 the join does not care what the results from the join are
725 meant for. It just produces a result set of rows. The fact that
726 one is a <command>SELECT</command> command and the other is an
727 <command>UPDATE</command> is handled higher up in the executor, where
728 it knows that this is an <command>UPDATE</command>, and it knows that
729 this result should go into table <literal>t1</literal>. But which of the rows
730 that are there has to be replaced by the new row?
731 </para>
732
733 <para>
734 To resolve this problem, another entry is added to the target list
735 in <command>UPDATE</command> (and also in
736 <command>DELETE</command>) statements: the current tuple ID
737 (<acronym>CTID</acronym>).<indexterm><primary>CTID</primary></indexterm>
738 This is a system column containing the
739 file block number and position in the block for the row. Knowing
740 the table, the <acronym>CTID</acronym> can be used to retrieve the
741 original row of <literal>t1</literal> to be updated. After adding the
742 <acronym>CTID</acronym> to the target list, the query actually looks like:
743
744 <programlisting>
745 SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
746 </programlisting>
747
748 Now another detail of <productname>PostgreSQL</productname> enters
749 the stage. Old table rows aren't overwritten, and this
750 is why <command>ROLLBACK</command> is fast. In an <command>UPDATE</command>,
751 the new result row is inserted into the table (after stripping the
752 <acronym>CTID</acronym>) and in the row header of the old row, which the
753 <acronym>CTID</acronym> pointed to, the <literal>cmax</literal> and
754 <literal>xmax</literal> entries are set to the current command counter
755 and current transaction ID. Thus the old row is hidden, and after
756 the transaction commits the vacuum cleaner can eventually remove
757 the dead row.
758 </para>
759
760 <para>
761 Knowing all that, we can simply apply view rules in absolutely
762 the same way to any command. There is no difference.
763 </para>
764 </sect2>
765
766 <sect2 id="rules-views-power">
767 <title>The Power of Views in <productname>PostgreSQL</productname></title>
768
769 <para>
770 The above demonstrates how the rule system incorporates view
771 definitions into the original query tree. In the second example, a
772 simple <command>SELECT</command> from one view created a final
773 query tree that is a join of 4 tables (<literal>unit</literal> was used twice with
774 different names).
775 </para>
776
777 <para>
778 The benefit of implementing views with the rule system is
779 that the planner has all
780 the information about which tables have to be scanned plus the
781 relationships between these tables plus the restrictive
782 qualifications from the views plus the qualifications from
783 the original query
784 in one single query tree. And this is still the situation
785 when the original query is already a join over views.
786 The planner has to decide which is
787 the best path to execute the query, and the more information
788 the planner has, the better this decision can be. And
789 the rule system as implemented in <productname>PostgreSQL</productname>
790 ensures that this is all information available about the query
791 up to that point.
792 </para>
793 </sect2>
794
795 <sect2 id="rules-views-update">
796 <title>Updating a View</title>
797
798 <para>
799 What happens if a view is named as the target relation for an
800 <command>INSERT</command>, <command>UPDATE</command>,
801 <command>DELETE</command>, or <command>MERGE</command>? Doing the
802 substitutions described above would give a query tree in which the result
803 relation points at a subquery range-table entry, which will not
804 work. There are several ways in which <productname>PostgreSQL</productname>
805 can support the appearance of updating a view, however.
806 In order of user-experienced complexity those are: automatically substitute
807 in the underlying table for the view, execute a user-defined trigger,
808 or rewrite the query per a user-defined rule.
809 These options are discussed below.
810 </para>
811
812 <para>
813 If the subquery selects from a single base relation and is simple
814 enough, the rewriter can automatically replace the subquery with the
815 underlying base relation so that the <command>INSERT</command>,
816 <command>UPDATE</command>, <command>DELETE</command>, or
817 <command>MERGE</command> is applied to the base relation in the
818 appropriate way. Views that are <quote>simple enough</quote> for this
819 are called <firstterm>automatically updatable</firstterm>. For detailed
820 information on the kinds of view that can be automatically updated, see
821 <xref linkend="sql-createview"/>.
822 </para>
823
824 <para>
825 Alternatively, the operation may be handled by a user-provided
826 <literal>INSTEAD OF</literal> trigger on the view
827 (see <xref linkend="sql-createtrigger"/>).
828 Rewriting works slightly differently
829 in this case. For <command>INSERT</command>, the rewriter does
830 nothing at all with the view, leaving it as the result relation
831 for the query. For <command>UPDATE</command>, <command>DELETE</command>,
832 and <command>MERGE</command>, it's still necessary to expand the
833 view query to produce the <quote>old</quote> rows that the command will
834 attempt to update, delete, or merge. So the view is expanded as normal,
835 but another unexpanded range-table entry is added to the query
836 to represent the view in its capacity as the result relation.
837 </para>
838
839 <para>
840 The problem that now arises is how to identify the rows to be
841 updated in the view. Recall that when the result relation
842 is a table, a special <acronym>CTID</acronym> entry is added to the target
843 list to identify the physical locations of the rows to be updated.
844 This does not work if the result relation is a view, because a view
845 does not have any <acronym>CTID</acronym>, since its rows do not have
846 actual physical locations. Instead, for an <command>UPDATE</command>,
847 <command>DELETE</command>, or <command>MERGE</command> operation, a
848 special <literal>wholerow</literal> entry is added to the target list,
849 which expands to include all columns from the view. The executor uses this
850 value to supply the <quote>old</quote> row to the
851 <literal>INSTEAD OF</literal> trigger. It is up to the trigger to work
852 out what to update based on the old and new row values.
853 </para>
854
855 <para>
856 Another possibility is for the user to define <literal>INSTEAD</literal>
857 rules that specify substitute actions for <command>INSERT</command>,
858 <command>UPDATE</command>, and <command>DELETE</command> commands on
859 a view. These rules will rewrite the command, typically into a command
860 that updates one or more tables, rather than views. That is the topic
861 of <xref linkend="rules-update"/>. Note that this will not work with
862 <command>MERGE</command>, which currently does not support rules on
863 the target relation other than <command>SELECT</command> rules.
864 </para>
865
866 <para>
867 Note that rules are evaluated first, rewriting the original query
868 before it is planned and executed. Therefore, if a view has
869 <literal>INSTEAD OF</literal> triggers as well as rules on <command>INSERT</command>,
870 <command>UPDATE</command>, or <command>DELETE</command>, then the rules will be
871 evaluated first, and depending on the result, the triggers may not be
872 used at all.
873 </para>
874
875 <para>
876 Automatic rewriting of an <command>INSERT</command>,
877 <command>UPDATE</command>, <command>DELETE</command>, or
878 <command>MERGE</command> query on a
879 simple view is always tried last. Therefore, if a view has rules or
880 triggers, they will override the default behavior of automatically
881 updatable views.
882 </para>
883
884 <para>
885 If there are no <literal>INSTEAD</literal> rules or <literal>INSTEAD OF</literal>
886 triggers for the view, and the rewriter cannot automatically rewrite
887 the query as an update on the underlying base relation, an error will
888 be thrown because the executor cannot update a view as such.
889 </para>
890
891 </sect2>
892
893 </sect1>
894
895 <sect1 id="rules-materializedviews">
896 <title>Materialized Views</title>
897
898 <indexterm zone="rules-materializedviews">
899 <primary>rule</primary>
900 <secondary>and materialized views</secondary>
901 </indexterm>
902
903 <indexterm zone="rules-materializedviews">
904 <primary>materialized view</primary>
905 <secondary>implementation through rules</secondary>
906 </indexterm>
907
908 <indexterm zone="rules-materializedviews">
909 <primary>view</primary>
910 <secondary>materialized</secondary>
911 </indexterm>
912
913 <para>
914 Materialized views in <productname>PostgreSQL</productname> use the
915 rule system like views do, but persist the results in a table-like form.
916 The main differences between:
917
918 <programlisting>
919 CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;
920 </programlisting>
921
922 and:
923
924 <programlisting>
925 CREATE TABLE mymatview AS SELECT * FROM mytab;
926 </programlisting>
927
928 are that the materialized view cannot subsequently be directly updated
929 and that the query used to create the materialized view is stored in
930 exactly the same way that a view's query is stored, so that fresh data
931 can be generated for the materialized view with:
932
933 <programlisting>
934 REFRESH MATERIALIZED VIEW mymatview;
935 </programlisting>
936
937 The information about a materialized view in the
938 <productname>PostgreSQL</productname> system catalogs is exactly
939 the same as it is for a table or view. So for the parser, a
940 materialized view is a relation, just like a table or a view. When
941 a materialized view is referenced in a query, the data is returned
942 directly from the materialized view, like from a table; the rule is
943 only used for populating the materialized view.
944 </para>
945
946 <para>
947 While access to the data stored in a materialized view is often much
948 faster than accessing the underlying tables directly or through a view,
949 the data is not always current; yet sometimes current data is not needed.
950 Consider a table which records sales:
951
952 <programlisting>
953 CREATE TABLE invoice (
954 invoice_no integer PRIMARY KEY,
955 seller_no integer, -- ID of salesperson
956 invoice_date date, -- date of sale
957 invoice_amt numeric(13,2) -- amount of sale
958 );
959 </programlisting>
960
961 If people want to be able to quickly graph historical sales data, they
962 might want to summarize, and they may not care about the incomplete data
963 for the current date:
964
965 <programlisting>
966 CREATE MATERIALIZED VIEW sales_summary AS
967 SELECT
968 seller_no,
969 invoice_date,
970 sum(invoice_amt)::numeric(13,2) as sales_amt
971 FROM invoice
972 WHERE invoice_date &lt; CURRENT_DATE
973 GROUP BY
974 seller_no,
975 invoice_date;
976
977 CREATE UNIQUE INDEX sales_summary_seller
978 ON sales_summary (seller_no, invoice_date);
979 </programlisting>
980
981 This materialized view might be useful for displaying a graph in the
982 dashboard created for salespeople. A job could be scheduled to update
983 the statistics each night using this SQL statement:
984
985 <programlisting>
986 REFRESH MATERIALIZED VIEW sales_summary;
987 </programlisting>
988 </para>
989
990 <para>
991 Another use for a materialized view is to allow faster access to data
992 brought across from a remote system through a foreign data wrapper.
993 A simple example using <literal>file_fdw</literal> is below, with timings,
994 but since this is using cache on the local system the performance
995 difference compared to access to a remote system would usually be greater
996 than shown here. Notice we are also exploiting the ability to put an
997 index on the materialized view, whereas <literal>file_fdw</literal> does
998 not support indexes; this advantage might not apply for other sorts of
999 foreign data access.
1000 </para>
1001
1002 <para>
1003 Setup:
1004
1005 <programlisting>
1006 CREATE EXTENSION file_fdw;
1007 CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
1008 CREATE FOREIGN TABLE words (word text NOT NULL)
1009 SERVER local_file
1010 OPTIONS (filename '/usr/share/dict/words');
1011 CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
1012 CREATE UNIQUE INDEX wrd_word ON wrd (word);
1013 CREATE EXTENSION pg_trgm;
1014 CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
1015 VACUUM ANALYZE wrd;
1016 </programlisting>
1017
1018 Now let's spell-check a word. Using <literal>file_fdw</literal> directly:
1019
1020 <programlisting>
1021 SELECT count(*) FROM words WHERE word = 'caterpiler';
1022
1023 count
1024 -------
1025 0
1026 (1 row)
1027 </programlisting>
1028
1029 With <command>EXPLAIN ANALYZE</command>, we see:
1030
1031 <programlisting>
1032 Aggregate (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1.00 loops=1)
1033 -&gt; Foreign Scan on words (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0.00 loops=1)
1034 Filter: (word = 'caterpiler'::text)
1035 Rows Removed by Filter: 479829
1036 Foreign File: /usr/share/dict/words
1037 Foreign File Size: 4953699
1038 Planning time: 0.118 ms
1039 Execution time: 188.273 ms
1040 </programlisting>
1041
1042 If the materialized view is used instead, the query is much faster:
1043
1044 <programlisting>
1045 Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1.00 loops=1)
1046 -&gt; Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0.00 loops=1)
1047 Index Cond: (word = 'caterpiler'::text)
1048 Heap Fetches: 0
1049 Index Searches: 1
1050 Planning time: 0.164 ms
1051 Execution time: 0.117 ms
1052 </programlisting>
1053
1054 Either way, the word is spelled wrong, so let's look for what we might
1055 have wanted. Again using <literal>file_fdw</literal> and
1056 <literal>pg_trgm</literal>:
1057
1058 <programlisting>
1059 SELECT word FROM words ORDER BY word &lt;-&gt; 'caterpiler' LIMIT 10;
1060
1061 word
1062 ---------------
1063 cater
1064 caterpillar
1065 Caterpillar
1066 caterpillars
1067 caterpillar's
1068 Caterpillar's
1069 caterer
1070 caterer's
1071 caters
1072 catered
1073 (10 rows)
1074 </programlisting>
1075
1076 <programlisting>
1077 Limit (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10.00 loops=1)
1078 -&gt; Sort (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10.00 loops=1)
1079 Sort Key: ((word &lt;-&gt; 'caterpiler'::text))
1080 Sort Method: top-N heapsort Memory: 25kB
1081 -&gt; Foreign Scan on words (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829.00 loops=1)
1082 Foreign File: /usr/share/dict/words
1083 Foreign File Size: 4953699
1084 Planning time: 0.128 ms
1085 Execution time: 1431.679 ms
1086 </programlisting>
1087
1088 Using the materialized view:
1089
1090 <programlisting>
1091 Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10.00 loops=1)
1092 -&gt; Index Scan using wrd_trgm on wrd (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10.00 loops=1)
1093 Order By: (word &lt;-&gt; 'caterpiler'::text)
1094 Index Searches: 1
1095 Planning time: 0.196 ms
1096 Execution time: 198.640 ms
1097 </programlisting>
1098
1099 If you can tolerate periodic update of the remote data to the local
1100 database, the performance benefit can be substantial.
1101 </para>
1102
1103 </sect1>
1104
1105 <sect1 id="rules-update">
1106 <title>Rules on <command>INSERT</command>, <command>UPDATE</command>, and <command>DELETE</command></title>
1107
1108 <indexterm zone="rules-update">
1109 <primary>rule</primary>
1110 <secondary sortas="INSERT">for INSERT</secondary>
1111 </indexterm>
1112
1113 <indexterm zone="rules-update">
1114 <primary>rule</primary>
1115 <secondary sortas="UPDATE">for UPDATE</secondary>
1116 </indexterm>
1117
1118 <indexterm zone="rules-update">
1119 <primary>rule</primary>
1120 <secondary sortas="DELETE">for DELETE</secondary>
1121 </indexterm>
1122
1123 <para>
1124 Rules that are defined on <command>INSERT</command>, <command>UPDATE</command>,
1125 and <command>DELETE</command> are significantly different from the view rules
1126 described in the previous sections. First, their <command>CREATE
1127 RULE</command> command allows more:
1128
1129 <itemizedlist>
1130 <listitem>
1131 <para>
1132 They are allowed to have no action.
1133 </para>
1134 </listitem>
1135
1136 <listitem>
1137 <para>
1138 They can have multiple actions.
1139 </para>
1140 </listitem>
1141
1142 <listitem>
1143 <para>
1144 They can be <literal>INSTEAD</literal> or <literal>ALSO</literal> (the default).
1145 </para>
1146 </listitem>
1147
1148 <listitem>
1149 <para>
1150 The pseudorelations <literal>NEW</literal> and <literal>OLD</literal> become useful.
1151 </para>
1152 </listitem>
1153
1154 <listitem>
1155 <para>
1156 They can have rule qualifications.
1157 </para>
1158 </listitem>
1159 </itemizedlist>
1160
1161 Second, they don't modify the query tree in place. Instead they
1162 create zero or more new query trees and can throw away the
1163 original one.
1164 </para>
1165
1166 <caution>
1167 <para>
1168 In many cases, tasks that could be performed by rules
1169 on <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> are better done
1170 with triggers. Triggers are notationally a bit more complicated, but their
1171 semantics are much simpler to understand. Rules tend to have surprising
1172 results when the original query contains volatile functions: volatile
1173 functions may get executed more times than expected in the process of
1174 carrying out the rules.
1175 </para>
1176
1177 <para>
1178 Also, there are some cases that are not supported by these types of rules at
1179 all, notably including <literal>WITH</literal> clauses in the original query and
1180 multiple-assignment sub-<literal>SELECT</literal>s in the <literal>SET</literal> list
1181 of <command>UPDATE</command> queries. This is because copying these constructs
1182 into a rule query would result in multiple evaluations of the sub-query,
1183 contrary to the express intent of the query's author.
1184 </para>
1185 </caution>
1186
1187 <sect2 id="rules-update-how">
1188 <title>How Update Rules Work</title>
1189
1190 <para>
1191 Keep the syntax:
1192
1193 <programlisting>
1194 CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
1195 TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
1196 DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }
1197 </programlisting>
1198
1199 in mind.
1200 In the following, <firstterm>update rules</firstterm> means rules that are defined
1201 on <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>.
1202 </para>
1203
1204 <para>
1205 Update rules get applied by the rule system when the result
1206 relation and the command type of a query tree are equal to the
1207 object and event given in the <command>CREATE RULE</command> command.
1208 For update rules, the rule system creates a list of query trees.
1209 Initially the query-tree list is empty.
1210 There can be zero (<literal>NOTHING</literal> key word), one, or multiple actions.
1211 To simplify, we will look at a rule with one action. This rule
1212 can have a qualification or not and it can be <literal>INSTEAD</literal> or
1213 <literal>ALSO</literal> (the default).
1214 </para>
1215
1216 <para>
1217 What is a rule qualification? It is a restriction that tells
1218 when the actions of the rule should be done and when not. This
1219 qualification can only reference the pseudorelations <literal>NEW</literal> and/or <literal>OLD</literal>,
1220 which basically represent the relation that was given as object (but with a
1221 special meaning).
1222 </para>
1223
1224 <para>
1225 So we have three cases that produce the following query trees for
1226 a one-action rule.
1227
1228 <variablelist>
1229 <varlistentry>
1230 <term>No qualification, with either <literal>ALSO</literal> or
1231 <literal>INSTEAD</literal></term>
1232 <listitem>
1233 <para>
1234 the query tree from the rule action with the original query
1235 tree's qualification added
1236 </para>
1237 </listitem>
1238 </varlistentry>
1239
1240 <varlistentry>
1241 <term>Qualification given and <literal>ALSO</literal></term>
1242 <listitem>
1243 <para>
1244 the query tree from the rule action with the rule
1245 qualification and the original query tree's qualification
1246 added
1247 </para>
1248 </listitem>
1249 </varlistentry>
1250
1251 <varlistentry>
1252 <term>Qualification given and <literal>INSTEAD</literal></term>
1253 <listitem>
1254 <para>
1255 the query tree from the rule action with the rule
1256 qualification and the original query tree's qualification; and
1257 the original query tree with the negated rule qualification
1258 added
1259 </para>
1260 </listitem>
1261 </varlistentry>
1262 </variablelist>
1263
1264 Finally, if the rule is <literal>ALSO</literal>, the unchanged original query tree is
1265 added to the list. Since only qualified <literal>INSTEAD</literal> rules already add the
1266 original query tree, we end up with either one or two output query trees
1267 for a rule with one action.
1268 </para>
1269
1270 <para>
1271 For <literal>ON INSERT</literal> rules, the original query (if not suppressed by <literal>INSTEAD</literal>)
1272 is done before any actions added by rules. This allows the actions to
1273 see the inserted row(s). But for <literal>ON UPDATE</literal> and <literal>ON
1274 DELETE</literal> rules, the original query is done after the actions added by rules.
1275 This ensures that the actions can see the to-be-updated or to-be-deleted
1276 rows; otherwise, the actions might do nothing because they find no rows
1277 matching their qualifications.
1278 </para>
1279
1280 <para>
1281 The query trees generated from rule actions are thrown into the
1282 rewrite system again, and maybe more rules get applied resulting
1283 in additional or fewer query trees.
1284 So a rule's actions must have either a different
1285 command type or a different result relation than the rule itself is
1286 on, otherwise this recursive process will end up in an infinite loop.
1287 (Recursive expansion of a rule will be detected and reported as an
1288 error.)
1289 </para>
1290
1291 <para>
1292 The query trees found in the actions of the
1293 <structname>pg_rewrite</structname> system catalog are only
1294 templates. Since they can reference the range-table entries for
1295 <literal>NEW</literal> and <literal>OLD</literal>, some substitutions have to be made before they can be
1296 used. For any reference to <literal>NEW</literal>, the target list of the original
1297 query is searched for a corresponding entry. If found, that
1298 entry's expression replaces the reference. Otherwise, <literal>NEW</literal> means the
1299 same as <literal>OLD</literal> (for an <command>UPDATE</command>) or is replaced by
1300 a null value (for an <command>INSERT</command>). Any reference to <literal>OLD</literal> is
1301 replaced by a reference to the range-table entry that is the
1302 result relation.
1303 </para>
1304
1305 <para>
1306 After the system is done applying update rules, it applies view rules to the
1307 produced query tree(s). Views cannot insert new update actions so
1308 there is no need to apply update rules to the output of view rewriting.
1309 </para>
1310
1311 <sect3 id="rules-update-how-first">
1312 <title>A First Rule Step by Step</title>
1313
1314 <para>
1315 Say we want to trace changes to the <literal>sl_avail</literal> column in the
1316 <literal>shoelace_data</literal> relation. So we set up a log table
1317 and a rule that conditionally writes a log entry when an
1318 <command>UPDATE</command> is performed on
1319 <literal>shoelace_data</literal>.
1320
1321 <programlisting>
1322 CREATE TABLE shoelace_log (
1323 sl_name text, -- shoelace changed
1324 sl_avail integer, -- new available value
1325 log_who text, -- who did it
1326 log_when timestamp -- when
1327 );
1328
1329 CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
1330 WHERE NEW.sl_avail &lt;&gt; OLD.sl_avail
1331 DO INSERT INTO shoelace_log VALUES (
1332 NEW.sl_name,
1333 NEW.sl_avail,
1334 current_user,
1335 current_timestamp
1336 );
1337 </programlisting>
1338 </para>
1339
1340 <para>
1341 Now someone does:
1342
1343 <programlisting>
1344 UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
1345 </programlisting>
1346
1347 and we look at the log table:
1348
1349 <programlisting>
1350 SELECT * FROM shoelace_log;
1351
1352 sl_name | sl_avail | log_who | log_when
1353 ---------+----------+---------+----------------------------------
1354 sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST
1355 (1 row)
1356 </programlisting>
1357 </para>
1358
1359 <para>
1360 That's what we expected. What happened in the background is the following.
1361 The parser created the query tree:
1362
1363 <programlisting>
1364 UPDATE shoelace_data SET sl_avail = 6
1365 FROM shoelace_data shoelace_data
1366 WHERE shoelace_data.sl_name = 'sl7';
1367 </programlisting>
1368
1369 There is a rule <literal>log_shoelace</literal> that is <literal>ON UPDATE</literal> with the rule
1370 qualification expression:
1371
1372 <programlisting>
1373 NEW.sl_avail &lt;&gt; OLD.sl_avail
1374 </programlisting>
1375
1376 and the action:
1377
1378 <programlisting>
1379 INSERT INTO shoelace_log VALUES (
1380 new.sl_name, new.sl_avail,
1381 current_user, current_timestamp )
1382 FROM shoelace_data new, shoelace_data old;
1383 </programlisting>
1384
1385 (This looks a little strange since you cannot normally write
1386 <literal>INSERT ... VALUES ... FROM</literal>. The <literal>FROM</literal>
1387 clause here is just to indicate that there are range-table entries
1388 in the query tree for <literal>new</literal> and <literal>old</literal>.
1389 These are needed so that they can be referenced by variables in
1390 the <command>INSERT</command> command's query tree.)
1391 </para>
1392
1393 <para>
1394 The rule is a qualified <literal>ALSO</literal> rule, so the rule system
1395 has to return two query trees: the modified rule action and the original
1396 query tree. In step 1, the range table of the original query is
1397 incorporated into the rule's action query tree. This results in:
1398
1399 <programlisting>
1400 INSERT INTO shoelace_log VALUES (
1401 new.sl_name, new.sl_avail,
1402 current_user, current_timestamp )
1403 FROM shoelace_data new, shoelace_data old,
1404 <emphasis>shoelace_data shoelace_data</emphasis>;
1405 </programlisting>
1406
1407 In step 2, the rule qualification is added to it, so the result set
1408 is restricted to rows where <literal>sl_avail</literal> changes:
1409
1410 <programlisting>
1411 INSERT INTO shoelace_log VALUES (
1412 new.sl_name, new.sl_avail,
1413 current_user, current_timestamp )
1414 FROM shoelace_data new, shoelace_data old,
1415 shoelace_data shoelace_data
1416 <emphasis>WHERE new.sl_avail &lt;&gt; old.sl_avail</emphasis>;
1417 </programlisting>
1418
1419 (This looks even stranger, since <literal>INSERT ... VALUES</literal> doesn't have
1420 a <literal>WHERE</literal> clause either, but the planner and executor will have no
1421 difficulty with it. They need to support this same functionality
1422 anyway for <literal>INSERT ... SELECT</literal>.)
1423 </para>
1424
1425 <para>
1426 In step 3, the original query tree's qualification is added,
1427 restricting the result set further to only the rows that would have been touched
1428 by the original query:
1429
1430 <programlisting>
1431 INSERT INTO shoelace_log VALUES (
1432 new.sl_name, new.sl_avail,
1433 current_user, current_timestamp )
1434 FROM shoelace_data new, shoelace_data old,
1435 shoelace_data shoelace_data
1436 WHERE new.sl_avail &lt;&gt; old.sl_avail
1437 <emphasis>AND shoelace_data.sl_name = 'sl7'</emphasis>;
1438 </programlisting>
1439 </para>
1440
1441 <para>
1442 Step 4 replaces references to <literal>NEW</literal> by the target list entries from the
1443 original query tree or by the matching variable references
1444 from the result relation:
1445
1446 <programlisting>
1447 INSERT INTO shoelace_log VALUES (
1448 <emphasis>shoelace_data.sl_name</emphasis>, <emphasis>6</emphasis>,
1449 current_user, current_timestamp )
1450 FROM shoelace_data new, shoelace_data old,
1451 shoelace_data shoelace_data
1452 WHERE <emphasis>6</emphasis> &lt;&gt; old.sl_avail
1453 AND shoelace_data.sl_name = 'sl7';
1454 </programlisting>
1455
1456 </para>
1457
1458 <para>
1459 Step 5 changes <literal>OLD</literal> references into result relation references:
1460
1461 <programlisting>
1462 INSERT INTO shoelace_log VALUES (
1463 shoelace_data.sl_name, 6,
1464 current_user, current_timestamp )
1465 FROM shoelace_data new, shoelace_data old,
1466 shoelace_data shoelace_data
1467 WHERE 6 &lt;&gt; <emphasis>shoelace_data.sl_avail</emphasis>
1468 AND shoelace_data.sl_name = 'sl7';
1469 </programlisting>
1470 </para>
1471
1472 <para>
1473 That's it. Since the rule is <literal>ALSO</literal>, we also output the
1474 original query tree. In short, the output from the rule system
1475 is a list of two query trees that correspond to these statements:
1476
1477 <programlisting>
1478 INSERT INTO shoelace_log VALUES (
1479 shoelace_data.sl_name, 6,
1480 current_user, current_timestamp )
1481 FROM shoelace_data
1482 WHERE 6 &lt;&gt; shoelace_data.sl_avail
1483 AND shoelace_data.sl_name = 'sl7';
1484
1485 UPDATE shoelace_data SET sl_avail = 6
1486 WHERE sl_name = 'sl7';
1487 </programlisting>
1488
1489 These are executed in this order, and that is exactly what
1490 the rule was meant to do.
1491 </para>
1492
1493 <para>
1494 The substitutions and the added qualifications
1495 ensure that, if the original query would be, say:
1496
1497 <programlisting>
1498 UPDATE shoelace_data SET sl_color = 'green'
1499 WHERE sl_name = 'sl7';
1500 </programlisting>
1501
1502 no log entry would get written. In that case, the original query
1503 tree does not contain a target list entry for
1504 <literal>sl_avail</literal>, so <literal>NEW.sl_avail</literal> will get
1505 replaced by <literal>shoelace_data.sl_avail</literal>. Thus, the extra
1506 command generated by the rule is:
1507
1508 <programlisting>
1509 INSERT INTO shoelace_log VALUES (
1510 shoelace_data.sl_name, <emphasis>shoelace_data.sl_avail</emphasis>,
1511 current_user, current_timestamp )
1512 FROM shoelace_data
1513 WHERE <emphasis>shoelace_data.sl_avail</emphasis> &lt;&gt; shoelace_data.sl_avail
1514 AND shoelace_data.sl_name = 'sl7';
1515 </programlisting>
1516
1517 and that qualification will never be true.
1518 </para>
1519
1520 <para>
1521 It will also work if the original query modifies multiple rows. So
1522 if someone issued the command:
1523
1524 <programlisting>
1525 UPDATE shoelace_data SET sl_avail = 0
1526 WHERE sl_color = 'black';
1527 </programlisting>
1528
1529 four rows in fact get updated (<literal>sl1</literal>, <literal>sl2</literal>, <literal>sl3</literal>, and <literal>sl4</literal>).
1530 But <literal>sl3</literal> already has <literal>sl_avail = 0</literal>. In this case, the original
1531 query trees qualification is different and that results
1532 in the extra query tree:
1533
1534 <programlisting>
1535 INSERT INTO shoelace_log
1536 SELECT shoelace_data.sl_name, 0,
1537 current_user, current_timestamp
1538 FROM shoelace_data
1539 WHERE 0 &lt;&gt; shoelace_data.sl_avail
1540 AND <emphasis>shoelace_data.sl_color = 'black'</emphasis>;
1541 </programlisting>
1542
1543 being generated by the rule. This query tree will surely insert
1544 three new log entries. And that's absolutely correct.
1545 </para>
1546
1547 <para>
1548 Here we can see why it is important that the original query tree
1549 is executed last. If the <command>UPDATE</command> had been
1550 executed first, all the rows would have already been set to zero, so the
1551 logging <command>INSERT</command> would not find any row where
1552 <literal>0 &lt;&gt; shoelace_data.sl_avail</literal>.
1553 </para>
1554 </sect3>
1555
1556 </sect2>
1557
1558 <sect2 id="rules-update-views">
1559 <title>Cooperation with Views</title>
1560
1561 <indexterm zone="rules-update-views"><primary>view</primary><secondary>updating</secondary></indexterm>
1562
1563 <para>
1564 A simple way to protect view relations from the mentioned
1565 possibility that someone can try to run <command>INSERT</command>,
1566 <command>UPDATE</command>, or <command>DELETE</command> on them is
1567 to let those query trees get thrown away. So we could create the rules:
1568
1569 <programlisting>
1570 CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
1571 DO INSTEAD NOTHING;
1572 CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
1573 DO INSTEAD NOTHING;
1574 CREATE RULE shoe_del_protect AS ON DELETE TO shoe
1575 DO INSTEAD NOTHING;
1576 </programlisting>
1577
1578 If someone now tries to do any of these operations on the view
1579 relation <literal>shoe</literal>, the rule system will
1580 apply these rules. Since the rules have
1581 no actions and are <literal>INSTEAD</literal>, the resulting list of
1582 query trees will be empty and the whole query will become
1583 nothing because there is nothing left to be optimized or
1584 executed after the rule system is done with it.
1585 </para>
1586
1587 <para>
1588 A more sophisticated way to use the rule system is to
1589 create rules that rewrite the query tree into one that
1590 does the right operation on the real tables. To do that
1591 on the <literal>shoelace</literal> view, we create
1592 the following rules:
1593
1594 <programlisting>
1595 CREATE RULE shoelace_ins AS ON INSERT TO shoelace
1596 DO INSTEAD
1597 INSERT INTO shoelace_data VALUES (
1598 NEW.sl_name,
1599 NEW.sl_avail,
1600 NEW.sl_color,
1601 NEW.sl_len,
1602 NEW.sl_unit
1603 );
1604
1605 CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
1606 DO INSTEAD
1607 UPDATE shoelace_data
1608 SET sl_name = NEW.sl_name,
1609 sl_avail = NEW.sl_avail,
1610 sl_color = NEW.sl_color,
1611 sl_len = NEW.sl_len,
1612 sl_unit = NEW.sl_unit
1613 WHERE sl_name = OLD.sl_name;
1614
1615 CREATE RULE shoelace_del AS ON DELETE TO shoelace
1616 DO INSTEAD
1617 DELETE FROM shoelace_data
1618 WHERE sl_name = OLD.sl_name;
1619 </programlisting>
1620 </para>
1621
1622 <para>
1623 If you want to support <literal>RETURNING</literal> queries on the view,
1624 you need to make the rules include <literal>RETURNING</literal> clauses that
1625 compute the view rows. This is usually pretty trivial for views on a
1626 single table, but it's a bit tedious for join views such as
1627 <literal>shoelace</literal>. An example for the insert case is:
1628
1629 <programlisting>
1630 CREATE RULE shoelace_ins AS ON INSERT TO shoelace
1631 DO INSTEAD
1632 INSERT INTO shoelace_data VALUES (
1633 NEW.sl_name,
1634 NEW.sl_avail,
1635 NEW.sl_color,
1636 NEW.sl_len,
1637 NEW.sl_unit
1638 )
1639 RETURNING
1640 shoelace_data.*,
1641 (SELECT shoelace_data.sl_len * u.un_fact
1642 FROM unit u WHERE shoelace_data.sl_unit = u.un_name);
1643 </programlisting>
1644
1645 Note that this one rule supports both <command>INSERT</command> and
1646 <command>INSERT RETURNING</command> queries on the view &mdash; the
1647 <literal>RETURNING</literal> clause is simply ignored for <command>INSERT</command>.
1648 </para>
1649
1650 <para>
1651 Note that in the <literal>RETURNING</literal> clause of a rule,
1652 <literal>OLD</literal> and <literal>NEW</literal> refer to the
1653 pseudorelations added as extra range table entries to the rewritten
1654 query, rather than old/new rows in the result relation. Thus, for
1655 example, in a rule supporting <command>UPDATE</command> queries on this
1656 view, if the <literal>RETURNING</literal> clause contained
1657 <literal>old.sl_name</literal>, the old name would always be returned,
1658 regardless of whether the <literal>RETURNING</literal> clause in the
1659 query on the view specified <literal>OLD</literal> or <literal>NEW</literal>,
1660 which might be confusing. To avoid this confusion, and support returning
1661 old and new values in queries on the view, the <literal>RETURNING</literal>
1662 clause in the rule definition should refer to entries from the result
1663 relation such as <literal>shoelace_data.sl_name</literal>, without
1664 specifying <literal>OLD</literal> or <literal>NEW</literal>.
1665 </para>
1666
1667 <para>
1668 Now assume that once in a while, a pack of shoelaces arrives at
1669 the shop and a big parts list along with it. But you don't want
1670 to manually update the <literal>shoelace</literal> view every
1671 time. Instead we set up two little tables: one where you can
1672 insert the items from the part list, and one with a special
1673 trick. The creation commands for these are:
1674
1675 <programlisting>
1676 CREATE TABLE shoelace_arrive (
1677 arr_name text,
1678 arr_quant integer
1679 );
1680
1681 CREATE TABLE shoelace_ok (
1682 ok_name text,
1683 ok_quant integer
1684 );
1685
1686 CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
1687 DO INSTEAD
1688 UPDATE shoelace
1689 SET sl_avail = sl_avail + NEW.ok_quant
1690 WHERE sl_name = NEW.ok_name;
1691 </programlisting>
1692
1693 Now you can fill the table <literal>shoelace_arrive</literal> with
1694 the data from the parts list:
1695
1696 <programlisting>
1697 SELECT * FROM shoelace_arrive;
1698
1699 arr_name | arr_quant
1700 ----------+-----------
1701 sl3 | 10
1702 sl6 | 20
1703 sl8 | 20
1704 (3 rows)
1705 </programlisting>
1706
1707 Take a quick look at the current data:
1708
1709 <programlisting>
1710 SELECT * FROM shoelace;
1711
1712 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1713 ----------+----------+----------+--------+---------+-----------
1714 sl1 | 5 | black | 80 | cm | 80
1715 sl2 | 6 | black | 100 | cm | 100
1716 sl7 | 6 | brown | 60 | cm | 60
1717 sl3 | 0 | black | 35 | inch | 88.9
1718 sl4 | 8 | black | 40 | inch | 101.6
1719 sl8 | 1 | brown | 40 | inch | 101.6
1720 sl5 | 4 | brown | 1 | m | 100
1721 sl6 | 0 | brown | 0.9 | m | 90
1722 (8 rows)
1723 </programlisting>
1724
1725 Now move the arrived shoelaces in:
1726
1727 <programlisting>
1728 INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
1729 </programlisting>
1730
1731 and check the results:
1732
1733 <programlisting>
1734 SELECT * FROM shoelace ORDER BY sl_name;
1735
1736 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1737 ----------+----------+----------+--------+---------+-----------
1738 sl1 | 5 | black | 80 | cm | 80
1739 sl2 | 6 | black | 100 | cm | 100
1740 sl7 | 6 | brown | 60 | cm | 60
1741 sl4 | 8 | black | 40 | inch | 101.6
1742 sl3 | 10 | black | 35 | inch | 88.9
1743 sl8 | 21 | brown | 40 | inch | 101.6
1744 sl5 | 4 | brown | 1 | m | 100
1745 sl6 | 20 | brown | 0.9 | m | 90
1746 (8 rows)
1747
1748 SELECT * FROM shoelace_log;
1749
1750 sl_name | sl_avail | log_who| log_when
1751 ---------+----------+--------+----------------------------------
1752 sl7 | 6 | Al | Tue Oct 20 19:14:45 1998 MET DST
1753 sl3 | 10 | Al | Tue Oct 20 19:25:16 1998 MET DST
1754 sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST
1755 sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST
1756 (4 rows)
1757 </programlisting>
1758 </para>
1759
1760 <para>
1761 It's a long way from the one <literal>INSERT ... SELECT</literal>
1762 to these results. And the description of the query-tree
1763 transformation will be the last in this chapter. First, there is
1764 the parser's output:
1765
1766 <programlisting>
1767 INSERT INTO shoelace_ok
1768 SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant
1769 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;
1770 </programlisting>
1771
1772 Now the first rule <literal>shoelace_ok_ins</literal> is applied and turns this
1773 into:
1774
1775 <programlisting>
1776 UPDATE shoelace
1777 SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant
1778 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
1779 shoelace_ok old, shoelace_ok new,
1780 shoelace shoelace
1781 WHERE shoelace.sl_name = shoelace_arrive.arr_name;
1782 </programlisting>
1783
1784 and throws away the original <command>INSERT</command> on
1785 <literal>shoelace_ok</literal>. This rewritten query is passed to
1786 the rule system again, and the second applied rule
1787 <literal>shoelace_upd</literal> produces:
1788
1789 <programlisting>
1790 UPDATE shoelace_data
1791 SET sl_name = shoelace.sl_name,
1792 sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,
1793 sl_color = shoelace.sl_color,
1794 sl_len = shoelace.sl_len,
1795 sl_unit = shoelace.sl_unit
1796 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
1797 shoelace_ok old, shoelace_ok new,
1798 shoelace shoelace, shoelace old,
1799 shoelace new, shoelace_data shoelace_data
1800 WHERE shoelace.sl_name = shoelace_arrive.arr_name
1801 AND shoelace_data.sl_name = shoelace.sl_name;
1802 </programlisting>
1803
1804 Again it's an <literal>INSTEAD</literal> rule and the previous query tree is trashed.
1805 Note that this query still uses the view <literal>shoelace</literal>.
1806 But the rule system isn't finished with this step, so it continues
1807 and applies the <literal>_RETURN</literal> rule on it, and we get:
1808
1809 <programlisting>
1810 UPDATE shoelace_data
1811 SET sl_name = s.sl_name,
1812 sl_avail = s.sl_avail + shoelace_arrive.arr_quant,
1813 sl_color = s.sl_color,
1814 sl_len = s.sl_len,
1815 sl_unit = s.sl_unit
1816 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
1817 shoelace_ok old, shoelace_ok new,
1818 shoelace shoelace, shoelace old,
1819 shoelace new, shoelace_data shoelace_data,
1820 shoelace old, shoelace new,
1821 shoelace_data s, unit u
1822 WHERE s.sl_name = shoelace_arrive.arr_name
1823 AND shoelace_data.sl_name = s.sl_name;
1824 </programlisting>
1825
1826 Finally, the rule <literal>log_shoelace</literal> gets applied,
1827 producing the extra query tree:
1828
1829 <programlisting>
1830 INSERT INTO shoelace_log
1831 SELECT s.sl_name,
1832 s.sl_avail + shoelace_arrive.arr_quant,
1833 current_user,
1834 current_timestamp
1835 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
1836 shoelace_ok old, shoelace_ok new,
1837 shoelace shoelace, shoelace old,
1838 shoelace new, shoelace_data shoelace_data,
1839 shoelace old, shoelace new,
1840 shoelace_data s, unit u,
1841 shoelace_data old, shoelace_data new
1842 shoelace_log shoelace_log
1843 WHERE s.sl_name = shoelace_arrive.arr_name
1844 AND shoelace_data.sl_name = s.sl_name
1845 AND (s.sl_avail + shoelace_arrive.arr_quant) &lt;&gt; s.sl_avail;
1846 </programlisting>
1847
1848 After that the rule system runs out of rules and returns the
1849 generated query trees.
1850 </para>
1851
1852 <para>
1853 So we end up with two final query trees that are equivalent to the
1854 <acronym>SQL</acronym> statements:
1855
1856 <programlisting>
1857 INSERT INTO shoelace_log
1858 SELECT s.sl_name,
1859 s.sl_avail + shoelace_arrive.arr_quant,
1860 current_user,
1861 current_timestamp
1862 FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,
1863 shoelace_data s
1864 WHERE s.sl_name = shoelace_arrive.arr_name
1865 AND shoelace_data.sl_name = s.sl_name
1866 AND s.sl_avail + shoelace_arrive.arr_quant &lt;&gt; s.sl_avail;
1867
1868 UPDATE shoelace_data
1869 SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant
1870 FROM shoelace_arrive shoelace_arrive,
1871 shoelace_data shoelace_data,
1872 shoelace_data s
1873 WHERE s.sl_name = shoelace_arrive.sl_name
1874 AND shoelace_data.sl_name = s.sl_name;
1875 </programlisting>
1876
1877 The result is that data coming from one relation inserted into another,
1878 changed into updates on a third, changed into updating
1879 a fourth plus logging that final update in a fifth
1880 gets reduced into two queries.
1881 </para>
1882
1883 <para>
1884 There is a little detail that's a bit ugly. Looking at the two
1885 queries, it turns out that the <literal>shoelace_data</literal>
1886 relation appears twice in the range table where it could
1887 definitely be reduced to one. The planner does not handle it and
1888 so the execution plan for the rule systems output of the
1889 <command>INSERT</command> will be
1890
1891 <literallayout class="monospaced">
1892 Nested Loop
1893 -&gt; Merge Join
1894 -&gt; Seq Scan
1895 -&gt; Sort
1896 -&gt; Seq Scan on s
1897 -&gt; Seq Scan
1898 -&gt; Sort
1899 -&gt; Seq Scan on shoelace_arrive
1900 -&gt; Seq Scan on shoelace_data
1901 </literallayout>
1902
1903 while omitting the extra range table entry would result in a
1904
1905 <literallayout class="monospaced">
1906 Merge Join
1907 -&gt; Seq Scan
1908 -&gt; Sort
1909 -&gt; Seq Scan on s
1910 -&gt; Seq Scan
1911 -&gt; Sort
1912 -&gt; Seq Scan on shoelace_arrive
1913 </literallayout>
1914
1915 which produces exactly the same entries in the log table. Thus,
1916 the rule system caused one extra scan on the table
1917 <literal>shoelace_data</literal> that is absolutely not
1918 necessary. And the same redundant scan is done once more in the
1919 <command>UPDATE</command>. But it was a really hard job to make
1920 that all possible at all.
1921 </para>
1922
1923 <para>
1924 Now we make a final demonstration of the
1925 <productname>PostgreSQL</productname> rule system and its power.
1926 Say you add some shoelaces with extraordinary colors to your
1927 database:
1928
1929 <programlisting>
1930 INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
1931 INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
1932 </programlisting>
1933
1934 We would like to make a view to check which
1935 <literal>shoelace</literal> entries do not fit any shoe in color.
1936 The view for this is:
1937
1938 <programlisting>
1939 CREATE VIEW shoelace_mismatch AS
1940 SELECT * FROM shoelace WHERE NOT EXISTS
1941 (SELECT shoename FROM shoe WHERE slcolor = sl_color);
1942 </programlisting>
1943
1944 Its output is:
1945
1946 <programlisting>
1947 SELECT * FROM shoelace_mismatch;
1948
1949 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1950 ---------+----------+----------+--------+---------+-----------
1951 sl9 | 0 | pink | 35 | inch | 88.9
1952 sl10 | 1000 | magenta | 40 | inch | 101.6
1953 </programlisting>
1954 </para>
1955
1956 <para>
1957 Now we want to set it up so that mismatching shoelaces that are
1958 not in stock are deleted from the database.
1959 To make it a little harder for <productname>PostgreSQL</productname>,
1960 we don't delete it directly. Instead we create one more view:
1961
1962 <programlisting>
1963 CREATE VIEW shoelace_can_delete AS
1964 SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;
1965 </programlisting>
1966
1967 and do it this way:
1968
1969 <programlisting>
1970 DELETE FROM shoelace WHERE EXISTS
1971 (SELECT * FROM shoelace_can_delete
1972 WHERE sl_name = shoelace.sl_name);
1973 </programlisting>
1974
1975 The results are:
1976
1977 <programlisting>
1978 SELECT * FROM shoelace;
1979
1980 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1981 ---------+----------+----------+--------+---------+-----------
1982 sl1 | 5 | black | 80 | cm | 80
1983 sl2 | 6 | black | 100 | cm | 100
1984 sl7 | 6 | brown | 60 | cm | 60
1985 sl4 | 8 | black | 40 | inch | 101.6
1986 sl3 | 10 | black | 35 | inch | 88.9
1987 sl8 | 21 | brown | 40 | inch | 101.6
1988 sl10 | 1000 | magenta | 40 | inch | 101.6
1989 sl5 | 4 | brown | 1 | m | 100
1990 sl6 | 20 | brown | 0.9 | m | 90
1991 (9 rows)
1992 </programlisting>
1993 </para>
1994
1995 <para>
1996 A <command>DELETE</command> on a view, with a subquery qualification that
1997 in total uses 4 nesting/joined views, where one of them
1998 itself has a subquery qualification containing a view
1999 and where calculated view columns are used,
2000 gets rewritten into
2001 one single query tree that deletes the requested data
2002 from a real table.
2003 </para>
2004
2005 <para>
2006 There are probably only a few situations out in the real world
2007 where such a construct is necessary. But it makes you feel
2008 comfortable that it works.
2009 </para>
2010 </sect2>
2011
2012 </sect1>
2013
2014 <sect1 id="rules-privileges">
2015 <title>Rules and Privileges</title>
2016
2017 <indexterm zone="rules-privileges">
2018 <primary>privilege</primary>
2019 <secondary sortas="Regeln">with rules</secondary>
2020 </indexterm>
2021
2022 <indexterm zone="rules-privileges">
2023 <primary>privilege</primary>
2024 <secondary sortas="Sichten">with views</secondary>
2025 </indexterm>
2026
2027 <para>
2028 Due to rewriting of queries by the <productname>PostgreSQL</productname>
2029 rule system, other tables/views than those used in the original
2030 query get accessed. When update rules are used, this can include write access
2031 to tables.
2032 </para>
2033
2034 <para>
2035 Rewrite rules don't have a separate owner. The owner of
2036 a relation (table or view) is automatically the owner of the
2037 rewrite rules that are defined for it.
2038 The <productname>PostgreSQL</productname> rule system changes the
2039 behavior of the default access control system. With the exception of
2040 <literal>SELECT</literal> rules associated with security invoker views
2041 (see <link linkend="sql-createview"><command>CREATE VIEW</command></link>),
2042 all relations that are used due to rules get checked against the
2043 privileges of the rule owner, not the user invoking the rule.
2044 This means that, except for security invoker views, users only need the
2045 required privileges for the tables/views that are explicitly named in
2046 their queries.
2047 </para>
2048
2049 <para>
2050 For example: A user has a list of phone numbers where some of
2051 them are private, the others are of interest for the assistant of the office.
2052 The user can construct the following:
2053
2054 <programlisting>
2055 CREATE TABLE phone_data (person text, phone text, private boolean);
2056 CREATE VIEW phone_number AS
2057 SELECT person, CASE WHEN NOT private THEN phone END AS phone
2058 FROM phone_data;
2059 GRANT SELECT ON phone_number TO assistant;
2060 </programlisting>
2061
2062 Nobody except that user (and the database superusers) can access the
2063 <literal>phone_data</literal> table. But because of the <command>GRANT</command>,
2064 the assistant can run a <command>SELECT</command> on the
2065 <literal>phone_number</literal> view. The rule system will rewrite the
2066 <command>SELECT</command> from <literal>phone_number</literal> into a
2067 <command>SELECT</command> from <literal>phone_data</literal>.
2068 Since the user is the owner of
2069 <literal>phone_number</literal> and therefore the owner of the rule, the
2070 read access to <literal>phone_data</literal> is now checked against the user's
2071 privileges and the query is permitted. The check for accessing
2072 <literal>phone_number</literal> is also performed, but this is done
2073 against the invoking user, so nobody but the user and the
2074 assistant can use it.
2075 </para>
2076
2077 <para>
2078 The privileges are checked rule by rule. So the assistant is for now the
2079 only one who can see the public phone numbers. But the assistant can set up
2080 another view and grant access to that to the public. Then, anyone
2081 can see the <literal>phone_number</literal> data through the assistant's view.
2082 What the assistant cannot do is to create a view that directly
2083 accesses <literal>phone_data</literal>. (Actually the assistant can, but it will not work since
2084 every access will be denied during the permission checks.)
2085 And as soon as the user notices that the assistant opened
2086 their <literal>phone_number</literal> view, the user can revoke the assistant's access. Immediately, any
2087 access to the assistant's view would fail.
2088 </para>
2089
2090 <para>
2091 One might think that this rule-by-rule checking is a security
2092 hole, but in fact it isn't. But if it did not work this way, the assistant
2093 could set up a table with the same columns as <literal>phone_number</literal> and
2094 copy the data to there once per day. Then it's the assistant's own data and
2095 the assistant can grant access to everyone they want. A
2096 <command>GRANT</command> command means, <quote>I trust you</quote>.
2097 If someone you trust does the thing above, it's time to
2098 think it over and then use <command>REVOKE</command>.
2099 </para>
2100
2101 <para>
2102 Note that while views can be used to hide the contents of certain
2103 columns using the technique shown above, they cannot be used to reliably
2104 conceal the data in unseen rows unless the
2105 <literal>security_barrier</literal> flag has been set. For example,
2106 the following view is insecure:
2107 <programlisting>
2108 CREATE VIEW phone_number AS
2109 SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
2110 </programlisting>
2111 This view might seem secure, since the rule system will rewrite any
2112 <command>SELECT</command> from <literal>phone_number</literal> into a
2113 <command>SELECT</command> from <literal>phone_data</literal> and add the
2114 qualification that only entries where <literal>phone</literal> does not begin
2115 with 412 are wanted. But if the user can create their own functions,
2116 it is not difficult to convince the planner to execute the user-defined
2117 function prior to the <function>NOT LIKE</function> expression.
2118 For example:
2119 <programlisting>
2120 CREATE FUNCTION tricky(text, text) RETURNS bool AS $$
2121 BEGIN
2122 RAISE NOTICE '% =&gt; %', $1, $2;
2123 RETURN true;
2124 END;
2125 $$ LANGUAGE plpgsql COST 0.0000000000000000000001;
2126
2127 SELECT * FROM phone_number WHERE tricky(person, phone);
2128 </programlisting>
2129 Every person and phone number in the <literal>phone_data</literal> table will be
2130 printed as a <literal>NOTICE</literal>, because the planner will choose to
2131 execute the inexpensive <function>tricky</function> function before the
2132 more expensive <function>NOT LIKE</function>. Even if the user is
2133 prevented from defining new functions, built-in functions can be used in
2134 similar attacks. (For example, most casting functions include their
2135 input values in the error messages they produce.)
2136 </para>
2137
2138 <para>
2139 Similar considerations apply to update rules. In the examples of
2140 the previous section, the owner of the tables in the example
2141 database could grant the privileges <literal>SELECT</literal>,
2142 <literal>INSERT</literal>, <literal>UPDATE</literal>, and <literal>DELETE</literal> on
2143 the <literal>shoelace</literal> view to someone else, but only
2144 <literal>SELECT</literal> on <literal>shoelace_log</literal>. The rule action to
2145 write log entries will still be executed successfully, and that
2146 other user could see the log entries. But they could not create fake
2147 entries, nor could they manipulate or remove existing ones. In this
2148 case, there is no possibility of subverting the rules by convincing
2149 the planner to alter the order of operations, because the only rule
2150 which references <literal>shoelace_log</literal> is an unqualified
2151 <literal>INSERT</literal>. This might not be true in more complex scenarios.
2152 </para>
2153
2154 <para>
2155 When it is necessary for a view to provide row-level security, the
2156 <literal>security_barrier</literal> attribute should be applied to
2157 the view. This prevents maliciously-chosen functions and operators from
2158 being passed values from rows until after the view has done its work. For
2159 example, if the view shown above had been created like this, it would
2160 be secure:
2161 <programlisting>
2162 CREATE VIEW phone_number WITH (security_barrier) AS
2163 SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
2164 </programlisting>
2165 Views created with the <literal>security_barrier</literal> may perform
2166 far worse than views created without this option. In general, there is
2167 no way to avoid this: the fastest possible plan must be rejected
2168 if it may compromise security. For this reason, this option is not
2169 enabled by default.
2170 </para>
2171
2172 <para>
2173 The query planner has more flexibility when dealing with functions that
2174 have no side effects. Such functions are referred to as <literal>LEAKPROOF</literal>, and
2175 include many simple, commonly used operators, such as many equality
2176 operators. The query planner can safely allow such functions to be evaluated
2177 at any point in the query execution process, since invoking them on rows
2178 invisible to the user will not leak any information about the unseen rows.
2179 Further, functions which do not take arguments or which are not passed any
2180 arguments from the security barrier view do not have to be marked as
2181 <literal>LEAKPROOF</literal> to be pushed down, as they never receive data
2182 from the view. In contrast, a function that might throw an error depending
2183 on the values received as arguments (such as one that throws an error in the
2184 event of overflow or division by zero) is not leakproof, and could provide
2185 significant information about the unseen rows if applied before the security
2186 view's row filters.
2187 </para>
2188
2189 <para>
2190 For example, an index scan cannot be selected for queries on security
2191 barrier views (or tables with row-level security policies) if an
2192 operator used in the <literal>WHERE</literal> clause is associated with the
2193 operator family of the index, but its underlying function is not marked
2194 <literal>LEAKPROOF</literal>. The <xref linkend="app-psql"/> program's
2195 <command><link linkend="app-psql-meta-command-dao">\dAo+</link></command>
2196 meta-command is useful to list operator families and determine which of
2197 their operators are marked as leakproof.
2198 </para>
2199
2200 <para>
2201 It is important to understand that even a view created with the
2202 <literal>security_barrier</literal> option is intended to be secure only
2203 in the limited sense that the contents of the invisible tuples will not be
2204 passed to possibly-insecure functions. The user may well have other means
2205 of making inferences about the unseen data; for example, they can see the
2206 query plan using <command>EXPLAIN</command>, or measure the run time of
2207 queries against the view. A malicious attacker might be able to infer
2208 something about the amount of unseen data, or even gain some information
2209 about the data distribution or most common values (since these things may
2210 affect the run time of the plan; or even, since they are also reflected in
2211 the optimizer statistics, the choice of plan). If these types of "covert
2212 channel" attacks are of concern, it is probably unwise to grant any access
2213 to the data at all.
2214 </para>
2215 </sect1>
2216
2217 <sect1 id="rules-status">
2218 <title>Rules and Command Status</title>
2219
2220 <para>
2221 The <productname>PostgreSQL</productname> server returns a command
2222 status string, such as <literal>INSERT 149592 1</literal>, for each
2223 command it receives. This is simple enough when there are no rules
2224 involved, but what happens when the query is rewritten by rules?
2225 </para>
2226
2227 <para>
2228 Rules affect the command status as follows:
2229
2230 <itemizedlist>
2231 <listitem>
2232 <para>
2233 If there is no unconditional <literal>INSTEAD</literal> rule for the query, then
2234 the originally given query will be executed, and its command
2235 status will be returned as usual. (But note that if there were
2236 any conditional <literal>INSTEAD</literal> rules, the negation of their qualifications
2237 will have been added to the original query. This might reduce the
2238 number of rows it processes, and if so the reported status will
2239 be affected.)
2240 </para>
2241 </listitem>
2242
2243 <listitem>
2244 <para>
2245 If there is any unconditional <literal>INSTEAD</literal> rule for the query, then
2246 the original query will not be executed at all. In this case,
2247 the server will return the command status for the last query
2248 that was inserted by an <literal>INSTEAD</literal> rule (conditional or
2249 unconditional) and is of the same command type
2250 (<command>INSERT</command>, <command>UPDATE</command>, or
2251 <command>DELETE</command>) as the original query. If no query
2252 meeting those requirements is added by any rule, then the
2253 returned command status shows the original query type and
2254 zeroes for the row-count and OID fields.
2255 </para>
2256 </listitem>
2257 </itemizedlist>
2258 </para>
2259
2260 <para>
2261 The programmer can ensure that any desired <literal>INSTEAD</literal> rule is the one
2262 that sets the command status in the second case, by giving it the
2263 alphabetically last rule name among the active rules, so that it
2264 gets applied last.
2265 </para>
2266 </sect1>
2267
2268 <sect1 id="rules-triggers">
2269 <title>Rules Versus Triggers</title>
2270
2271 <indexterm zone="rules-triggers">
2272 <primary>rule</primary>
2273 <secondary sortas="Trigger">compared with triggers</secondary>
2274 </indexterm>
2275
2276 <indexterm zone="rules-triggers">
2277 <primary>trigger</primary>
2278 <secondary sortas="Regeln">compared with rules</secondary>
2279 </indexterm>
2280
2281 <para>
2282 Many things that can be done using triggers can also be
2283 implemented using the <productname>PostgreSQL</productname>
2284 rule system. One of the things that cannot be implemented by
2285 rules are some kinds of constraints, especially foreign keys. It is possible
2286 to place a qualified rule that rewrites a command to <literal>NOTHING</literal>
2287 if the value of a column does not appear in another table.
2288 But then the data is silently thrown away and that's
2289 not a good idea. If checks for valid values are required,
2290 and in the case of an invalid value an error message should
2291 be generated, it must be done by a trigger.
2292 </para>
2293
2294 <para>
2295 In this chapter, we focused on using rules to update views. All of
2296 the update rule examples in this chapter can also be implemented
2297 using <literal>INSTEAD OF</literal> triggers on the views. Writing such
2298 triggers is often easier than writing rules, particularly if complex
2299 logic is required to perform the update.
2300 </para>
2301
2302 <para>
2303 For the things that can be implemented by both, which is best
2304 depends on the usage of the database.
2305 A trigger is fired once for each affected row. A rule modifies
2306 the query or generates an additional query. So if many
2307 rows are affected in one statement, a rule issuing one extra
2308 command is likely to be faster than a trigger that is
2309 called for every single row and must re-determine what to do
2310 many times. However, the trigger approach is conceptually far
2311 simpler than the rule approach, and is easier for novices to get right.
2312 </para>
2313
2314 <para>
2315 Here we show an example of how the choice of rules versus triggers
2316 plays out in one situation. There are two tables:
2317
2318 <programlisting>
2319 CREATE TABLE computer (
2320 hostname text, -- indexed
2321 manufacturer text -- indexed
2322 );
2323
2324 CREATE TABLE software (
2325 software text, -- indexed
2326 hostname text -- indexed
2327 );
2328 </programlisting>
2329
2330 Both tables have many thousands of rows and the indexes on
2331 <structfield>hostname</structfield> are unique. The rule or trigger should
2332 implement a constraint that deletes rows from <literal>software</literal>
2333 that reference a deleted computer. The trigger would use this command:
2334
2335 <programlisting>
2336 DELETE FROM software WHERE hostname = $1;
2337 </programlisting>
2338
2339 Since the trigger is called for each individual row deleted from
2340 <literal>computer</literal>, it can prepare and save the plan for this
2341 command and pass the <structfield>hostname</structfield> value in the
2342 parameter. The rule would be written as:
2343
2344 <programlisting>
2345 CREATE RULE computer_del AS ON DELETE TO computer
2346 DO DELETE FROM software WHERE hostname = OLD.hostname;
2347 </programlisting>
2348 </para>
2349
2350 <para>
2351 Now we look at different types of deletes. In the case of a:
2352
2353 <programlisting>
2354 DELETE FROM computer WHERE hostname = 'mypc.local.net';
2355 </programlisting>
2356
2357 the table <literal>computer</literal> is scanned by index (fast), and the
2358 command issued by the trigger would also use an index scan (also fast).
2359 The extra command from the rule would be:
2360
2361 <programlisting>
2362 DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
2363 AND software.hostname = computer.hostname;
2364 </programlisting>
2365
2366 Since there are appropriate indexes set up, the planner
2367 will create a plan of
2368
2369 <literallayout class="monospaced">
2370 Nestloop
2371 -&gt; Index Scan using comp_hostidx on computer
2372 -&gt; Index Scan using soft_hostidx on software
2373 </literallayout>
2374
2375 So there would be not that much difference in speed between
2376 the trigger and the rule implementation.
2377 </para>
2378
2379 <para>
2380 With the next delete we want to get rid of all the 2000 computers
2381 where the <structfield>hostname</structfield> starts with
2382 <literal>old</literal>. There are two possible commands to do that. One
2383 is:
2384
2385 <programlisting>
2386 DELETE FROM computer WHERE hostname &gt;= 'old'
2387 AND hostname &lt; 'ole'
2388 </programlisting>
2389
2390 The command added by the rule will be:
2391
2392 <programlisting>
2393 DELETE FROM software WHERE computer.hostname &gt;= 'old' AND computer.hostname &lt; 'ole'
2394 AND software.hostname = computer.hostname;
2395 </programlisting>
2396
2397 with the plan
2398
2399 <literallayout class="monospaced">
2400 Hash Join
2401 -&gt; Seq Scan on software
2402 -&gt; Hash
2403 -&gt; Index Scan using comp_hostidx on computer
2404 </literallayout>
2405
2406 The other possible command is:
2407
2408 <programlisting>
2409 DELETE FROM computer WHERE hostname ~ '^old';
2410 </programlisting>
2411
2412 which results in the following executing plan for the command
2413 added by the rule:
2414
2415 <literallayout class="monospaced">
2416 Nestloop
2417 -&gt; Index Scan using comp_hostidx on computer
2418 -&gt; Index Scan using soft_hostidx on software
2419 </literallayout>
2420
2421 This shows, that the planner does not realize that the
2422 qualification for <structfield>hostname</structfield> in
2423 <literal>computer</literal> could also be used for an index scan on
2424 <literal>software</literal> when there are multiple qualification
2425 expressions combined with <literal>AND</literal>, which is what it does
2426 in the regular-expression version of the command. The trigger will
2427 get invoked once for each of the 2000 old computers that have to be
2428 deleted, and that will result in one index scan over
2429 <literal>computer</literal> and 2000 index scans over
2430 <literal>software</literal>. The rule implementation will do it with two
2431 commands that use indexes. And it depends on the overall size of
2432 the table <literal>software</literal> whether the rule will still be faster in the
2433 sequential scan situation. 2000 command executions from the trigger over the SPI
2434 manager take some time, even if all the index blocks will soon be in the cache.
2435 </para>
2436
2437 <para>
2438 The last command we look at is:
2439
2440 <programlisting>
2441 DELETE FROM computer WHERE manufacturer = 'bim';
2442 </programlisting>
2443
2444 Again this could result in many rows to be deleted from
2445 <literal>computer</literal>. So the trigger will again run many commands
2446 through the executor. The command generated by the rule will be:
2447
2448 <programlisting>
2449 DELETE FROM software WHERE computer.manufacturer = 'bim'
2450 AND software.hostname = computer.hostname;
2451 </programlisting>
2452
2453 The plan for that command will again be the nested loop over two
2454 index scans, only using a different index on <literal>computer</literal>:
2455
2456 <programlisting>
2457 Nestloop
2458 -&gt; Index Scan using comp_manufidx on computer
2459 -&gt; Index Scan using soft_hostidx on software
2460 </programlisting>
2461
2462 In any of these cases, the extra commands from the rule system
2463 will be more or less independent from the number of affected rows
2464 in a command.
2465 </para>
2466
2467 <para>
2468 The summary is, rules will only be significantly slower than
2469 triggers if their actions result in large and badly qualified
2470 joins, a situation where the planner fails.
2471 </para>
2472 </sect1>
2473
2474 </chapter>