Re: WIP fix proposal for bug #6123

Did we ever decide on this?  Is it a TODO?

---------------------------------------------------------------------------

On Fri, Jul 22, 2011 at 04:01:20PM -0500, Kevin Grittner wrote:
> Robert Haas <robertmhaas@gmail.com> wrote:
> > On Wed, Jul 20, 2011 at 2:58 PM, Kevin Grittner
> > <Kevin.Grittner@wicourts.gov> wrote:
> >> So basically, the goal of this patch is to ensure that a BEFORE
> >> DELETE trigger doesn't silently fail to delete a row because that
> >> row was updated during the BEFORE DELETE trigger firing (in our
> >> case by secondary trigger firing).
> > 
> > I've run across this problem before while writing application code
> > and have found it surprising, unfortunate, and difficult to work
> > around. It's not so bad when it only bites you once, but as things
> > get more complicated and you have more and more triggers floating
> > around, it gets pretty darn horrible.  One of the things I've done
> > to mitigate the impact of this is to write as many triggers as
> > possible as AFTER triggers
>  
> Yeah, this is not an issue in AFTER triggers, so moving any updating
> to those is a solution.  In most cases that's where you want
> triggered modifications to take place anyway.  The cascading delete
> situation is the most obvious exception, although there certainly
> could be others.
>  
> > but that's sometimes difficult to accomplish.
>  
> Yeah, sometimes.
>  
> > Your scenario is a BEFORE DELETE trigger that does an UPDATE on
> > the same row, but I think this problem also occurs if you have a
> > BEFORE UPDATE trigger that does an UPDATE on the same row.  I
> > believe the second update gets silently ignored.
>  
> My testing shows that the primary update gets ignored, while all the
> triggered effects of that update are persisted.  Yuck.  :-(  It
> certainly seems possible to turn that around, but that hardly seems
> better.  In asking application programmers here what they would
> *expect* to happen, they all seem to think that it is surprising
> that the BEFORE trigger functions *return a record*, rather than a
> boolean to say whether to proceed with the operation.  They feel it
> would be less confusing if a value set into NEW was effective if the
> operation does take effect, and the boolean controls whether or not
> that happens.  They rather expect that if an update happens from the
> same transaction while a before trigger is running, that the NEW
> record will reflect the change.
>  
> I recognize how hard it would be to create that expected behavior,
> and how unlikely it is that the community would accept such a change
> at this point.  But current behavior is to silently do something
> really dumb, so I think some change should be considered -- even if
> that change is to throw an error where we now allow nonsense.
>  
> INSERT is not a problem -- if a BEFORE INSERT trigger inserts a row
> with a conflicting primary key (or other unique index key), the
> operation will be rolled back.  That's fine.
>  
> I think DELETE can be cleanly fixed with a patch similar to what I
> posted earlier in the thread.  I found one more value that looks
> like it should be set, and it could use some comments, but I believe
> that we can get DELETE behavior which is every bit as sensible as
> INSERT behavior with a very small change.
>  
> The worms do come crawling out of the can on BEFORE UPDATE triggers,
> though.  When faced with an UPDATE which hasn't yet been applied,
> and other UPDATEs triggering from within the BEFORE UPDATE trigger
> which touch the same row, it doesn't seem like you can honor both
> the original UPDATE which was requested *and* the triggered UPDATEs.
> Of course, if you discard the original UPDATE, you can't very well
> do anything with the record returned from the BEFORE UPDATE trigger
> for that update.  Since it seems equally evil to allow the update
> while ignoring some of the work caused by its trigger functions as
> to show the work of the triggered updates while suppressing the
> original update, I think the right thing is to throw an error if the
> old row for a BEFORE UPDATE is updated by the same transaction and
> the trigger function ultimately returns a non-NULL value.
>  
> Thoughts?
>  
> -Kevin
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +