By transaction I mean several SQL statements wrapped (for example) in a begin isolation level serializable block. A concurrent transaction can make this transaction fail, i.e. rollback.
How to restart the failed transaction in PostgreSQL?
-
1You have to watch for such an event and be ready to start the same transaction again from the client, in case the action is still valid after the failure, of course.Kouber Saparev– Kouber Saparev2013-12-14 17:42:10 +00:00Commented Dec 14, 2013 at 17:42
-
But do all clients get notified when a transaction failed? I am using node-postgres (node.js) but I am unsure whether transaction failure throws an exception.ehmicky– ehmicky2013-12-14 20:12:25 +00:00Commented Dec 14, 2013 at 20:12
-
1pg-promise supports nested transactions, and those become save points on nested levels, which you can use to restore your transaction.vitaly-t– vitaly-t2017-01-29 12:44:32 +00:00Commented Jan 29, 2017 at 12:44
Add a comment
|
2 Answers
You need to use client-driver specific mechanisms to detect an error in the transaction. When you see an error you must rollback the transaction and re-issue the entire transaction. You can't just re-do the most recent part1.
The ROLLBACK is required; if you don't do it, any further operations on that connection fail with a "transaction aborted" error.
Most client drivers either throw exceptions (in languages that support them) to indicate SQL errors, or expect you to check for an error code after every database operation, either by checking a function return code, or by calling a special function to check for errors. node-postgres is asynchronous and non-blocking, so it's not especially likely to throw exceptions; you should be looking for a function that lets you query a session for the last operation's resulting SQLSTATE and client driver error status.
node-postgres doesn't seem to have abundant documentation so your best bet may be to examine the source code for node-postgres, or find examples of how other people do this. I expect you'll find functions to check the error state of a session. What you're looking for is a function to get the SQLSTATE for the last operation on the connection.
It may be worth posting a question that's focused specifically on how to detect and handle errors in node-postgres.
1 Actually, SAVEPOINT and ROLLBACK TO SAVEPOINT let you do exactly that, but they won't help with serialization errors.
1 Comment
ehmicky
That's actually the kind of answer I wanted. Thanks a lot!
This answer was added 3 years later, to account for changes since. And even though the original answer is still valid, this answer shows how easy it is to do today with the right tools that weren't available back then.
It is easy to do with pg-promise, which supports nested transactions of any nested level. See Nested Transactions where it explains that while the top-level transaction is represented by your standard BEGIN->COMMIT/ROLLBACK, all nested transactions automatically become SAVEPOINT.
db.tx(t => {
// BEGIN
// top-level changes cannot be restarted:
return t.any('UPDATE users SET name=$1 WHERE id=$2', ['Mike', 123])
.then(() => {
return t.tx(t1 => {
// SAVEPOINT
return t1.none('INSERT log(event) VALUES($1)', 'entry');
})
.catch(error => {
// ROLLBACK TO SAVEPOINT executed
return t.none('UPDATE log SET event = $1');
});
});
})
.then(data => {
// success, COMMIT executed
})
.catch(error => {
// error, ROLLBACK executed
});
In the example above we do an UPDATE on the top level, which cannot be restarted for that reason. And then we do a nested transaction/savepoint, with an INSERT, which if fails, replaced with an UPDATE on the top level again.
This way, even though our SAVEPOINT failed, we can still finish our top-level transaction successfully.
And if you want to be able to restart the entire transaction, simply wrap the whole thing into a sub-transaction, which you then can re-run inside the main transaction as many times as you want.