Re: Remembering bug #6123
От | Kevin Grittner |
---|---|
Тема | Re: Remembering bug #6123 |
Дата | |
Msg-id | 4F10677A02000025000447D2@gw.wicourts.gov обсуждение исходный текст |
Ответ на | Re: Remembering bug #6123 (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Remembering bug #6123
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
Tom Lane <tgl@sss.pgh.pa.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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Andrew DunstanДата:
Сообщение: Re: [COMMITTERS] pgsql: Fix breakage from earlier plperl fix.
Следующее
От: Alex HunsakerДата:
Сообщение: Re: [COMMITTERS] pgsql: Fix breakage from earlier plperl fix.