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