Re: Error on failed COMMIT

Am 12.02.2020 um 00:27 schrieb Tom Lane:
> Vik Fearing <vik(at)postgresfriends(dot)org> writes:
>> Actually, I was imagining that it would end the transaction as it does
>> today, just with an error code.
>> This is backed up by General Rule 9 which says "The current
>> SQL-transaction is terminated."
> Hm ... that would be sensible, but I'm not entirely convinced. There
> are several preceding rules that say that an exception condition is
> raised, and normally you can stop reading at that point; nothing else
> is going to happen. If COMMIT acts specially in this respect, they
> ought to say so.
>
> In any case, while this interpretation might change the calculus a bit,
> I think we still end up concluding that altering this behavior has more
> downside than upside.

Let me illustrate this issue from an application (framework) developer's
perspective:

When an application interacts with a database, it must be clearly
possible to determine, whether a commit actually succeeded (and made all
changes persistent), or the commit failed for any reason (and all of the
changes have been rolled back). If a commit succeeds, an application
must be allowed to assume that all changes it made in the preceeding
transaction are made persistent and it is valid to update its internal
state (e.g. caches) to the values updated in the transaction. This must
be possible, even if the transaction is constructed collaboratively by
multipe independent layers of the application (e.g. a framework and an
application layer). Unfortunately, this seems not to be possible with
the current implementation - at least not with default settings:

Assume the application is written in Java and sees Postgres through the
JDBC driver:

composeTransaction() {
   Connection con = getConnection(); // implicitly "begin"
   try {
      insertFrameworkLevelState(con);
      insertApplicationLevelState(con);
      con.commit();
      publishNewState();
   } catch (Throwable ex) {
      con.rollback();
   }
}

With the current implementation, it is possible, that the control flow
reaches "publishNewState()" without the changes done in
"insertFrameworkLevelState()" have been made persistent - without the
framework-level code (which is everything except
"insertApplicationLevelState()") being able to detect the problem (e.g.
if "insertApplicationLevelState()" tries add a null into a non-null
column catching the exception or any other application-level error that
is not properly handled through safepoints).

From a framework's perspective, this behavior is absolutely
unacceptable. Here, the framework-level code sees a database that
commits successfully but does not make its changes persistent.
Therefore, I don't think that altering this behavior has more downside
than upside.

Best regards

Bernhard