Thread: PostgreSQL transaction aborted on SQL error

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

Urko Lekuona schrieb am 04.08.2020 um 10:44:
> 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.

Which is exactly how a transaction is defined:

Either all statements are successful or none.


> If you run it, you'd see that when the unique key constraint is
> violated, my transaction is stopped

You can use INSERT ON CONFLICT DOT NOTHING to avoid that.


> make it work like other RDBMS do, where if a statement failed, the
> transaction could continue

Which completely violates the idea of a transaction.

The choices you have are:

* use auto-commit
* make sure your inserts don't throw an error
* use manual savepoints around each statement (don't forget DDL statements!)

Thomas