På tirsdag 04. august 2020 kl. 10:44:36, skrev Urko Lekuona <urko@arima.eu>:
Hello,
First time writing here, I hope this is the right place to ask this kind of question. I've been working with PostgreSQL for a while now but i've just found out that PostgreSQL marks my transaction for ROLLBACK and even stops the execution of the transaction if an error occurs.
I'm a Java developer and I'm using JDBC to connect to PostgreSQL. I've made a gist to showcase this behavior (https://gist.github.com/UrkoLekuona/4be14e099aeec2e7d8ef616886b66fdf/). If you run it, you'd see that when the unique key constraint is violated, my transaction is stopped, i.e. the SELECT and DROP statements are not executed. The thrown exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
I've purposely set AutoCommit to false, because in my real life use case this is not an option. The only workaround I've found for this exception is setting the connection property autosave to ALWAYS, (https://jdbc.postgresql.org/documentation/head/connect.html).
My question is: is this the correct way of solving this issue? I'd rather if there was a PostgreSQL flag to disable this behavior and make it work like other RDBMS do, where if a statement failed, the transaction could continue without explicitly marking a savepoint and rolling back.
Thanks in advance for your help, it is appreciated.
Urko
The correct approach is to ROLLBACK the transaction in a "catch-block" instead of trying to execute further statements. The java.sql.Connection is "invalid" after an SQLException and should be rolled back.