Re: Batch API for After Triggers

On 9 June 2013 05:08, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
>> While fiddling with FK tuning, Noah suggested batching trigger
>> executions together to avoid execution overhead.
>
> I like the general idea, but I'd prefer a way to avoid having to queue
> up tons of trigger events to be executed in the first place.

There's already a thread on that exact topic, for FKs, which is what
spawned this thread.

> Aggregates do this by providing a way to store up information to be
> processed by an eventual 'final' function.

As I mentioned in my post, I did consider that and then chose not to
do that. However, having a final func is a major modification in the
way that we specify trigger functions. We'd also need to cope with
recursive trigger execution, which would mean the final func would get
called potentially many times, so there's no way of knowing if the
final func is actually the last call needed. That sounded complex and
confusing to me.

The proposed API allows you to do exactly that anyway, more easily, by
just waiting until tg_event_num == tg_tot_num_events.

> Another option, as Kevin
> asked about, would be statement level triggers which are able to see
> both the OLD and the NEW versions of the relation.
>
> The per-row trigger option with a way to be called immediately

... it already exists and is known as the WHEN clause. This is the
mechanism I expect to use to tune FKs

> and then
> store what it cares about for a later final function strikes me as very
> generalized and able to do things that the statement-level option
> couldn't,

> but I'm not sure if there's a use-case that could solve which
> the OLD/NEW statement trigger capability couldn't.

I think the two things are quite different, as I explained on a
separate post to Kevin.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services