Lists: | pgsql-committerspgsql-hackers |
---|
From: | tgl(at)postgresql(dot)org (Tom Lane) |
---|---|
To: | pgsql-committers(at)postgresql(dot)org |
Subject: | pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean |
Date: | 2009-11-20 20:38:12 |
Message-ID: | 20091120203812.247D1753FB7@cvs.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-committers pgsql-hackers |
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)
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)postgresql(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [COMMITTERS] pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean |
Date: | 2009-11-20 22:00:05 |
Message-ID: | 603c8f070911201400k2bbf83d3j6fda4f072cdf72d5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-committers pgsql-hackers |
On Fri, Nov 20, 2009 at 3:38 PM, Tom Lane <tgl(at)postgresql(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [COMMITTERS] pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean |
Date: | 2009-11-20 22:05:02 |
Message-ID: | 17969.1258754702@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-committers pgsql-hackers |
Robert Haas <robertmhaas(at)gmail(dot)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
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [COMMITTERS] pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean |
Date: | 2009-11-20 22:12:32 |
Message-ID: | 603c8f070911201412k2f023565lb8735065baf35a3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-committers pgsql-hackers |
On Fri, Nov 20, 2009 at 5:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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