0

i'm writing a postgresql function implementing a transaction. this how the code looks like:

BEGIN TRANSACTION REPEATABLE READ
INSERT INTO table1 VALUES(value1, value2);
INSERT INTO table2 VALUES(value3);
COMMIT
END;

Now i need to check if one of the insert query return some kind of error for insert rollback. How can i do it?

giozh
  • 9,868
  • 30
  • 102
  • 183
  • 3
    You do not need to check for errors. Transaction will automatically rollback, if one of the commands throws error. – Ihor Romanchenko Jul 30 '13 at 08:03
  • So i can call rollback just in a explicit case (i.e i call a select query and return value is greater than a constant)? – giozh Jul 30 '13 at 08:11
  • Yes. But explicit rollback is rarely used. Most of the time it is better to throw an error, that will cause rollback. Such error will clearly state the cause of rollback. – Ihor Romanchenko Jul 30 '13 at 08:35
  • Transaction control cannot be used inside a postgresql function. See among others: [Commit, savepoint, rollback to in PostgreSQL?](http://stackoverflow.com/questions/5448984) – Daniel Vérité Jul 30 '13 at 11:16

1 Answers1

0

Checking for, and handling errors, is good. Do it consistently. While transactions will automatically roll back, you may want to alert the user log the problem, or take other actions. This being said the transaction will roll back on its own if there is a database error.

On the other hand, if you need to roll back a transaction because of an application error (outside the database), you issue a ROLLBACK command.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182