Postgresql Catching Transaction Error and Rollback

Question

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:

BEGIN;

DELETE 
FROM tournament_tossup_values
WHERE tournament_id = $1 AND
NOT EXISTS
(
    SELECT id
    FROM tournament_match
    WHERE tournament_id = $1
);

UPDATE tournament
SET bonus_point_value = $5, parts_per_bonus = $6
WHERE id = $1 AND NOT EXISTS (
    SELECT id 
    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 1

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.

From the tx method documentation:

Executes a callback function as a transaction (...)

A transaction wraps a regular task into additional queries:

it executes BEGIN just before invoking the callback function

it executes COMMIT, if the callback didn't throw any error or return a rejected promise, it executes ROLLBACK, if the callback did throw an error or return a rejected promise

it executes corresponding SAVEPOINT commands when the method is called recursively.

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:

await db.tx(async t => {
    await t.none('DELETE...');
    await t.any('UPDATE...');
});