On 8/8/2010 12:49 PM, Dean Rasheed wrote:
On 7 August 2010 10:56, Marko Tiikkaja<marko.tiikk...@cs.helsinki.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
