Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group