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

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.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