Re: Error on failed COMMIT

On Mon, 17 Feb 2020 at 13:02, Haumacher, Bernhard <haui(at)haumacher(dot)de> wrote:

> Am 14.02.2020 um 20:36 schrieb Robert Haas:
> > On Fri, Feb 14, 2020 at 2:08 PM Dave Cramer <davecramer(at)postgres(dot)rocks>
> wrote:
> >> Well now you are asking the driver to re-interpret the results in a
> different way than the server which is not what we tend to do.
> >>
> >> The server throws an error we throw an error. We really aren't in the
> business of re-interpreting the servers responses.
> > I don't really see a reason why the driver has to throw an exception
> > if and only if there is an ERROR on the PostgreSQL side. But even if
> > you want to make that rule for some reason, it doesn't preclude
> > correct behavior here. All you really need is to have con.commit()
> > return some indication of what the command tag was, just as, say, psql
> > would do.
>
> I think, this 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.

>
> Am 14.02.2020 um 21:07 schrieb Tom Lane:
> > Dave Cramer <davecramer(at)postgres(dot)rocks> writes:
> >> We have the same blast radius.
> >> I have offered to make the behaviour requested dependent on a
> configuration
> >> parameter but apparently this is not sufficient.
> > Nope, that is absolutely not happening. We learned very painfully, back
> > around 7.3 when we tried to put in autocommit on/off, that if server
> > behaviors like this are configurable then most client code has to be
> > prepared to work with every possible setting. The argument that "you can
> > just set it to work the way you expect" is a dangerous falsehood. I see
> > no reason to think that a change like this wouldn't suffer the same sort
> > of embarrassing and expensive failure that autocommit did.
>
> 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.

I think the notion that every JDBC driver works exactly the same way for
every API call is a challenge.
Take for instance SERIALIZABLE transaction isolation.
Only PostgreSQL actually implements it correctly. AFAIK Oracle SERIALIZABLE
is actually REPEATABLE READ

What many other frameworks do is have vendor specific behaviour.
Perhaps writing a proxying driver might solve the problem?

> 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.

Regards,
Dave