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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
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 22:10:41
Message-ID: 11540.1311113441@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

I think you have no idea how large a can of worms you're opening.

To point out just one problem, if heap_delete restarts itself and tries
to delete some other row version than it started with, should the ON
DELETE triggers be fired again? If not, why not? If they are, what
prevents an infinite loop?

IMO, application code that causes this to happen is impossibly fragile
and needs to be rewritten to not do it.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2011-07-19 22:51:57 Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE
Previous Message Kevin Grittner 2011-07-19 21:45:16 Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE