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(dot)tiikkaja(at)cs(dot)helsinki(dot)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