Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Well, it seems to me that if the trigger update and the main
> update were executed as separate commands (with no triggers
> involved) it would often be the case that they'd dovetail nicely.
> When this has come up for me, it's usually been the case that the
> sets of fields being updated are completely non-overlapping.
Agreed that this is typically the case -- that's why the application
programmers here expected NEW to be effectively a dynamic
representation of the WIP state of the row. A lot of things would
"just work" that way. Of course, they're blissfully unaware of what
a huge revamp of the guts of PostgreSQL that would be.
> So ideally what I'd like to happen is to have EPQ, or something
> like it, test whether the newest version of the row still
> satisfies the UPDATE criteria. If so, it applies the update to
> the new row version; if not, it either discards the main UPDATE or
> throws an error. There's still some room here for surprising
> results, but I think they would be surprising results arising out
> of having done something intrinsically complicated, rather than
> surprising results arising out of an odd implementation artifact.
So, you're advocating a "logical merge" of the results with
something exceptional done on a conflicting update of the same
columns? That would effectively get you to the same end result as a
"live" NEW tuple, but without such a radical revamp of the guts of
things. Still, not trivial to do properly, and I would argue for
throwing an error rather than silently doing something surprising on
conflict.
This issue has already forced the rearrangement of our release
schedule here, so I'm going to do the simple fix of just throwing an
error on update from the BEFORE UPDATE trigger (of the row for with
the trigger is firing). That fix is very simple and seems very safe
to me, and should allow us to deploy without further schedule
slippage; then I'll see if I can code up what you're suggesting. I
had a new patch I was about to post with new error language, a
different SQLSTATE, comments, and regression test changes; but
unless someone wants to see that I won't clutter the list with it
until I've had a chance to see if I can manage to handle it the way
you're requesting.
There's no doubt that it would be better the way you're suggesting;
but it looks to me like about five times as many lines of code,
harder to be sure it's right, and probably forcing me to learn a few
new subsystems of PostgreSQL internals to accomplish.
Thanks for the feedback.
-Kevin