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

On Fri, Mar  6, 2020 at 01:12:10PM -0500, Robert Haas wrote:
> On Fri, Mar 6, 2020 at 11:55 AM Dave Cramer <davecramer@postgres.rocks> wrote:
> > There have been some arguments that the client can fix this easily.
> >
> > Turns out it is not as easy as one might think.
> >
> > If the client (in this case JDBC) uses conn.commit() then yes relatively easy as we know that commit is being executed.
>
> Right...
>
> > however if the client executes commit using direct SQL and possibly multiplexes a number of commands we would have to parse the SQL to figure out what is being sent. This could include a column named commit_date or a comment with commit embedded in it. It really doesn't make sense to have a full fledged PostgreSQL SQL parser in every client. This is something the server does very well.
>
> That's true. If the command tag is either COMMIT or ROLLBACK then the
> statement was either COMMIT or ROLLBACK, but Vladimir's example query
> /*commit*/rollback does seem like a pretty annoying case. I was
> assuming that the JDBC driver required use of con.commit() in the
> cases we care about, but perhaps that's not so.

Let me try to summarize where I think we are on this topic.

First, Vik reported that we don't follow the SQL spec when issuing a
COMMIT WORK in a failed transaction.  We return success and issue the
ROLLBACK command tag, rather than erroring.  In general, if we don't
follow the spec, we should either have a good reason, or the breakage to
match the spec is too severe.  (I am confused why this has not been
reported before.)

Second, someone suggested that if COMMIT throws an error, that future
statements would be considered to be in the same transaction block until
ROLLBACK is issued.  It was determined that this is not required, and
that the API should have COMMIT WORK on a failed transaction still exit
the transaction block.  This behavior is much more friendly for SQL
scripts piped into psql.

Third, the idea that individual interfaces, e.g. JDBC, should throw an
error in this case while the server just changes the COMMIT return tag
to ROLLBACK is confusing.  People regularly test SQL commands in the
server before writing applications or while debugging, and a behavior
mismatch would cause confusion.

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?