Re: Error on failed COMMIT

On Sun, Feb 23, 2020 at 7:59 PM Dave Cramer <davecramer(at)postgres(dot)rocks> wrote:
>
> I think the fact that this is a violation of the SQL SPEC lends considerable credence to the argument for changing the behaviour.
> Since this can lead to losing a transaction I think there is even more reason to look at changing the behaviour.

The assumption that COMMIT terminates the transaction is going to be
deeply embedded into many applications. It's just too convenient not
to rely on. For example, I maintain a bash based deployment framework
that assembles large SQL files from bit and pieces and tacks a COMMIT
at the end. It's not *that* much work to test for failure and add a
rollback but it's the kind of surprise our users hate during the
upgrade process.

Over the years we've tightened the behavior of postgres to be inline
with the spec (example: Tom cleaned up the row-wise comparison
behavior in 8.2) but in other cases we had to punt (IS NULL/coalesce
disagreement over composites for example), identifier case sensitivity
etc. The point is, changing this stuff can be really painful and we
have to evaluate the benefits vs the risks.

My biggest sense of alarm with the proposed change is that it could
leave applications in a state where the transaction is hanging there
it could previously assume it had resolved; this could be catastrophic
in impact in certain real world scenarios. Tom is right, a GUC is the
equivalent of "sweeping the problem under the wrong" (if you want
examples of the long term consequences of that vision read through
this: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html).
The value proposition of the change is however a little light
relative to the risks IMO.

I do think we need to have good page summarizing non-spec behaviors in
the documentation however.

merlin