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? -- Bruce Momjian <br...@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +