Re: Triggers on foreign tables

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> /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

Perhaps some folks will be annoyed- I'm not annoyed, but I don't
really agree. :)

> 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.

I can certainly see use-cases for this, a very simple one being a way to
keep track of what's been updated/inserted/whatever through this
particular foreign table (essentially, an auditing table). The *remote*
side might not be ideal for tracking that information and you might want
the info locally and remotely anyway.

> 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.

Performance, while a concern, should probably be secondary when there
are valid use-cases for this where the performance wouldn't be a problem
for users.

Thanks,

Stephen