Hello all,
I've got a question about the state in which a session/transaction finds itself in case a COMMIT statement fails. I hope this is a good mailing list to ask it on.
Is it true that a failed COMMIT behaves just like a ROLLBACK statement would have if it was issues in its stead? In other words, is it true that the transaction is rolled back (as it is on every error) and the session will accept new statements without the need for the client to issue a ROLLBACK?
And to extend the question a bit:
Is is true that, if a client uses the "simple query" flavor of the pgwire protocol and sends multiple SQL commands in a single query string looking like "BEGIN; <stmt 1>; <stmt 2>;...;COMMIT" then, when the result(s) come and there was an error somewhere, the session can be in either of two states:
- in an error state if the error was encountered by one of stmt1, stmt2, etc. Meaning that future statements sent on future query strings will be rejected with "ERROR: current transaction is aborted, commands ignored until end of transaction block" and the client needs to send a ROLLBACK to get itself out of this sticky situation?
- in a regular state, accepting new statements, if the error was encountered by COMMIT.
If everything I've said before is true, is there any guidance / best practices that developers and/or client drivers should implement for dealing with this "should I send a ROLLBACK so I can continue to use my connection or not?" question when receiving an error after sending a multi-statement query string that contains a COMMIT in it?
Thank you very much,
- Andrei