Tom Lane wrote: > "Kevin Grittner" writes: >> Going back through the patches we had to make to 9.0 to move to >> PostgreSQL triggers, I noticed that I let the issues raised as bug >> #6123 lie untouched during the 9.2 development cycle. In my view, >> the best suggestion for a solution was proposed by Florian here: > >> http://archives.postgresql.org/pgsql-hackers/2011-08/msg00388.php > > Do you mean this: > > After every BEFORE trigger invocation, if the trigger returned > non-NULL, check if latest row version is still the same as when > the trigger started. If not, complain. That is the consice statement of it, yes. > While that sounds relatively safe, if possibly performance- > impacting, it's not apparent to me how it fixes the problem you > complained of. The triggers you were using were modifying rows > other than the one being targeted by the triggering action, so a > test like the above would not notice that they'd done anything. My initial use-case was that a BEFORE DELETE trigger was deleting related "child" rows, and the BEFORE DELETE trigger at the child level was updating counts on the original (parent) row. The proposed change would cause an error to be thrown when the parent level returned a non-NULL value from its BEFORE DELETE trigger. That would prevent the silent corruption of the data, so it's a big step forward in my view; but it's not the behavior we most want in our shop for this particular case. In the messages later in the thread, Florian pointed out that this pattern would allow us to get the desired behavior: | BEFORE DELETE ON : | DELETE FROM WHERE parent_id = OLD.id; | IF FOUND THEN | -- Removing children might have modified our row, | -- so returning non-NULL is not an option | DELETE FROM WHERE id = OLD.id; | RETURN NULL; | ELSE | -- No children removed, so our row should be unmodified | RETURN OLD; | END IF; The advantage of Florian's approach is that it changes the default behavior to something very safe, while allowing arbitrarily complex behavior through correspondingly more complex code. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers