Re: Proposal / proof of concept: Triggers on VIEWs

On 4 August 2010 15:08, Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> On 8/4/10 5:03 PM +0300, Dean Rasheed wrote:
>>
>> On 4 August 2010 14:43, Marko Tiikkaja<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
>>  wrote:
>>>
>>> I'm not sure I understand.  RETURNING in DELETE on a table fetches the
>>> old
>>> value after it was DELETEd, so it really is what the tuple was before the
>>> DLETE, not what is seen by the snapshot.  In a BEFORE DELETE trigger, the
>>> row is always locked so it can't change after the trigger is fired.
>>>
>>
>> Ah, I think I mis-understood. If I understand what you're saying
>> correctly, you're worried that the row might have been modified in the
>> same query, prior to being deleted, and you want RETURNING to return
>> the updated value, as it was when it was deleted.
>
> I'm mainly concerned about concurrently running transactions.
>

Sorry for the delay replying.

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

Regards,
Dean