Re: Error on failed COMMIT

Am 12.02.2020 um 00:27 schrieb Tom Lane:
> Vik Fearing <vik@postgresfriends.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