trigger when clause
Hi,
Does anyone know the time complexity of the algorithm used to handle
triggers with a when clause?
To make this a little more concrete, what is likely to perform better
a) A single trigger with "n" if/else clauses
b) A set of "n" triggers each using a different when clause.
What if n is 10?
What if n is 200?
The when clause would just be comparing a text "predicate" column for
equality.
Cheers,
Andy
--
Andy Chambers
Software Engineer
(e) achambers@mcna.net
(t) 954-682-0573
CONFIDENTIALITY NOTICE: This electronic mail may contain information that
is privileged, confidential, and/or otherwise protected from disclosure to
anyone other than its intended recipient(s). Any dissemination or use of
this electronic mail or its contents by persons other than the intended
recipient(s) is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by reply
e-mail so that we may correct our internal records. Please then delete the
original message. Thank you.
On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote:
Does anyone know the time complexity of the algorithm used to handle
triggers with a when clause?
It's done with a linear scan of all triggers, testing the WHEN clause
for each.
To make this a little more concrete, what is likely to perform better
a) A single trigger with "n" if/else clauses
b) A set of "n" triggers each using a different when clause.
Both are essentially linear.
If you want to scale to a large number of conditions, I would recommend
using one trigger in a fast procedural language, and searching for the
matching conditions using something better than a linear search.
To beat a linear search, you need something resembling an index, which
is dependent on the types of conditions. For instance, if your
conditions are:
00 <= x < 10
10 <= x < 20
20 <= x < 30
...
you can use a tree structure. But, obviously, postgres won't know enough
about the conditions to know that a tree structure is appropriate from a
given sequence of WHEN clauses. So, you should use one trigger and code
the condition matching yourself.
Regards,
Jeff Davis
On Tue, Apr 10, 2012 at 5:10 PM, Jeff Davis <pgsql@j-davis.com> wrote:
On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote:
Does anyone know the time complexity of the algorithm used to handle
triggers with a when clause?It's done with a linear scan of all triggers, testing the WHEN clause
for each.To make this a little more concrete, what is likely to perform better
a) A single trigger with "n" if/else clauses
b) A set of "n" triggers each using a different when clause.Both are essentially linear.
If you want to scale to a large number of conditions, I would recommend
using one trigger in a fast procedural language, and searching for the
matching conditions using something better than a linear search.To beat a linear search, you need something resembling an index, which
is dependent on the types of conditions. For instance, if your
conditions are:00 <= x < 10
10 <= x < 20
20 <= x < 30
...you can use a tree structure. But, obviously, postgres won't know enough
about the conditions to know that a tree structure is appropriate from a
given sequence of WHEN clauses. So, you should use one trigger and code
the condition matching yourself.
Thanks Jeff. That's very helpful.
--
Andy