Re: Proposal / proof of concept: Triggers on VIEWs

On 8/4/10 4:31 PM +0300, Dean Rasheed wrote:
>>     1) You can't re-evaluate the UPDATE expression like an UPDATE on a
>>        table does.  Consider for example  UPDATE foo SET a=a+1;  If the
>>        tuples change before we get to them, we lose data because we
>>        simply can't re-evaluate "a+1" in the trigger.
>>
>
> Is this the same problem the writeable CTE patch ran into?

No, that was something different.

> Yeah, the assumption is that the number of affected rows is the number
> of rows in the view that matched the user's WHERE clause. You could
> return fewer affected rows by having the trigger return NULL for some
> of them, but you can't say that you've affected more than that. So
> even if the trigger updates 10 rows in the base tables for a given row
> in the view, that still only counts as 1 row affected in the view by
> the original query.

I think that's fine.

>>     3) You can't set the RETURNING results.  You suggested that
>>        RETURNING for DELETE would return the OLD value, but that seems
>>        broken because that's not necessarily what was deleted.
>
> Well that's what happens for a table. Alternatively the trigger could
> modify OLD, and then have RETURNING return that, but that's not what
> happens in a BEFORE DELETE trigger on a table.

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.

> For INSERT and UPDATE the trigger would compute and make the necessary
> changes to the base tables, and then return the new contents of the
> view's row in a modified copy of NEW, if necessary for RETURNING. This
> might include re-computed derived values for example.

I see.


Regards,
Marko Tiikkaja