Re: Tables cannot have INSTEAD OF triggers

On 2 April 2015 at 22:23, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Apr 2, 2015 at 5:02 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>>> I think the upshot is that INSTEAD OF triggers work in a particular way
>>> because that's what is needed to support updatable views. If triggers
>>> on tables should behave differently, maybe it should be a separate
>>> trigger type. Maybe it would be feasible to extend BEFORE triggers to
>>> support RETURNING, for example?
>>
>> What in the above prohibits extending the behaviour to tables? I have
>> yet to see what compatibility or similarity problem that'd pose. It
>> seems all mightily handwavy to me.
>
> Yeah. It's possible there's a better interface here than INSTEAD OF,
> and one of the things I didn't like about the OP was that it started
> by stating the syntax that would be used rather than by describing the
> problem that needed to be solved. It's generally better to start with
> the latter, and then work out the syntax from there. But having
> gotten that gripe out of my system, and especially in view of Dean's
> comments, it's not very clear to me what's wrong with using INSTEAD OF
> for this purpose. If you make BEFORE triggers do this via RETURNING,
> then you might have a trigger that returns multiple rows, which seems
> like it would introduce a bunch of new complexity for no obvious
> benefit.
>

Yes, I'm inclined to agree. One of the reasons that INSTEAD OF
triggers weren't supported on tables was the lack of an obvious
use-case for it, but now having thought about partitioning, I think
they would provide a fairly neat solution to that problem. I don't
think that putting a view with INSTEAD OF triggers on top of the
parent table and then always going through that view works quite as
well, because there are still a few cases where a view doesn't work as
well as a table. A view can't be the target of a foreign key, for
example, so there'd be no way for a cascaded UPDATE to invoke the
INSTEAD OF triggers.

If you needed to handle the case of updates causing a change of
partition, adding conditional INSTEAD OF triggers to the child tables
would be a way to do that, retaining support for RETURNING, and
keeping the logic localised to each partition, only invoking it when
necessary.

Supporting INSTEAD OF triggers on tables is not completely trivial to
implement, but it doesn't look too hard either, and the more I think
about it, the more I suspect that other use-cases will emerge to make
that effort worthwhile.

Regards,
Dean