Re: Trigger difference in 9.0 and 9.3

On Mon, Sep 8, 2014 at 9:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Erwan Tanajaya <erwan(dot)tanajaya(at)gmail(dot)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(at)postgresql(dot)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
>

hi Tom Lane,

i do as you suggested, i returning null on the initial before delete
trigger(parrent trigger), but when i returning null no delete action is
accured.
am i doing wrong ?

"the trigger can avoid the error by re-issuing the triggering statement and
returning NULL"
how i re-issuing the triggering statement ?

it wasn't buggy in postgresql 9.0 and other database i use, i got the
expected result that i want.

thank you in advance
i really need solution for this simple case

Best Regards,
Erwan Tanajaya