Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE
Date: 2011-07-19 21:45:16
Message-ID: 4E25B49C020000250003F51D@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> [ circular trigger relationships ]
>
>> I expect the DELETE from a to delete related b record(s) and then
>> succeed. Instead the UPDATE in b_del_trig causes delete of a to
>> silently fail.
>
> The update causes the already-selected target row version of the
> DELETE to be obsoleted, so heap_delete finds it has nothing to do.
> I'm disinclined to mess with that logic.

It's pretty astonishing behavior for application programmers. It's
not unusual for triggers on detail from one table to maintain a
status, count, or sum in a higher level table. When a DELETE from
the higher level table causes deletes at the lower level, the lower
level trigger really doesn't have any way to know that. I think
this should be considered a bug.

At the point where heap_delete finds that the tuple has expired, I
think it should follow the ctid chain as long as xmax is from the
same top-level transaction, and then decide if it has something to
do. Normally, that would be zero iterations, so I doubt the
performance impact is huge.

I'm willing to mess with that logic if there's a chance that it will
be accepted. I may *need* to even if it won't, to allow our
migration to native PostgreSQL triggers to succeed -- we've been
trying to work around it for the last few days, but the places where
it's popping up are becoming increasingly hard to accommodate that
way.

-Kevin

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2011-07-19 22:10:41 Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE
Previous Message Tom Lane 2011-07-19 21:30:47 Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE