Sv: PostgreSQL transaction aborted on SQL error

På tirsdag 04. august 2020 kl. 10:44:36, skrev Urko Lekuona <urko(at)arima(dot)eu
<mailto:urko(at)arima(dot)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/
<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 propertyautosave to ALWAYS, (
https://jdbc.postgresql.org/documentation/head/connect.html
<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.

--
Andreas Joseph Krogh