1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter34.The Rule System</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="server-programming.html" title="PartV.Server Programming">
<link rel="prev" href="trigger-example.html" title="33.4.A Complete Example">
<link rel="next" href="rules-views.html" title="34.2.Views and the Rule System">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter" lang="en" id="rules">
<div class="titlepage"><div><div><h2 class="title">
<a name="rules"></a>Chapter34.The Rule System</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="rules.html#querytree">34.1. The Query Tree</a></span></dt>
<dt><span class="sect1"><a href="rules-views.html">34.2. Views and the Rule System</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="rules-views.html#rules-select">34.2.1. How <code class="command">SELECT</code> Rules Work</a></span></dt>
<dt><span class="sect2"><a href="rules-views.html#id717045">34.2.2. View Rules in Non-<code class="command">SELECT</code> Statements</a></span></dt>
<dt><span class="sect2"><a href="rules-views.html#id717372">34.2.3. The Power of Views in <span class="productname">PostgreSQL</span></a></span></dt>
<dt><span class="sect2"><a href="rules-views.html#rules-views-update">34.2.4. Updating a View</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="rules-update.html">34.3. Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code></a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="rules-update.html#id717622">34.3.1. How Update Rules Work</a></span></dt>
<dt><span class="sect2"><a href="rules-update.html#rules-update-views">34.3.2. Cooperation with Views</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="rules-privileges.html">34.4. Rules and Privileges</a></span></dt>
<dt><span class="sect1"><a href="rules-status.html">34.5. Rules and Command Status</a></span></dt>
<dt><span class="sect1"><a href="rules-triggers.html">34.6. Rules versus Triggers</a></span></dt>
</dl>
</div>
<a name="id715578"></a><p> This chapter discusses the rule system in
<span class="productname">PostgreSQL</span>. Production rule systems
are conceptually simple, but there are many subtle points
involved in actually using them.</p>
<p> Some other database systems define active database rules, which
are usually stored procedures and triggers. In
<span class="productname">PostgreSQL</span>, these can be implemented
using functions and triggers as well.</p>
<p> The rule system (more precisely speaking, the query rewrite rule
system) is totally different from stored procedures and triggers.
It modifies queries to take rules into consideration, and then
passes the modified query to the query planner for planning and
execution. It is very powerful, and can be used for many things
such as query language procedures, views, and versions. The
theoretical foundations and the power of this rule system are
also discussed in [<a href="biblio.html#ston90b" title="[ston90b]">ston90b</a>] and [<a href="biblio.html#ong90" title="[ong90]">ong90</a>].</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="querytree"></a>34.1.The Query Tree</h2></div></div></div>
<a name="id715646"></a><p> To understand how the rule system works it is necessary to know
when it is invoked and what its input and results are.</p>
<p> The rule system is located between the parser and the planner.
It takes the output of the parser, one query tree, and the user-defined
rewrite rules, which are also
query trees with some extra information, and creates zero or more
query trees as result. So its input and output are always things
the parser itself could have produced and thus, anything it sees
is basically representable as an <acronym class="acronym">SQL</acronym> statement.</p>
<p> Now what is a query tree? It is an internal representation of an
<acronym class="acronym">SQL</acronym> statement where the single parts that it is
built from are stored separately. These query trees can be shown
in the server log if you set the configuration parameters
<code class="varname">debug_print_parse</code>,
<code class="varname">debug_print_rewritten</code>, or
<code class="varname">debug_print_plan</code>. The rule actions are also
stored as query trees, in the system catalog
<code class="structname">pg_rewrite</code>. They are not formatted like
the log output, but they contain exactly the same information.</p>
<p> Reading a raw query tree requires some experience. But since
<acronym class="acronym">SQL</acronym> representations of query trees are
sufficient to understand the rule system, this chapter will not
teach how to read them.</p>
<p> When reading the <acronym class="acronym">SQL</acronym> representations of the
query trees in this chapter it is necessary to be able to identify
the parts the statement is broken into when it is in the query tree
structure. The parts of a query tree are
</p>
<div class="variablelist"><dl>
<dt><span class="term"> the command type
</span></dt>
<dd><p> This is a simple value telling which command
(<code class="command">SELECT</code>, <code class="command">INSERT</code>,
<code class="command">UPDATE</code>, <code class="command">DELETE</code>) produced
the query tree.
</p></dd>
<dt><span class="term"> the range table
</span></dt>
<dd>
<p> The range table is a list of relations that are used in the query.
In a <code class="command">SELECT</code> statement these are the relations given after
the <code class="literal">FROM</code> key word.
</p>
<p> Every range table entry identifies a table or view and tells
by which name it is called in the other parts of the query.
In the query tree, the range table entries are referenced by
number rather than by name, so here it doesn't matter if there
are duplicate names as it would in an <acronym class="acronym">SQL</acronym>
statement. This can happen after the range tables of rules
have been merged in. The examples in this chapter will not have
this situation.
</p>
</dd>
<dt><span class="term"> the result relation
</span></dt>
<dd>
<p> This is an index into the range table that identifies the
relation where the results of the query go.
</p>
<p> <code class="command">SELECT</code> queries normally don't have a result
relation. The special case of a <code class="command">SELECT INTO</code> is
mostly identical to a <code class="command">CREATE TABLE</code> followed by a
<code class="literal">INSERT ... SELECT</code> and is not discussed
separately here.
</p>
<p> For <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and
<code class="command">DELETE</code> commands, the result relation is the table
(or view!) where the changes are to take effect.
</p>
</dd>
<dt><span class="term"> the target list
</span></dt>
<dd>
<p> The target list is a list of expressions that define the
result of the query. In the case of a
<code class="command">SELECT</code>, these expressions are the ones that
build the final output of the query. They correspond to the
expressions between the key words <code class="command">SELECT</code>
and <code class="command">FROM</code>. (<code class="literal">*</code> is just an
abbreviation for all the column names of a relation. It is
expanded by the parser into the individual columns, so the
rule system never sees it.)
</p>
<p> <code class="command">DELETE</code> commands don't need a target list
because they don't produce any result. In fact, the planner will
add a special <acronym class="acronym">CTID</acronym> entry to the empty target list, but
this is after the rule system and will be discussed later; for the
rule system, the target list is empty.
</p>
<p> For <code class="command">INSERT</code> commands, the target list describes
the new rows that should go into the result relation. It consists of the
expressions in the <code class="literal">VALUES</code> clause or the ones from the
<code class="command">SELECT</code> clause in <code class="literal">INSERT
... SELECT</code>. The first step of the rewrite process adds
target list entries for any columns that were not assigned to by
the original command but have defaults. Any remaining columns (with
neither a given value nor a default) will be filled in by the
planner with a constant null expression.
</p>
<p> For <code class="command">UPDATE</code> commands, the target list
describes the new rows that should replace the old ones. In the
rule system, it contains just the expressions from the <code class="literal">SET
column = expression</code> part of the command. The planner will handle
missing columns by inserting expressions that copy the values from
the old row into the new one. And it will add the special
<acronym class="acronym">CTID</acronym> entry just as for <code class="command">DELETE</code>, too.
</p>
<p> Every entry in the target list contains an expression that can
be a constant value, a variable pointing to a column of one
of the relations in the range table, a parameter, or an expression
tree made of function calls, constants, variables, operators, etc.
</p>
</dd>
<dt><span class="term"> the qualification
</span></dt>
<dd><p> The query's qualification is an expression much like one of
those contained in the target list entries. The result value of
this expression is a Boolean that tells whether the operation
(<code class="command">INSERT</code>, <code class="command">UPDATE</code>,
<code class="command">DELETE</code>, or <code class="command">SELECT</code>) for the
final result row should be executed or not. It corresponds to the <code class="literal">WHERE</code> clause
of an <acronym class="acronym">SQL</acronym> statement.
</p></dd>
<dt><span class="term"> the join tree
</span></dt>
<dd><p> The query's join tree shows the structure of the <code class="literal">FROM</code> clause.
For a simple query like <code class="literal">SELECT ... FROM a, b, c</code>, the join tree is just
a list of the <code class="literal">FROM</code> items, because we are allowed to join them in
any order. But when <code class="literal">JOIN</code> expressions, particularly outer joins,
are used, we have to join in the order shown by the joins.
In that case, the join tree shows the structure of the <code class="literal">JOIN</code> expressions. The
restrictions associated with particular <code class="literal">JOIN</code> clauses (from <code class="literal">ON</code> or
<code class="literal">USING</code> expressions) are stored as qualification expressions attached
to those join-tree nodes. It turns out to be convenient to store
the top-level <code class="literal">WHERE</code> expression as a qualification attached to the
top-level join-tree item, too. So really the join tree represents
both the <code class="literal">FROM</code> and <code class="literal">WHERE</code> clauses of a <code class="command">SELECT</code>.
</p></dd>
<dt><span class="term"> the others
</span></dt>
<dd><p> The other parts of the query tree like the <code class="literal">ORDER BY</code>
clause aren't of interest here. The rule system
substitutes some entries there while applying rules, but that
doesn't have much to do with the fundamentals of the rule
system.
</p></dd>
</dl></div>
</div>
</div></body>
</html>
|
