Re: what are rules for?

Tom Lane wrote:
> Well, both the trigger call API and the underlying implementation deal
> in CTIDs, so just airily saying "we don't need 'em" doesn't obviously
> work. (Note I did not say "obviously doesn't work". Whether this is
> feasible depends on much closer analysis than any of the hand-waving
> that we've done so far.)

> To my mind there are two really fundamental issues underlying this.
> One, which is what CTID fixes, is that a view doesn't have any primary
> key by which to identify which row you're talking about. (Even if
> there is a candidate key implicit in the view semantics, we don't
> have any way for the system to know what it is.)

The Oracle "instead of" trigger ducks this issue completely. The
trigger is called once per row in the view that matches the top-level
"where" clause, and it is entirely up to the author of the trigger
function to work out what to update (if anything). In fact the trigger
is free to update an entirely different set of rows if it wants to!

An obvious problem with this is that if the view has no unique key,
the trigger may end up doing the same work several times over. Say I
do "update my_view set a=10 where b=5", and 20 rows match "b=5". Then
the trigger function will get called 20 times, and it will probably
just do the same thing each time. I'm not aware of a good solution to
this, other than "don't write views like that".

> The other nasty little
> issue is that if the view involves any non-immutable functions, it's
> not necessarily the case that you can recompute the OLD row at all.

Surely the results of updating a view containing non-immutable
functions are going to be pretty unpredictable anyway.

> Also, if the view involves expensive functions, you'd probably rather
> the system *didn't* recompute them unless absolutely needed, even if
> they're immutable. A transform-based approach can succeed at that, but
> a trigger-based approach really can't since it needs to see materialized
> OLD and NEW rows.

Yes that's true. You could perhaps mitigate against this sort of
performance problem by providing some mechanism for the trigger
definer to select which columns to pass to trigger function.

Even if the view doesn't contain expensive functions, I would expect
a trigger to perform worse than a query-rewrite in cases such as a
single update statement which affects multiple rows. So triggers might
not be suitable for such cases, but there would also be many other
cases where the performance would be similar, and then the ease-of-use
and greater flexibility of triggers compared to rules would make them
preferable (IMO).

Dean.

_________________________________________________________________

http://clk.atdmt.com/UKM/go/msnnkmgl0010000002ukm/direct/01/