Re: Proposal / proof of concept: Triggers on VIEWs

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.

For the record, here is the Oracle code in all its gory detail:

CREATE TABLE foo(a int, b int);
CREATE VIEW foo_v AS SELECT * FROM foo;

CREATE TRIGGER del_trig INSTEAD OF DELETE ON foo_v
FOR EACH ROW
BEGIN
DELETE FROM foo WHERE a=:OLD.a;
END;
/

CREATE TRIGGER mod_trig INSTEAD OF UPDATE ON foo_v
FOR EACH ROW
BEGIN
UPDATE foo SET a=:NEW.a, b=:NEW.b WHERE a=:OLD.a;
END;
/

INSERT INTO foo VALUES(1,1);
COMMIT;

So in the first case, 2 concurrent transactions T1 and T2 are started
and do the following:

1. T1 does UPDATE foo_v SET b=0 WHERE a=1;
-- Trigger fires and updates foo
2. T2 does DELETE FROM foo_v WHERE a=1 AND b=1;
-- This matches 1 row in the VIEW (T1 not committed yet)
-- Trigger fires and does DELETE FROM foo WHERE a=1
-- T2 waits for T1
3. T1 commits
4. T2 resumes and deletes the row from foo, since it satisfies a=1

Arguably, this is a deficiency in the trigger function rather than the
trigger firing code. It could be fixed by having the trigger re-check
all the columns in the table against OLD, but that would be pretty
cumbersome for very wide tables, and none of the documented
examples I've seen take that approach.

The second case is as follows:

INSERT INTO foo VALUES(1,1);
COMMIT;

Then 2 concurrent transactions T1 and T2 are started and do the
following:

1. T1 does UPDATE foo_v SET b=b+1 WHERE a=1;
-- Trigger fires and does UPDATE foo SET b=2 WHERE a=1;
2. T2 does UDPATE foo_v SET b=b+1 WHERE a=1;
-- Trigger fires and does UPDATE foo SET b=2 WHERE a=1;
-- T2 waits for T1
3. T1 commits
4. T2 resumes and sets b to 2 as requested inside the trigger

So the net result is b=2 rather than b=3 - pretty-much the textbook
concurrency example. Surprisingly I don't see too many people
complaining about this in Oracle, because to me it seems pretty bad.

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.

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.

Thoughts?

Regards,
Dean

> 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
>