Re: BUG #6123: DELETE fails if before trigger causes another trigger to UPDATE - Mailing list pgsql-bugs

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

No.

> If not, why not?

It's the same row.  The UPDATE trigger fires when it's UPDATEd; but
it isn't being DELETEd a second time.  Imposing this level of
awareness of MVCC mechanics on application programmers isn't a good
idea, in my view.

I wouldn't dive into it lightly.  As I said, we've been trying to
work around it, but it is neither unreasonable to maintain summary
data in high level tables, nor to cascade deletes, and with hundreds
of tables, most of which have many triggers, it's not trivial to
catch all of these or to figure out how to avoid the issue in each
case.

For those who haven't heard about our environment or have forgotten,
these triggers were written in a vendor-neutral SQL dialect based on
the standard, and we parsed the SQL to generate Java classes which
contained the procedural logic and the less portable behaviors,
calling out to JDBC through a thin DBMS-specific portability layer.
Since we've decided to commit to PostgreSQL we're converting all
triggers to native PostgreSQL, with a mechanical first pass based on
a new tree-walker (for the parser which emitted the Java classes)
which is emitting plpgsql functions and trigger creation statements.

While the database is pretty well normalized, there is some
summarization of data from detail tables to higher levels for
performance, and some cases where a delete at a higher level should
cascade to lower levels, at least in some circumstances.  Both
behaviors are implemented in triggers in the old code and have been
working fine in the old framework.

> IMO, application code that causes this to happen is impossibly
> fragile

They've been working fine for over ten years in our old framework,
so "impossibly fragile" seems an overstatement.

> and needs to be rewritten to not do it.

Would you say it is more sane to sprinkle the logic to maintain
redundant summary data across the application instead of putting it
in triggers on the detail, or more sane to move logic to cascade
deletes outside the database?  When the triggers were firing from
our middle tier just above the database we didn't have to choose;
you seem to be saying we should, but neither alternative looks
pretty to me.

-Kevin