Re: Error on failed COMMIT

On Fri, 14 Feb 2020 at 14:37, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> 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. If the server provides you with status information and you
>> throw it out instead of passing it along to the application, that's
>> not ideal.
>>
>
> Well con.commit() returns void :(
>

I'd like to second Dave on this, from the .NET perspective - actual client
access is done via standard drivers in almost all cases, and these drivers
generally adhere to database API abstractions (JDBC for Java, ADO.NET for
.NET, and so on). AFAIK, in almost all such abstractions, commit can either
complete (implying success) or throw an exception - there is no third way
to return a status code. It's true that a driver may expose NOTICE/WARNING
messages via some other channel (Npgsql emits .NET events for these), but
this is a separate message "channel" that is disconnected API-wise from the
commit; this makes the mechanism very "undiscoverable".

In other words, if we do agree that there are some legitimate cases where a
program may end up executing commit on a failed transaction (e.g. because
of a combination of framework and application code), and we think that a
well-written client should be aware of the failed transaction and behave in
an exceptional way around a non-committing commit, then I think that's a
good case for a server-side change:

- Asking drivers to do this at the client have the exact same breakage
impact as the server change, since the user-visible behavior changes in the
same way (the change is just shifted from server to driver). What's worse
is that every driver now has to reimplement the same new logic, and we'd
most probably end up with some drivers doing it in some languages, and
others not doing it in others (so behavioral differences).
- Asking end-users (i.e. application code) to do this seems even worse,
as every user/application in the world now has to be made somehow aware of
a somewhat obscure and very un-discoverable situation.

Shay