6

This should be an easy one for those familiar with Postgresql:

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

(The reason why I'm rollbacking anyway: just in case an exception unrelated to a SQL operation ocurs.)

Jon Smark
  • 2,528
  • 24
  • 31

1 Answers1

4

If an error occurs, PostgreSQL does not actually rollback the transaction. It fails every subsequent statements with an error. You can try this out in the client.

You need to execute rollback before any statements can be executed successfully.

In the case that you close the connection and start a new one, this is of little consequence. However, if you retain the connection and run other statements which you expect to execute successfully, it will not work.

drone.ah
  • 1,135
  • 14
  • 28
  • So, it seems my assumption was wrong, but I was right to rollback anyway. Thanks! – Jon Smark Apr 04 '13 at 15:50
  • 2
    @JonSmark Correct. Pg marks the transaction as aborted but doesn't return to autocommit mode or open a new transaction until you issue a rollback. The reason for that is that otherwise you can have nasty situations like `INSERT INTO historytable SELECT * FROM maintable;` failing then the following `TRUNCATE TABLE maintable` succeeding. Ouch! – Craig Ringer Apr 05 '13 at 00:04