Re: Trigger difference in 9.0 and 9.3
От | Tom Lane |
---|---|
Тема | Re: Trigger difference in 9.0 and 9.3 |
Дата | |
Msg-id | 831.1410186499@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Trigger difference in 9.0 and 9.3 (Erwan Tanajaya <erwan.tanajaya@gmail.com>) |
Ответы |
Re: Trigger difference in 9.0 and 9.3
(Erwan Tanajaya <erwan.tanajaya@gmail.com>)
|
Список | pgsql-novice |
Erwan Tanajaya <erwan.tanajaya@gmail.com> writes: > how i write this trigger in postgresql 9.3 in postgresql 9.0 it is running > well > i had transaction header and transaction detail table > I had trigger on after detail table inserted, updated, or deleted it update > value on header table > when header is deleted i had trigger that run in before delete event to > delete the detail table. > i need header run in before event because when detail is deleted(or update > and insert) and the trigger is running i need selecting data from header. > now in postgresql 9.3 i get this error "tuple to be updated was already > modified by an operation triggered by current command", it running well in > 9.0 I suppose you're running into this 9.3 change: Author: Kevin Grittner <kgrittn@postgresql.org> Branch: master Release: REL9_3_BR [6868ed749] 2012-10-26 14:55:36 -0500 Throw error if expiring tuple is again updated or deleted. This prevents surprising behavior when a FOR EACH ROW trigger BEFORE UPDATE or BEFORE DELETE directly or indirectly updates or deletes the the old row. Prior to this patch the requested action on the row could be silently ignored while all triggered actions based on the occurence of the requested action could be committed. One example of how this could happen is if the BEFORE DELETE trigger for a "parent" row deleted "children" which had trigger functions to update summary or status data on the parent. This also prevents similar surprising problems if the query has a volatile function which updates a target row while it is already being updated. Where the new error messages are generated, in most cases the best fix will be to move code from the BEFORE trigger to an AFTER trigger. Where this is not feasible, the trigger can avoid the error by re-issuing the triggering statement and returning NULL. Kevin Grittner and Tom Lane with input from Florian Pflug and Robert Haas, based on problems encountered during conversion of Wisconsin Circuit Court trigger logic to plpgsql triggers. If you're running into this, it's fairly likely that your original coding didn't actually do what it appears to do. I'd recommend you rearrange your trigger code as suggested just for clarity's sake, even if it somehow was not in fact buggy. But if you're convinced it wasn't buggy, you can have the initial trigger return NULL in cases where the initial update can be skipped (which is what 9.0 was doing silently). regards, tom lane
В списке pgsql-novice по дате отправления: