Re: Error on failed COMMIT - Mailing list pgsql-hackers

On Mon, 17 Feb 2020 at 13:02, Haumacher, Bernhard <haui@haumacher.de> wrote:

... would be an appropriate solution. PostgreSQL reports the
"unsuccessful" commit through the "ROLLBACK" status code and the driver
translates this into a Java SQLException, because this is the only way
to communicate the "non-successfullness" from the void commit() method.
Since the commit() was not successful, from the API point of view this
is an error and it is fine to report this using an exception.

Well it doesn't always report the unsuccessful commit as a rollback sometimes it says

"there is no transaction" depending on what happened in the transaction

Also when there is an error there is also a status provided by the backend. 

Since this is not an error to the backend there is no status that the exception can provide.

Doing this in a (non-default) driver setting is not ideal, because I
expect do be notified *by default* from a database (driver) if a commit
was not successful (and since the API is void, the only notification
path is an exception). We already have a non-default option named
"autosafe", which fixes the problem somehow.

The challenge with making this the default, is as Tom noted, many other people don't expect this.

Nobody expects a database reporting a successful commit, while it internally rolled back.

If there is code out there depending on this bug, it is fair to provide a backwards-compatible option to re-activate this unexpected behavior.

What many other frameworks do is have vendor specific behaviour. 

Perhaps writing a proxying driver might solve the problem?

That's exactly what we do - extending our database abstraction layer to work around database-specific interpretations of the JDBC API.

But of cause, the abstraction layer is not able to reconstruct an error from a commit() call, that has been dropped by the driver. Of cause, I could try to insert another dummy entry into a dummy table immediately before each commit to get again the exception reporting that the transaction is in rollback-only-mode... but this does not sound reasonable to me.

If we really need both behaviors ("silently ignore failed commits" and
"notify about failed commits") I would prefer adding a
backwards-compatible option
"silently-ignore-failed-commit-due-to-auto-rollback" (since it is a
really aburd setting from my point of view, since consistency is at risk
if this happens - the worst thing to expect from a database).

The error has been reported to the client. At this point the client is expected to do a rollback.

As I explained, there is not "the client" but there are several software layers - and the error only has been reported to some of these layers that may decide not to communicate the problem down the road. Therefore, the final commit() must report the problem again.

Best regard, Bernhard