Re: Proposal / proof of concept: Triggers on VIEWs

On 8/6/2010 10:49 AM, Dean Rasheed wrote:
> On 4 August 2010 15:08, Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi>  wrote:
>> I'm mainly concerned about concurrently running transactions.
>
> Once again, I think I mis-understood your point. I think that the
> database can't really lock anything before firing the trigger because
> the view might contain grouping/aggregates or even not be based on any
> real tables at all, so it would be impossible to work out what to
> lock.

Right.

> Thus it would be up to the trigger function to get this right.
> In the simplest case, for a DELETE, this might look something like:
>
> CREATE OR REPLACE FUNCTION instead_of_delete_trig_fn()
> RETURNS trigger AS
> $$
> BEGIN
>    DELETE FROM base_table WHERE pk = OLD.pk;
>    IF NOT FOUND THEN RETURN NULL; END IF;
>
>    RETURN OLD;
> END;
> $$
> LANGUAGE plpgsql;
>
> If 2 users try to delete the same row, the second would block until
> the first user's transaction finished, and if the first user
> committed, the second user's trigger would return NULL, which the
> database would signal as no rows deleted.

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 have a few ideas on how this 
could be tackled, but I think we need to split these two threads.  I 
still think that having triggers on views without addressing these 
concurrency concerns is not a good idea, though.


Regards,
Marko Tiikkaja