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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
