Re: Error on failed COMMIT

Bruce, thanks for taking the time to summarize.

Bruce>Fourth, it is not clear how many applications would break if COMMIT
Bruce>started issuing an error rather than return success

None.

Bruce>applications that issue COMMIT and expect success after a transaction
Bruce>block has failed

An application must expect an exception from a COMMIT statement like any
other SQL.

Wire protocol specification explicitly says implementations must expect
error messages at any time.

---

Bruce>Do we know how other database systems handle this?

Oracle DB produces an error from COMMIT if transaction can't be committed
(e.g. failure in the processing of "on commit refresh materialized view").

---

The bug is "deferred constraint violation" and "non-deferred constraint
violation" end up with
**different** behavior for COMMIT.

deferred violation produces an error while non-deferred violation produces
"silent rollback".

In other words, there are already cases in PostgreSQL when commit produces
an error. It is nothing new.
The new part is that PostgreSQL must not produce "silent rollbacks".

Bruce>First, Vik reported that we don't follow the SQL spec

+1

Bruce>Second, someone suggested that if COMMIT throws an error, that future
Bruce>statements would be considered to be in the same transaction

No. Please disregard that. That is ill. COMMIT (and/or ROLLBACK) must
terminate the transaction in any case.
The transaction must not exist after COMMIT finishes (successfully or not).
The same for PREPARE TRANSACTION. If it fails, then the transaction must be
clear.

A litmus test is "deferred constraint violation". It works Ok in the
current PostgreSQL.
If the database can't commit, it should respond with a clear error that
describes the reason for the failure.

Bruce>Third, the idea that individual interfaces, e.g. JDBC, should throw

Individual interfaces should not deviate from server behavior much.
They should convert server-provided errors to the language-native format.
They should not invent their own rules to convert server messages to errors.
That would provide a uniform PostgreSQL experience for the end-users.

Note: there are even multiple JDBC implementations for PostgreSQL, so
slight differences in transaction handling
is the very last "feature" people want from PostgreSQL database.

Vladimir