Обсуждение: Trigger difference in 9.0 and 9.3

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

Erwan Tanajaya-2 wrote
> 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 ?

If you are inside a DELETE trigger you would do something like:

DELETE FROM table_this_trigger_is_on WHERE pk_for_the_table =
OLD.pk_for_the_table;
RETURN NULL;

The returning of NULL prevents the original DELETE action but since you took
care of it manually the final result is that the record in question is still
gone.

That said you need to see if you can introduce logic somewhere that says:
"since my parent record is going to go away anyway I should just avoid
updating it in the first place".

Aside from that a self-contain and reasonably faithful reproduction of your
exact problem might help you either obtain advice or make Kevin et. al.
reconsider the solution put in place to avoid the problem they were facing.

Note that in the two years this patch has been in place this is the first
time I recall seeing a report of this nature...which suggests there is a
correct solution that you are missing.  Though I am also thinking 9.3 hasn't
received as much adoption in the past two years as we would have liked...

David J.




--
View this message in context: http://postgresql.nabble.com/Trigger-difference-in-9-0-and-9-3-tp5818162p5826449.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Erwan Tanajaya <erwan.tanajaya@gmail.com> wrote:

>> Aside from that a self-contain and reasonably faithful
>> reproduction of your exact problem might help you either obtain
>> advice or make Kevin et. al. reconsider the solution put in
>> place to avoid the problem they were facing.

> I really hope Kevin and other dev have a better solution for
> this case

You really need to provide a self-contained test case to
demonstrate what you think the problem is.  For example, you might
think something like this works:

------------------------------------------------------------

create table tran_header
  (
    tran_id int not null,
    total_amt int not null default (0),
    primary key (tran_id)
  );
create table tran_detail
  (
    tran_id int not null,
    tran_det_id int not null,
    amt int not null,
    primary key (tran_id, tran_det_id)
  );

create function tran_detail_insert_func()
  returns trigger
  language plpgsql
as $$
begin
  update tran_header
    set total_amt = total_amt + new.amt
    where tran_header.tran_id = new.tran_id;
  return new;
end;
$$;
create function tran_detail_update_func()
  returns trigger
  language plpgsql
as $$
begin
  if old.tran_id = new.tran_id then
    update tran_header
      set total_amt = total_amt + new.amt - old.amt
      where tran_header.tran_id = old.tran_id;
  else
    update tran_header
      set total_amt = total_amt - old.amt
      where tran_header.tran_id = old.tran_id;
    update tran_header
      set total_amt = total_amt + new.amt
      where tran_header.tran_id = new.tran_id;
  end if;
  return new;
end;
$$;
create function tran_detail_delete_func()
  returns trigger
  language plpgsql
as $$
begin
  update tran_header
    set total_amt = total_amt - old.amt
    where tran_header.tran_id = old.tran_id;
  return old;
end;
$$;
create function tran_header_delete_func()
  returns trigger
  language plpgsql
as $$
begin
  delete from tran_detail where tran_id = old.tran_id;
  return old;
end;
$$;

create trigger tran_detail_insert_trig
  after insert on tran_detail
  for each row execute procedure tran_detail_insert_func();
create trigger tran_detail_update_trig
  after update on tran_detail
  for each row execute procedure tran_detail_update_func();
create trigger tran_detail_delete_trig
  after delete on tran_detail
  for each row execute procedure tran_detail_delete_func();
create trigger tran_header_delete_trig
  before delete on tran_header
  for each row execute procedure tran_header_delete_func();

insert into tran_header (tran_id) values (1), (2), (3);
insert into tran_detail (tran_id, tran_det_id, amt) values
  (1,1,100),(1,2,200),(2,1,100),(2,2,200),(2,3,300);

select * from tran_header order by tran_id;

delete from tran_header where tran_id = 2;

------------------------------------------------------------

The above probably doesn't do what you expect or intend on 9.0.
Try a select from tran_header and you will notice that the row you
probably *thought* you deleted is still there.  It is silently
doing the wrong thing.  You can fix it like this:

------------------------------------------------------------

create or replace function tran_header_delete_func()
  returns trigger
  language plpgsql
as $$
begin
  delete from tran_detail where tran_id = old.tran_id;
  if found then
    delete from tran_header where tran_id = old.tran_id;
    return null;
  end if;
  return old;
end;
$$;

------------------------------------------------------------

You could also have fixed it by deleting the header row *twice*,
since (unless there is some other process adding detail in between)
there won't be any detail the second time to update the header row
and cause a problem -- what the above fix does it to do that second
delete automatically.

What happens in more recent versions is that rather than silently
failing to delete the specified master row, it gives an error so
you can fix your broken code.

Now, if you can provide a self-contained test case (similar to the
above -- something that can run from an empty database to
demonstrate your issue), we can offer other advice or consider some
adjustment to the code if it seems warranted.  I'm pretty sure we
will not go back to the type of failure shown above.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company