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