Re: WIP fix proposal for bug #6123

On Wed, Aug 3, 2011 at 10:48 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> 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.

I don't believe this is very similar at all to the sorts of bugs we
fix in minor releases, but let's leave that aside for the moment since
it seems that we're in violent agreement about which release we're
targeting. With respect to the second part of your comment, it might
be useful to describe how this works in some of those other products.

>> 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.

It probably is, but I'm not sure we've done enough thinking about it
to be certain that it's the right way forward.

On that note, I think in some ways the problems we're hitting here are
very much like serialization anomalies. If the user updates a tuple
based on its PK and sets some non-PK field to a constant, and while
we're doing that, a BEFORE trigger updates any field in the tuple
other than the PK, then in theory it seems we ought to be able to
reconcile the updates. It feels like the result ought to be the same
as if we'd simply run the BEFORE-trigger update to completion, and
then run the main update. However, if the BEFORE-trigger modifies any
columns that the main update examined while constructing its value for
NEW, then the updates can't be serialized. There's no way to get the
same result as if you'd done either one of them first, because they
are inextricably intertwined.

In practice, my hand-wavy reference to "reconciling the updates" is a
problem because of the way the trigger interface works. It feels
pretty impossible to decide that we're going to do the update, but
with some other random values we dredged up from some other update
replacing some of the ones the user explicitly handed to us. But if
the trigger could return an indication of which column values it
wished to override, then it seems to me that we could piece together a
reasonable set of semantics. It's not exactly clear how to make that
work, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company