Re: WIP fix proposal for bug #6123

On Wed, Jul 20, 2011 at 2:58 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)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, but that's sometimes difficult to accomplish.

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.

I'm unfortunately unqualified to speak to the correctness of your
patch, but I concur with your view that the current behavior is lousy.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company