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/
