Postgres Pro
Downloads
Home   >   mailing lists

Thread: trigger when clause

trigger when clause

From
Andy Chambers
Date:
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.

Re: trigger when clause

From
Jeff Davis
Date:
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

Re: trigger when clause

From
Andy Chambers
Date:
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