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