Re: Error on failed COMMIT

On Thu, Feb 13, 2020 at 2:38 AM Haumacher, Bernhard <haui(at)haumacher(dot)de> wrote:
> 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.

I am not sure that this example really proves anything. If
insertFrameworkLevelState(con), insertApplicationLevelState(con), and
con.commit() throw exceptions, or if they return a status code and you
check it and throw an exception if it's not what you expect, then it
will work. If database errors that occur during those operations are
ignored, then you've got a problem, but it does not seem necessary to
change the behavior of the database to fix that problem.

I think one of the larger issues in this area is that people have
script that go:

BEGIN;
-- do stuff
COMMIT;
BEGIN;
-- do more stuff
COMMIT;

...and they run these scripts by piping them into psql. Now, if the
COMMIT leaves the session in a transaction state, this is going to
have pretty random behavior. Like your example, this can be fixed by
having proper error checking in the application, but that does require
that your application is something more than a psql script.

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