Re: Allow WHEN in INSTEAD OF triggers

On Sat, 28 Dec 2019 at 16:45, David Fetter <david(at)fetter(dot)org> wrote:
>
> On Sat, Dec 28, 2019 at 12:12:30AM -0300, Alvaro Herrera wrote:
> > On 2019-Dec-28, David Fetter wrote:
> >
> > > While noodling around with an upcoming patch to remove user-modifiable
> > > RULEs, I noticed that WHEN conditions were disallowed from INSTEAD OF
> > > triggers for no discernible reason. This patch removes that
> > > restriction.
> >
> > If you want to remove the restriction, your patch should add some test
> > cases that show it working.
>
> Tests added :)
>

I too think this is a bad idea.

Doing nothing if the trigger's WHEN condition isn't satisfied is not
consistent with the way other types of trigger work -- with any other
type of trigger, if the WHEN condition doesn't evaluate to true, the
query goes ahead as if the trigger hadn't been there. So the most
consistent thing to do would be to attempt an auto-update if the
trigger isn't fired, and that leads to a whole other world of pain
(e.g., you'd need 2 completely different query plans for the 2 cases,
and more if you had views on top of other views).

The SQL spec explicitly states that INSTEAD OF triggers on views
should not have WHEN clauses, and for good reason. There are cases
where it makes sense to deviate from the spec, but I don't think this
is one of them.

Regards,
Dean