mbhuiyan mbhuiyan - 1 year ago 146
Node.js Question

Postgresql Catching Transaction Error and Rollback

I am using pg-promise to run my SQL queries. The queries themselves are stored in external .sql files.

When I execute a transaction, Postgres will abort the transaction if an error occurs (as expected). The problem that I'm running into is any separate queries I try to run after the transaction is aborted are not run and I instead get this message:
"current transaction is aborted, commands ignored until end of transaction block". If the queries were being run in the psql console, I could solve this problem by issuing a ROLLBACK after the failed query. I don't think that's an option here since the SQL used by my application is located in an external file. I also don't think Savepoints are an option because the entire transaction should be thrown out if something fails.

How would I rollback in the SQL file if this error occurs?

Here's the SQL for reference:


FROM tournament_tossup_values
WHERE tournament_id = $1 AND
FROM tournament_match
WHERE tournament_id = $1

UPDATE tournament
SET bonus_point_value = $5, parts_per_bonus = $6
FROM tournament_match
WHERE tournament_id = $1
RETURNING bonus_point_value, parts_per_bonus; <-- Any subsequent accesses to the database by the application fail if this transaction fails

COMMIT; <-- I want to rollback everything if this fails

Thank you in advance!

Answer Source

When implementing a transaction in an external SQL file you need to provide all the proper handling for COMMIT and ROLLBACK. When you do not do that, the transaction status may become unpredictable inside your server-side code, and result in the type of errors that you are getting.

This can be a bit tricky, and easier said than done. This is why the best solution is not to do it at all.

Module pg-promise that you are already using provides reliable handling for transactions, via method tx, which is what you should be using.

To that end, split your SQL file into two files - one with your DELETE operation and one with your UPDATE operation, and then execute them as two queries inside a transaction:

db.tx(t=> {
    return t.batch([
    .then(data=> {
        // success;
    .catch(error=> {
        // error;