Re: Proposal / proof of concept: Triggers on VIEWs

On 8/8/2010 12:49 PM, Dean Rasheed wrote:
> On 7 August 2010 10:56, Marko Tiikkaja<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
>> The problem is that this isn't even nearly sufficient. I gave this some
>> more thought while I was away, and it seems that I missed at least one more
>> important thing: the WHERE clause. Imagine this query:
>>
>> DELETE FROM view WHERE pk = 1 AND f1> 0;
>>
>> Now the trigger function gets called if the row where pk = 1, as seen by the
>> query's snapshot, has f1> 0. But if a concurrent transaction sets f1 to 0
>> before the triggers gets to the row, you end up deleting a row that doesn't
>> match the WHERE clause.
>
> I've been playing with this in Oracle and I can confirm that it behaves
> exactly as my code would do. So in this example, the trigger deletes
> from the underlying table even after the row has been changed so that
> it no longer satisfies the original WHERE clause. The case I find
> worse is that if 2 concurrent transactions do UPDATE view SET f1=f1+1,
> the value will only be incremented once.

Wow. I'm surprised to hear this.

> In PostgreSQL we could fix it by declaring the VIEW query as FOR
> UPDATE, but that's no good if for example the VIEW was based on an
> aggregate. Oracle has the same limitations on FOR UPDATE, but also
> AFAICS it doesn't allow VIEWs to be created using SELECT FOR UPDATE
> at all.

Also making all SELECTs on the view FOR UPDATE is not a very good idea..

> For those migrating code from Oracle, providing this feature as-is
> might be valuable, since presumably they are not too concerned about
> these concurrency issues. Ideally we'd want to do better though.

Yes, you might be right. This feature on its on can greatly simplify
what people now have to do to get triggers on views.

Regards,
Marko Tiikkaja