Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>>> Would you feel comfortable with a patch which threw an error on
>>>> the DELETE case, as it does on the UPDATE case?
>>>
>>> Yes, though with a little additional twist. The twist being that
>>> I'd like the error to be thrown earlier, at the time of the
>>> offending UPDATE or DELETE, not later, when the original UPDATE
>>> or DELETE which caused the BEFORE trigger invocation stumbles
>>> over the updated row. It not only seems more logical that way,
>>> but it also makes it possible for the trigger to catch the
>>> error, and react accordingly.
>>
>> I hadn't thought of that. It does seem better in every way
>> except for how much work it takes to do it and how much testing
>> it needs to have confidence in it. Definitely not 9.1 material.
>
> IMHO, none of this is 9.1 material.
Nobody is currently arguing for including anything to fix this in
9.1. Perhaps I should have been more explicit that in agreeing with
Florian, I'm giving up on the idea of any PostgreSQL release
attempting to do anything about this before 9.2.
That said, this is a data corrupting bug in the view of management
here, based on reports from testers and my explanation of why they
saw that behavior. They are aware that it can be worked around, but
that's not acceptable if there's no reliable way to find all
occurrences of patterns that hit this bug. To keep this effort
alive here, I am using my quick hack for 9.0 and 9.1. Deletes will
behave as they expect, and updates won't quietly discard part of the
work of a transaction -- an error will be thrown in that situation.
> It's been like this forever
As have many other data mangling bugs which we fix in minor
releases. Our point here is that it's never been like this in any
product that the Wisconsin Courts has used for triggers, and never
will be.
> People who would otherwise be tempted to write their applications
> this way will end up not doing so precisely because it currently
> does not work.
I can attest to that.
> So I think we should focus on getting the right semantics here,
> rather than trying to minimize the scope of the change.
Agreed. I thought that's what we were talking about.
> I'm not sure I understand the justification for throwing an error.
Nobody has suggested sane semantics for doing otherwise. There is
the pipe dream of what application programmers here would like,
described earlier in the thread. I just don't see that happening,
and I'm not sure it addresses all of Florian's concerns anyway. You
had a suggestion for how to salvage the update situation, but it was
pretty hand-wavy, and I find it very hard to reconcile to some of
the issues raised by Florian. Maybe someone can propose sane
semantics which covers all the objections, and maybe that would even
be possible to implement; but right now Florian's approach seems
like the most solid proposal yet put forward.
> Updating a row twice is not an error under any other
> circumstances; nor is deleting a row you've updated. Why should
> it be here?
As Florian pointed out, it's not exactly a matter of doing those
things. If you have a proposal which addresses the issues raised
earlier in the thread, please share.
-Kevin