Обсуждение: Re: [COMMITTERS] pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean
Re: [COMMITTERS] pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean
От
Robert Haas
Дата:
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
Re: [COMMITTERS] pgsql: Add a WHEN clause to CREATE TRIGGER, allowing a boolean
От
Robert Haas
Дата:
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