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.
