pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean
Log Message:
-----------
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.
Modified Files:
--------------
pgsql/doc/src/sgml:
catalogs.sgml (r2.210 -> r2.211)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/catalogs.sgml?r1=2.210&r2=2.211)
trigger.sgml (r1.59 -> r1.60)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/trigger.sgml?r1=1.59&r2=1.60)
pgsql/doc/src/sgml/ref:
create_constraint.sgml (r1.20 -> r1.21)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_constraint.sgml?r1=1.20&r2=1.21)
create_trigger.sgml (r1.51 -> r1.52)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_trigger.sgml?r1=1.51&r2=1.52)
pgsql/src/backend/catalog:
index.c (r1.323 -> r1.324)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/index.c?r1=1.323&r2=1.324)
pgsql/src/backend/commands:
copy.c (r1.317 -> r1.318)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/copy.c?r1=1.317&r2=1.318)
tablecmds.c (r1.305 -> r1.306)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/tablecmds.c?r1=1.305&r2=1.306)
trigger.c (r1.256 -> r1.257)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/trigger.c?r1=1.256&r2=1.257)
pgsql/src/backend/executor:
execMain.c (r1.334 -> r1.335)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execMain.c?r1=1.334&r2=1.335)
execQual.c (r1.254 -> r1.255)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execQual.c?r1=1.254&r2=1.255)
execUtils.c (r1.165 -> r1.166)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execUtils.c?r1=1.165&r2=1.166)
nodeModifyTable.c (r1.2 -> r1.3)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/nodeModifyTable.c?r1=1.2&r2=1.3)
pgsql/src/backend/nodes:
copyfuncs.c (r1.451 -> r1.452)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/copyfuncs.c?r1=1.451&r2=1.452)
equalfuncs.c (r1.373 -> r1.374)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/equalfuncs.c?r1=1.373&r2=1.374)
pgsql/src/backend/parser:
gram.y (r2.693 -> r2.694)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/gram.y?r1=2.693&r2=2.694)
pgsql/src/backend/tcop:
utility.c (r1.317 -> r1.318)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/utility.c?r1=1.317&r2=1.318)
pgsql/src/backend/utils/adt:
ruleutils.c (r1.314 -> r1.315)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c?r1=1.314&r2=1.315)
pgsql/src/bin/pg_dump:
pg_dump.c (r1.552 -> r1.553)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dump.c?r1=1.552&r2=1.553)
pgsql/src/include/catalog:
catversion.h (r1.550 -> r1.551)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/catversion.h?r1=1.550&r2=1.551)
pg_trigger.h (r1.35 -> r1.36)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_trigger.h?r1=1.35&r2=1.36)
toasting.h (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/toasting.h?r1=1.9&r2=1.10)
pgsql/src/include/commands:
trigger.h (r1.77 -> r1.78)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/commands/trigger.h?r1=1.77&r2=1.78)
pgsql/src/include/nodes:
execnodes.h (r1.211 -> r1.212)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/execnodes.h?r1=1.211&r2=1.212)
parsenodes.h (r1.415 -> r1.416)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/parsenodes.h?r1=1.415&r2=1.416)
pgsql/src/include/utils:
rel.h (r1.115 -> r1.116)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/utils/rel.h?r1=1.115&r2=1.116)
pgsql/src/test/regress/expected:
triggers.out (r1.28 -> r1.29)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/triggers.out?r1=1.28&r2=1.29)
pgsql/src/test/regress/sql:
triggers.sql (r1.17 -> r1.18)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/triggers.sql?r1=1.17&r2=1.18)
On Fri, Nov 20, 2009 at 3:38 PM, Tom Lane <tgl@postgresql.org> wrote:
Log Message:
-----------
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.
Random thought: would it be possible to use something like this to
optimize foreign key constraints, by not firing them if none of the
relevant columns have been updated?
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
Random thought: would it be possible to use something like this to
optimize foreign key constraints, by not firing them if none of the
relevant columns have been updated?
There already is code in there to do that; see RI_FKey_keyequal_upd_fk
and RI_FKey_keyequal_upd_pk.
Earlier in the discussion of this patch I had suggested eliminating the
special-case code for FKs in favor of using a WHEN clause, but that
would probably not fly unless we can make the WHEN-based implementation
just as fast as the bespoke code; which seems unlikely.
regards, tom lane
On Fri, Nov 20, 2009 at 5:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Random thought: would it be possible to use something like this to
optimize foreign key constraints, by not firing them if none of the
relevant columns have been updated?There already is code in there to do that; see RI_FKey_keyequal_upd_fk
and RI_FKey_keyequal_upd_pk.
*scratches head* Hmm, I see EXPLAIN showing time & calls logged
against fk triggers even when I don't update any columns, unless the
existing value is NULL. But maybe I'm doing something strange, or
misinterpreting the output.
Earlier in the discussion of this patch I had suggested eliminating the
special-case code for FKs in favor of using a WHEN clause, but that
would probably not fly unless we can make the WHEN-based implementation
just as fast as the bespoke code; which seems unlikely.
Yeah.
...Robert