Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> In this particular example, I think it would work just as well to
> do the reference-count updates in AFTER triggers, and maybe the
> short answer is to tell people they have to do it like that
> instead of in BEFORE triggers.
I think that is quite often the right answer.
> However, I wonder what use-case led you to file bug #6123 to begin
> with.
In our Circuit Court software, we have about 1600 trigger functions
on about 400 tables, and this summer we converted them from our Java
middle tier framework to native PostgreSQL triggers. Since we had
been writing them in our interpretation of ANSI SQL trigger code,
parsing that, and using the parse tree to build a Java class for
each trigger, we were able to generate the PostgreSQL trigger
functions and CREATE TRIGGER statement mechanically (from the parse
tree), with pretty good success. In testing, though, our business
analysts noticed a number of situations where an attempt to delete a
row actually deleted related rows which should have gone away with
the row they were directly trying to delete, but the target row was
still there. A few days of investigation, including stepping
through query execution in gdb, turned up this issue.
Having identified (at least one flavor of) the problem, we grepped
the source code for the BEFORE triggers for UPDATE and DELETE
statements, and were able to fix a number of them by moving code to
AFTER triggers or setting values into NEW fields rather than running
an UPDATE. So far, so good.
But there were a number of situations where the DELETE of a row
needed to cause related rows in other tables to be deleted, and for
one reason or another a foreign key with ON DELETE CASCADE was not
an option. At the same time, triggers on some of those related
tables needed to update summary or redundant data in other tables
for performance reasons. Because a number of tables could be
involved, and some of the triggers (at the "lower" levels) could be
AFTER triggers and still contribute to the problem, (1) we had no
reliable way to ensure we would find all of the cases of this on all
code paths, and (2) due to referential integrity and other trigger-
based validations, it would be hard to restructure such that the
DELETE of the "child" rows was not done on the BEFORE DELETE trigger
of the "parent".
The patch we've been using in production throws errors if the row
for a BEFORE UPDATE trigger is updated by another statement. (Well,
OK, you showed me that it really is throwing an error if the row is
updated and there has been another statement executed, but as long
as it is *more* strict than we actually need, we won't corrupt data
-- and the current rule hasn't been hard for us to live with.) It
allows the DELETE to proceed if the tuple is updated from within the
BEFORE DELETE trigger. We would need to tweak some triggers to move
to the approach embodied in the recent patch drafts, but the IF
FOUND logic suggested by Florian looks like it will cover all of our
use cases, and they should be fairly easy to find with grep.
Hopefully this answers your question. I went looking for details on
particular failures here, but didn't have luck with so far. I can
try again if more detail like that would help.
-Kevin