| 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 >= rsh.slminlen_cm |
| 416 |
AND rsl.sl_len_cm <= 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> — 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 >= 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 >= 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 >= rsh.slminlen_cm |
| 595 |
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready |
| 596 |
WHERE shoe_ready.total_avail >= 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 >= rsh.slminlen_cm |
| 632 |
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready |
| 633 |
WHERE shoe_ready.total_avail >= 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 < 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 |
-> 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 |
-> 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 <-> '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 |
-> 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 <-> 'caterpiler'::text)) |
| 1080 |
Sort Method: top-N heapsort Memory: 25kB |
| 1081 |
-> 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 |
-> 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 <-> '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 <> 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 <> 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 <> 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 <> 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> <> 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 <> <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 <> 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> <> 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 <> 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 <> 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 — 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) <> 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 <> 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 |
-> Merge Join |
| 1894 |
-> Seq Scan |
| 1895 |
-> Sort |
| 1896 |
-> Seq Scan on s |
| 1897 |
-> Seq Scan |
| 1898 |
-> Sort |
| 1899 |
-> Seq Scan on shoelace_arrive |
| 1900 |
-> 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 |
-> Seq Scan |
| 1908 |
-> Sort |
| 1909 |
-> Seq Scan on s |
| 1910 |
-> Seq Scan |
| 1911 |
-> Sort |
| 1912 |
-> 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 '% => %', $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 |
-> Index Scan using comp_hostidx on computer |
| 2372 |
-> 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 >= 'old' |
| 2387 |
AND hostname < 'ole' |
| 2388 |
</programlisting> |
| 2389 |
|
| 2390 |
The command added by the rule will be: |
| 2391 |
|
| 2392 |
<programlisting> |
| 2393 |
DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole' |
| 2394 |
AND software.hostname = computer.hostname; |
| 2395 |
</programlisting> |
| 2396 |
|
| 2397 |
with the plan |
| 2398 |
|
| 2399 |
<literallayout class="monospaced"> |
| 2400 |
Hash Join |
| 2401 |
-> Seq Scan on software |
| 2402 |
-> Hash |
| 2403 |
-> 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 |
-> Index Scan using comp_hostidx on computer |
| 2418 |
-> 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 |
-> Index Scan using comp_manufidx on computer |
| 2459 |
-> 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> |