Rollback after transaction error

Question

This should be easy for anyone familiar with Postgresql:

My application issues a begin_work , performs a series of operations, and then issues commit . Operations and commit are completed inside the try-catch block, whose catch statement performs rollback . Assumption: if an error occurs during the SQL operation, Postgresql will automatically roll back from the transaction, and therefore my rollback will be redundant , but harmless. Is this assumption correct?

(The reason I'm rolling back anyway: just in case, an exception not related to the SQL ocurs operation.)

Answer 1

If an error occurs, PostgreSQL does not actually roll back the transaction. It does not fulfill all subsequent statements with an error. You can try this in the client.

You need to roll back before any statements can be successfully executed.

In the event that you close the connection and start a new one, it does not really matter. However, if you save the connection and run other statements that you expect to execute successfully, this will not work.