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
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>40.7. Rules Versus Triggers</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="rules-status.html" title="40.6. Rules and Command Status" /><link rel="next" href="xplang.html" title="Chapter 41. Procedural Languages" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">40.7. Rules Versus Triggers</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-status.html" title="40.6. Rules and Command Status">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><th width="60%" align="center">Chapter 40. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xplang.html" title="Chapter 41. Procedural Languages">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="RULES-TRIGGERS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">40.7. Rules Versus Triggers</h2></div></div></div><a id="id-1.8.6.12.2" class="indexterm"></a><a id="id-1.8.6.12.3" class="indexterm"></a><p>
Many things that can be done using triggers can also be
implemented using the <span class="productname">PostgreSQL</span>
rule system. One of the things that cannot be implemented by
rules are some kinds of constraints, especially foreign keys. It is possible
to place a qualified rule that rewrites a command to <code class="literal">NOTHING</code>
if the value of a column does not appear in another table.
But then the data is silently thrown away and that's
not a good idea. If checks for valid values are required,
and in the case of an invalid value an error message should
be generated, it must be done by a trigger.
</p><p>
In this chapter, we focused on using rules to update views. All of
the update rule examples in this chapter can also be implemented
using <code class="literal">INSTEAD OF</code> triggers on the views. Writing such
triggers is often easier than writing rules, particularly if complex
logic is required to perform the update.
</p><p>
For the things that can be implemented by both, which is best
depends on the usage of the database.
A trigger is fired once for each affected row. A rule modifies
the query or generates an additional query. So if many
rows are affected in one statement, a rule issuing one extra
command is likely to be faster than a trigger that is
called for every single row and must re-determine what to do
many times. However, the trigger approach is conceptually far
simpler than the rule approach, and is easier for novices to get right.
</p><p>
Here we show an example of how the choice of rules versus triggers
plays out in one situation. There are two tables:
</p><pre class="programlisting">
CREATE TABLE computer (
hostname text, -- indexed
manufacturer text -- indexed
);
CREATE TABLE software (
software text, -- indexed
hostname text -- indexed
);
</pre><p>
Both tables have many thousands of rows and the indexes on
<code class="structfield">hostname</code> are unique. The rule or trigger should
implement a constraint that deletes rows from <code class="literal">software</code>
that reference a deleted computer. The trigger would use this command:
</p><pre class="programlisting">
DELETE FROM software WHERE hostname = $1;
</pre><p>
Since the trigger is called for each individual row deleted from
<code class="literal">computer</code>, it can prepare and save the plan for this
command and pass the <code class="structfield">hostname</code> value in the
parameter. The rule would be written as:
</p><pre class="programlisting">
CREATE RULE computer_del AS ON DELETE TO computer
DO DELETE FROM software WHERE hostname = OLD.hostname;
</pre><p>
</p><p>
Now we look at different types of deletes. In the case of a:
</p><pre class="programlisting">
DELETE FROM computer WHERE hostname = 'mypc.local.net';
</pre><p>
the table <code class="literal">computer</code> is scanned by index (fast), and the
command issued by the trigger would also use an index scan (also fast).
The extra command from the rule would be:
</p><pre class="programlisting">
DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
AND software.hostname = computer.hostname;
</pre><p>
Since there are appropriate indexes set up, the planner
will create a plan of
</p><pre class="literallayout">
Nestloop
-> Index Scan using comp_hostidx on computer
-> Index Scan using soft_hostidx on software
</pre><p>
So there would be not that much difference in speed between
the trigger and the rule implementation.
</p><p>
With the next delete we want to get rid of all the 2000 computers
where the <code class="structfield">hostname</code> starts with
<code class="literal">old</code>. There are two possible commands to do that. One
is:
</p><pre class="programlisting">
DELETE FROM computer WHERE hostname >= 'old'
AND hostname < 'ole'
</pre><p>
The command added by the rule will be:
</p><pre class="programlisting">
DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'
AND software.hostname = computer.hostname;
</pre><p>
with the plan
</p><pre class="literallayout">
Hash Join
-> Seq Scan on software
-> Hash
-> Index Scan using comp_hostidx on computer
</pre><p>
The other possible command is:
</p><pre class="programlisting">
DELETE FROM computer WHERE hostname ~ '^old';
</pre><p>
which results in the following executing plan for the command
added by the rule:
</p><pre class="literallayout">
Nestloop
-> Index Scan using comp_hostidx on computer
-> Index Scan using soft_hostidx on software
</pre><p>
This shows, that the planner does not realize that the
qualification for <code class="structfield">hostname</code> in
<code class="literal">computer</code> could also be used for an index scan on
<code class="literal">software</code> when there are multiple qualification
expressions combined with <code class="literal">AND</code>, which is what it does
in the regular-expression version of the command. The trigger will
get invoked once for each of the 2000 old computers that have to be
deleted, and that will result in one index scan over
<code class="literal">computer</code> and 2000 index scans over
<code class="literal">software</code>. The rule implementation will do it with two
commands that use indexes. And it depends on the overall size of
the table <code class="literal">software</code> whether the rule will still be faster in the
sequential scan situation. 2000 command executions from the trigger over the SPI
manager take some time, even if all the index blocks will soon be in the cache.
</p><p>
The last command we look at is:
</p><pre class="programlisting">
DELETE FROM computer WHERE manufacturer = 'bim';
</pre><p>
Again this could result in many rows to be deleted from
<code class="literal">computer</code>. So the trigger will again run many commands
through the executor. The command generated by the rule will be:
</p><pre class="programlisting">
DELETE FROM software WHERE computer.manufacturer = 'bim'
AND software.hostname = computer.hostname;
</pre><p>
The plan for that command will again be the nested loop over two
index scans, only using a different index on <code class="literal">computer</code>:
</p><pre class="programlisting">
Nestloop
-> Index Scan using comp_manufidx on computer
-> Index Scan using soft_hostidx on software
</pre><p>
In any of these cases, the extra commands from the rule system
will be more or less independent from the number of affected rows
in a command.
</p><p>
The summary is, rules will only be significantly slower than
triggers if their actions result in large and badly qualified
joins, a situation where the planner fails.
</p></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules-status.html" title="40.6. Rules and Command Status">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xplang.html" title="Chapter 41. Procedural Languages">Next</a></td></tr><tr><td width="40%" align="left" valign="top">40.6. Rules and Command Status </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 41. Procedural Languages</td></tr></table></div></body></html>
|
