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

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

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-07-19 22:10:41
Subject: Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE
Previous:From: Tom LaneDate: 2011-07-19 21:30:47
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