Search code examples
databasepostgresqltransactionslibpq

Is it required to execute 'ROLLBACK' upon error?

I'm a newbie with PostgreSQL/libpq. So please help me clarify my confusion:

Assuming I start by executing a 'START TRANSACTION' and do proper error checking (PQresultStatus(res) != [proper_success_value]), am I required to execute a 'ROLLBACK' if something goes wrong after I make an insertion? For example:

  • START TRANSACTION : OK
  • INSERT .. : OK
  • UPDATE .. : FAIL

In this case am I required to execute a 'ROLLBACK' after 'UPDATE' fails? Also what do I do if 'ROLLBACK' also fails?

Solution

  • This really is best understood by reading the manual:

    https://www.postgresql.org/docs/current/static/tutorial-transactions.html

    ROLLBACK TO is the only way to regain control of a transaction block that was put in aborted state by the system due to an error, short of rolling it back completely and starting again.

    Rollback should not be able to fail.

    Transactions are placed in an aborted state meaning you can't carry on doing anything in that transaction. You can use a save point to recover that transaction but other than that all you can do is rollback.