Re: efficient trigger function selection?

Suppose I have an RDF-style table (with columns for subject, predicate,
various object types, and graph) and want to have dozens or even hundreds
of trigger functions defined conditionally on the predicate, ie "when
predicate = '<your predicate here>'".

My guess is Postgres is quite efficient at determining which if any trigger
functions to call, but I thought I'd ask.

Thx, ken

On Tue, 2012-04-10 at 16:07 -0400, Kenneth Tilton wrote:
> Suppose I have an RDF-style table (with columns for subject,
> predicate, various object types, and graph) and want to have dozens or
> even hundreds of trigger functions defined conditionally on the
> predicate, ie "when predicate = '<your predicate here>'".
>
>
> My guess is Postgres is quite efficient at determining which if any
> trigger functions to call, but I thought I'd ask.

I recommend measuring the overhead with some bogus no-op triggers; my
guess is that it will be significant but maybe not too bad depending on
what the rest of the application is doing.

What are you trying to accomplish with so many triggers?

Regards,
Jeff Davis

On Tue, Apr 10, 2012 at 4:59 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> On Tue, 2012-04-10 at 16:07 -0400, Kenneth Tilton wrote:
> > Suppose I have an RDF-style table (with columns for subject,
> > predicate, various object types, and graph) and want to have dozens or
> > even hundreds of trigger functions defined conditionally on the
> > predicate, ie "when predicate = '<your predicate here>'".
> >
> >
> > My guess is Postgres is quite efficient at determining which if any
> > trigger functions to call, but I thought I'd ask.
>
> I recommend measuring the overhead with some bogus no-op triggers; my
> guess is that it will be significant but maybe not too bad depending on
> what the rest of the application is doing.
>
> What are you trying to accomplish with so many triggers?
>

We are simulating a graph DB in Postgres and would have one RDF-like table
with columns as described above. If we want a trigger on what is
conventionally a column for "color", with pseudo-RDF we would have:

create trigger ... when predicate = 'color'

Since the graph data model reduces *everything *into so many RDF "triples",
almost every trigger function in the application would be "when predicate =
X".

well, let's see how many we really get before we panic. :)

Thx for the input.

-ken

On 2012-04-11, Kenneth Tilton <ktilton(at)mcna(dot)net> wrote:

> We are simulating a graph DB in Postgres and would have one RDF-like table
> with columns as described above. If we want a trigger on what is
> conventionally a column for "color", with pseudo-RDF we would have:
>
> create trigger ... when predicate = 'color'
>
> Since the graph data model reduces *everything *into so many RDF "triples",
> almost every trigger function in the application would be "when predicate =
> X".
>
> well, let's see how many we really get before we panic. :)
>
> Thx for the input.

partition your large table on on "predicate"
if priactical do a 1:1 partitioning. that way only the apropriate
triggers will be tested, and furthermore most queries will be
optimised by constraint exclusion. And your database will magically
transform from something approximating EAV to something close to 4NF.

--
⚂⚃ 100% natural