postgresql transaction: check for query error

Question

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?

You do not need to check for errors. Transaction will automatically rollback, if one of the commands throws error. — Ihor Romanchenko
– Ihor Romanchenko, Commented Jul 30, 2013 at 8: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
– giozh, Commented Jul 30, 2013 at 8: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
– Ihor Romanchenko, Commented Jul 30, 2013 at 8:35
Transaction control cannot be used inside a postgresql function. See among others: Commit, savepoint, rollback to in PostgreSQL? — Daniel Vérité
– Daniel Vérité, Commented Jul 30, 2013 at 11:16

Chris Travers · Accepted Answer · 2013-11-03 01:45:56Z

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.

answered Nov 3, 2013 at 1:45

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql transaction: check for query error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related