Skip to content

Commit 7fc0f06

Browse files
tglsfdctglsfdc
committed
Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression to be
checked to determine whether the trigger should be fired. For BEFORE triggers this is mostly a matter of spec compliance; but for AFTER triggers it can provide a noticeable performance improvement, since queuing of a deferred trigger event and re-fetching of the row(s) at end of statement can be short-circuited if the trigger does not need to be fired. Takahiro Itagaki, reviewed by KaiGai Kohei.
1 parent 201a45c commit 7fc0f06

File tree

27 files changed

+783
-100
lines changed

27 files changed

+783
-100
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.210 2009/10/14 22:14:21 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.211 2009/11/20 20:38:09 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -4756,6 +4756,15 @@
47564756
<entry></entry>
47574757
<entry>Argument strings to pass to trigger, each NULL-terminated</entry>
47584758
</row>
4759+
4760+
<row>
4761+
<entry><structfield>tgqual</structfield></entry>
4762+
<entry><type>text</type></entry>
4763+
<entry></entry>
4764+
<entry>Expression tree (in <function>nodeToString()</function>
4765+
representation) for the trigger's <literal>WHEN</> condition, or NULL
4766+
if none</entry>
4767+
</row>
47594768
</tbody>
47604769
</tgroup>
47614770
</table>

‎doc/src/sgml/ref/create_constraint.sgml

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_constraint.sgml,v 1.20 2009/09/19 10:23:26 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_constraint.sgml,v 1.21 2009/11/20 20:38:09 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -27,6 +27,7 @@ CREATE CONSTRAINT TRIGGER <replaceable class="parameter">name</replaceable>
2727
[ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
2828
{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
2929
FOR EACH ROW
30+
[ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
3031
EXECUTE PROCEDURE <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> )
3132
</synopsis>
3233
</refsynopsisdiv>
@@ -109,6 +110,22 @@ CREATE CONSTRAINT TRIGGER <replaceable class="parameter">name</replaceable>
109110
</listitem>
110111
</varlistentry>
111112

113+
<varlistentry>
114+
<term><replaceable class="parameter">condition</replaceable></term>
115+
<listitem>
116+
<para>
117+
A Boolean expression that determines whether the trigger function
118+
will actually be executed. This acts the same as in <xref
119+
linkend="SQL-CREATETRIGGER" endterm="SQL-CREATETRIGGER-TITLE">.
120+
Note in particular that evaluation of the <literal>WHEN</>
121+
condition is not deferred, but occurs immediately after the row
122+
update operation is performed. If the condition does not evaluate
123+
to <literal>true</> then the trigger is not queued for deferred
124+
execution.
125+
</para>
126+
</listitem>
127+
</varlistentry>
128+
112129
<varlistentry>
113130
<term><replaceable class="PARAMETER">function_name</replaceable></term>
114131
<listitem>

‎doc/src/sgml/ref/create_trigger.sgml

Lines changed: 108 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.51 2009/10/14 22:14:21 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.52 2009/11/20 20:38:09 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -23,6 +23,7 @@ PostgreSQL documentation
2323
<synopsis>
2424
CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
2525
ON <replaceable class="PARAMETER">table</replaceable> [ FOR [ EACH ] { ROW | STATEMENT } ]
26+
[ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
2627
EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
2728
</synopsis>
2829
</refsynopsisdiv>
@@ -72,6 +73,16 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
7273
<literal>FOR EACH STATEMENT</literal>.
7374
</para>
7475

76+
<para>
77+
Also, a trigger definition can specify a boolean <literal>WHEN</>
78+
condition, which will be tested to see whether the trigger should
79+
be fired. In row-level triggers the <literal>WHEN</> condition can
80+
examine the old and/or new values of columns of the row. Statement-level
81+
triggers can also have <literal>WHEN</> conditions, although the feature
82+
is not so useful for them since the condition cannot refer to any values
83+
in the table.
84+
</para>
85+
7586
<para>
7687
If multiple triggers of the same kind are defined for the same event,
7788
they will be fired in alphabetical order by name.
@@ -159,6 +170,31 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
159170
</listitem>
160171
</varlistentry>
161172

173+
<varlistentry>
174+
<term><replaceable class="parameter">condition</replaceable></term>
175+
<listitem>
176+
<para>
177+
A Boolean expression that determines whether the trigger function
178+
will actually be executed. If <literal>WHEN</> is specified, the
179+
function will only be called if the <replaceable
180+
class="parameter">condition</replaceable> returns <literal>true</>.
181+
In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</>
182+
condition can refer to columns of the old and/or new row values
183+
by writing <literal>OLD.<replaceable
184+
class="parameter">column_name</replaceable></literal> or
185+
<literal>NEW.<replaceable
186+
class="parameter">column_name</replaceable></literal> respectively.
187+
Of course, <literal>INSERT</> triggers cannot refer to <literal>OLD</>
188+
and <literal>DELETE</> triggers cannot refer to <literal>NEW</>.
189+
</para>
190+
191+
<para>
192+
Currently, <literal>WHEN</literal> expressions cannot contain
193+
subqueries.
194+
</para>
195+
</listitem>
196+
</varlistentry>
197+
162198
<varlistentry>
163199
<term><replaceable class="parameter">function_name</replaceable></term>
164200
<listitem>
@@ -213,6 +249,29 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
213249
value did not change.
214250
</para>
215251

252+
<para>
253+
In a <literal>BEFORE</> trigger, the <literal>WHEN</> condition is
254+
evaluated just before the function is or would be executed, so using
255+
<literal>WHEN</> is not materially different from testing the same
256+
condition at the beginning of the trigger function. Note in particular
257+
that the <literal>NEW</> row seen by the condition is the current value,
258+
as possibly modified by earlier triggers. Also, a <literal>BEFORE</>
259+
trigger's <literal>WHEN</> condition is not allowed to examine the
260+
system columns of the <literal>NEW</> row (such as <literal>oid</>),
261+
because those won't have been set yet.
262+
</para>
263+
264+
<para>
265+
In an <literal>AFTER</> trigger, the <literal>WHEN</> condition is
266+
evaluated just after the row update occurs, and it determines whether an
267+
event is queued to fire the trigger at the end of statement. So when an
268+
<literal>AFTER</> trigger's <literal>WHEN</> condition does not return
269+
true, it is not necessary to queue an event nor to re-fetch the row at end
270+
of statement. This can result in significant speedups in statements that
271+
modify many rows, if the trigger only needs to be fired for a few of the
272+
rows.
273+
</para>
274+
216275
<para>
217276
In <productname>PostgreSQL</productname> versions before 7.3, it was
218277
necessary to declare trigger functions as returning the placeholder
@@ -223,11 +282,56 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
223282
</para>
224283
</refsect1>
225284

226-
<refsect1 id="R1-SQL-CREATETRIGGER-2">
285+
<refsect1 id="SQL-CREATETRIGGER-examples">
227286
<title>Examples</title>
228287

229288
<para>
230-
<xref linkend="trigger-example"> contains a complete example.
289+
Execute the function <function>check_account_update</> whenever
290+
a row of the table <literal>accounts</> is about to be updated:
291+
292+
<programlisting>
293+
CREATE TRIGGER check_update
294+
BEFORE UPDATE ON accounts
295+
FOR EACH ROW
296+
EXECUTE PROCEDURE check_account_update();
297+
</programlisting>
298+
299+
The same, but only execute the function if column <literal>balance</>
300+
is specified as a target in the <command>UPDATE</> command:
301+
302+
<programlisting>
303+
CREATE TRIGGER check_update
304+
BEFORE UPDATE OF balance ON accounts
305+
FOR EACH ROW
306+
EXECUTE PROCEDURE check_account_update();
307+
</programlisting>
308+
309+
This form only executes the function if column <literal>balance</>
310+
has in fact changed value:
311+
312+
<programlisting>
313+
CREATE TRIGGER check_update
314+
BEFORE UPDATE ON accounts
315+
FOR EACH ROW
316+
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
317+
EXECUTE PROCEDURE check_account_update();
318+
</programlisting>
319+
320+
Call a function to log updates of <literal>accounts</>, but only if
321+
something changed:
322+
323+
<programlisting>
324+
CREATE TRIGGER log_update
325+
AFTER UPDATE ON accounts
326+
FOR EACH ROW
327+
WHEN (OLD.* IS DISTINCT FROM NEW.*)
328+
EXECUTE PROCEDURE log_account_update();
329+
</programlisting>
330+
</para>
331+
332+
<para>
333+
<xref linkend="trigger-example"> contains a complete example of a trigger
334+
function written in C.
231335
</para>
232336
</refsect1>
233337

@@ -258,7 +362,7 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
258362
<productname>PostgreSQL</productname> only allows the execution
259363
of a user-defined function for the triggered action. The standard
260364
allows the execution of a number of other SQL commands, such as
261-
<command>CREATE TABLE</command> as the triggered action. This
365+
<command>CREATE TABLE</command>, as the triggered action. This
262366
limitation is not hard to work around by creating a user-defined
263367
function that executes the desired commands.
264368
</para>

‎doc/src/sgml/trigger.sgml

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.59 2009/10/14 22:14:21 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.60 2009/11/20 20:38:09 tgl Exp $ -->
22

33
<chapter id="triggers">
44
<title>Triggers</title>
@@ -140,6 +140,25 @@
140140
triggers are not fired.
141141
</para>
142142

143+
<para>
144+
A trigger definition can also specify a boolean <literal>WHEN</>
145+
condition, which will be tested to see whether the trigger should
146+
be fired. In row-level triggers the <literal>WHEN</> condition can
147+
examine the old and/or new values of columns of the row. (Statement-level
148+
triggers can also have <literal>WHEN</> conditions, although the feature
149+
is not so useful for them.) In a before trigger, the <literal>WHEN</>
150+
condition is evaluated just before the function is or would be executed,
151+
so using <literal>WHEN</> is not materially different from testing the
152+
same condition at the beginning of the trigger function. However, in
153+
an after trigger, the <literal>WHEN</> condition is evaluated just after
154+
the row update occurs, and it determines whether an event is queued to
155+
fire the trigger at the end of statement. So when an after trigger's
156+
<literal>WHEN</> condition does not return true, it is not necessary
157+
to queue an event nor to re-fetch the row at end of statement. This
158+
can result in significant speedups in statements that modify many
159+
rows, if the trigger only needs to be fired for a few of the rows.
160+
</para>
161+
143162
<para>
144163
Typically, row before triggers are used for checking or
145164
modifying the data that will be inserted or updated. For example,
@@ -497,6 +516,7 @@ typedef struct Trigger
497516
int16 tgnattr;
498517
int16 *tgattr;
499518
char **tgargs;
519+
char *tgqual;
500520
} Trigger;
501521
</programlisting>
502522

‎src/backend/catalog/index.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.323 2009/10/14 22:14:21 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.324 2009/11/20 20:38:09 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -793,12 +793,13 @@ index_create(Oid heapRelationId,
793793
trigger->row = true;
794794
trigger->events = TRIGGER_TYPE_INSERT | TRIGGER_TYPE_UPDATE;
795795
trigger->columns = NIL;
796+
trigger->whenClause = NULL;
796797
trigger->isconstraint = true;
797798
trigger->deferrable = true;
798799
trigger->initdeferred = initdeferred;
799800
trigger->constrrel = NULL;
800801

801-
(void) CreateTrigger(trigger, conOid, indexRelationId,
802+
(void) CreateTrigger(trigger, NULL, conOid, indexRelationId,
802803
isprimary ? "PK_ConstraintTrigger" :
803804
"Unique_ConstraintTrigger",
804805
false);

‎src/backend/commands/copy.c

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.317 2009/09/21 20:10:21 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.318 2009/11/20 20:38:10 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1799,8 +1799,12 @@ CopyFrom(CopyState cstate)
17991799
resultRelInfo->ri_RelationDesc = cstate->rel;
18001800
resultRelInfo->ri_TrigDesc = CopyTriggerDesc(cstate->rel->trigdesc);
18011801
if (resultRelInfo->ri_TrigDesc)
1802+
{
18021803
resultRelInfo->ri_TrigFunctions = (FmgrInfo *)
18031804
palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(FmgrInfo));
1805+
resultRelInfo->ri_TrigWhenExprs = (List **)
1806+
palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(List *));
1807+
}
18041808
resultRelInfo->ri_TrigInstrument = NULL;
18051809

18061810
ExecOpenIndices(resultRelInfo);
@@ -1810,7 +1814,8 @@ CopyFrom(CopyState cstate)
18101814
estate->es_result_relation_info = resultRelInfo;
18111815

18121816
/* Set up a tuple slot too */
1813-
slot = MakeSingleTupleTableSlot(tupDesc);
1817+
slot = ExecInitExtraTupleSlot(estate);
1818+
ExecSetSlotDescriptor(slot, tupDesc);
18141819

18151820
econtext = GetPerTupleExprContext(estate);
18161821

@@ -2198,7 +2203,7 @@ CopyFrom(CopyState cstate)
21982203
pfree(defmap);
21992204
pfree(defexprs);
22002205

2201-
ExecDropSingleTupleTableSlot(slot);
2206+
ExecResetTupleTable(estate->es_tupleTable, false);
22022207

22032208
ExecCloseIndices(resultRelInfo);
22042209

‎src/backend/commands/tablecmds.c

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.305 2009/11/04 12:24:23 heikki Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.306 2009/11/20 20:38:10 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -5403,7 +5403,7 @@ validateForeignKeyConstraint(Constraint *fkconstraint,
54035403
trig.tgconstraint = constraintOid;
54045404
trig.tgdeferrable = FALSE;
54055405
trig.tginitdeferred = FALSE;
5406-
/* we needn't fill in tgargs */
5406+
/* we needn't fill in tgargs or tgqual */
54075407

54085408
/*
54095409
* See if we can do it with a single LEFT JOIN query. A FALSE result
@@ -5476,13 +5476,14 @@ CreateFKCheckTrigger(RangeVar *myRel, Constraint *fkconstraint,
54765476
}
54775477

54785478
fk_trigger->columns = NIL;
5479+
fk_trigger->whenClause = NULL;
54795480
fk_trigger->isconstraint = true;
54805481
fk_trigger->deferrable = fkconstraint->deferrable;
54815482
fk_trigger->initdeferred = fkconstraint->initdeferred;
54825483
fk_trigger->constrrel = fkconstraint->pktable;
54835484
fk_trigger->args = NIL;
54845485

5485-
(void) CreateTrigger(fk_trigger, constraintOid, indexOid,
5486+
(void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid,
54865487
"RI_ConstraintTrigger", false);
54875488

54885489
/* Make changes-so-far visible */
@@ -5527,6 +5528,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
55275528
fk_trigger->row = true;
55285529
fk_trigger->events = TRIGGER_TYPE_DELETE;
55295530
fk_trigger->columns = NIL;
5531+
fk_trigger->whenClause = NULL;
55305532
fk_trigger->isconstraint = true;
55315533
fk_trigger->constrrel = myRel;
55325534
switch (fkconstraint->fk_del_action)
@@ -5563,7 +5565,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
55635565
}
55645566
fk_trigger->args = NIL;
55655567

5566-
(void) CreateTrigger(fk_trigger, constraintOid, indexOid,
5568+
(void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid,
55675569
"RI_ConstraintTrigger", false);
55685570

55695571
/* Make changes-so-far visible */
@@ -5580,6 +5582,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
55805582
fk_trigger->row = true;
55815583
fk_trigger->events = TRIGGER_TYPE_UPDATE;
55825584
fk_trigger->columns = NIL;
5585+
fk_trigger->whenClause = NULL;
55835586
fk_trigger->isconstraint = true;
55845587
fk_trigger->constrrel = myRel;
55855588
switch (fkconstraint->fk_upd_action)
@@ -5616,7 +5619,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
56165619
}
56175620
fk_trigger->args = NIL;
56185621

5619-
(void) CreateTrigger(fk_trigger, constraintOid, indexOid,
5622+
(void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid,
56205623
"RI_ConstraintTrigger", false);
56215624
}
56225625

0 commit comments

Comments
 (0)