Re: Triggers on foreign tables

Le mardi 15 octobre 2013 09:47:31 Robert Haas a écrit :
> On Mon, Oct 14, 2013 at 5:24 PM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
> >>> And, I also want some comments from committers, not only from mine.
> >>
> >> +1
> >
> > +1
>
> /me pokes head up. I know I'm going to annoy people with this
> comment, but I feel like it's going to have to be made at some point
> by somebody, so here goes: I don't see the point of this feature. If
> you want a trigger on a table, why not set it on the remote side? A
> trigger on the foreign table won't be enforced consistently; it'll
> only work when the update is routed through the foreign table, not
> when people access the underlying table on the remote side through any
> other mechanism. The number of useful things you can do this way
> seems fairly small. Perhaps you could use a row-level trigger for
> RLS, to allow only certain rows on the foreign side to be updated, but
> even that seems like a slightly strange design: generally it'd be
> better to enforce the security as close to the target object as
> possible.
>
> There's another issue that concerns me here also: performance. IIUC,
> an update of N tuples on the remote side currently requires N+1 server
> round-trips. That is unspeakably awful, and we really oughta be
> looking for ways to make that number go down, by pushing the whole
> update to the remote side. But obviously that won't be possible if
> there's a per-row trigger that has to be evaluated on the local side.
> Now, assuming somebody comes up with code that implements that
> optimization, we can just disable it when there are local-side
> triggers. But, then you're back to having terrible performance. So
> even if the use case for this seemed really broad, I tend to think
> performance concerns would sink most of the possible real-world uses.
>
> I could, of course, be all wet....

Even if the row-level trigger functionality is controversial, in terms of
provided features VS performance, the original patch I submitted enables
statement-level triggers.

Although my goal was to implement row-level triggers and I hit a wall pretty
quickly, would it make sense to have statement-level triggers without their
row counterparts ?

I also think that pushing the whole update to the remote side will not be
possible in all cases, and like Kohei wrote, it may be an acceptable loss to
not be able to push it when a trigger is present. If we want to push the whole
update, we will have to cope with local joins and function evaluations in all
cases. IMO, triggers are just a special case of these limiting factors.