Fourth, it is not clear how many applications would break if COMMIT started issuing an error rather than return success a with ROLLBACK tag. Certainly SQL scripts would be fine. They would have one additional error in the script output, but if they had ON_ERROR_STOP enabled, they would have existed before the commit. Applications that track statement errors and issue rollbacks will be fine. So, we are left with applications that issue COMMIT and expect success after a transaction block has failed. Do we know how other database systems handle this?
Well I know pgjdbc handles my patch fine without any changes to the code
As I mentioned upthread 2 of the 3 go drivers already error if rollback is returned. 1 of them does not.
I suspect npgsql would be fine. Shay ?
Npgsql would be fine. In fact, Npgsql doesn't have any specific expectations nor any specific logic around commit; it assumes errors may be returned for any command (COMMIT or otherwise), and surfaces those errors as .NET exceptions. The transaction status is tracked via CommandComplete only, and as mentioned several times, PostgreSQL can already error on commit for various other reasons (e.g. deferred constraint checks). This direction makes a lot of sense to me.