Re: Error on failed COMMIT

On Sun, 23 Feb 2020 at 00:41, Shay Rojansky <roji(at)roji(dot)org> wrote:

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

To be fair this is Bernhard's position which, after thinking about this
some more, I am endorsing.

So we now have two of the largest client bases for PostgreSQL with known
issues effectively losing data because they don't notice that the commit
failed.
It is very likely that this occurs with all clients but they just don't
notice it. That is what is particularly alarming about this problem is that
we are silently ignoring an error.

While we can certainly code around this in the client drivers I don't
believe they should be responsible for fixing the failings of the server.

I fail to see where doing the right thing and reporting an error where
there is one should be trumped by not breaking existing apps which by all
accounts may be broken but just don't know it.

Dave