9

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?

AcarX
  • 289
  • 2
  • 10

1 Answers1

9

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.

Philip Couling
  • 13,581
  • 5
  • 53
  • 85
  • 2
    If I understand this correctly, I wouldn't be able to actually execute other commands before I rollback and complete the ongoing transaction? Would this also mean that upon a failure, ongoing transaction wouldn't be terminated until a rollback/commit is executed? – AcarX Jun 25 '17 at 18:39
  • 2
    Yes I believe so. Remember that closing a database connection will implicitly rollback any open transaction. – Philip Couling Jun 25 '17 at 19:09