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!

You are referring to library pg-promise, but you are not using its support for transactions - method tx? In this case we can't be sure what is going on, as you do not show the complete code of what you are doing. Perhaps it is best that you use method tx, and execute queries in it as shown in all examples. See Transactions. This will make your code more predictable, since you do not provide a proper ROLLBACK logic in your SQL anyway. — vitaly-t
– vitaly-t, Commented Jul 25, 2016 at 2:01

vitaly-t · Accepted Answer · 2020-06-19 13:54:21Z

5

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...');
});

edited Jun 19, 2020 at 13:54

answered Jul 25, 2016 at 2:14

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

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

3 Comments

mbhuiyan Over a year ago

Ah, that solution is what I was approaching. Just wanted to make sure there wasn't a solution that would allow me to keep everything in one SQL file. I'll go ahead and implement it the way you outlined above. Thanks! :)

vitaly-t Over a year ago

@mbhuiyan there can be a single-file a solution, but it would be way more awkward to implement and use, not really worth doing when a pg-promise transaction is so much easier to use.

Will Ediger Over a year ago

@vitaly-t Thank you so much for your work in making pg-promise, and thank you for answering so many postgres questions on SO and explaining how pg-promise can help solve the problem.

Collectives™ on Stack Overflow

Postgresql Catching Transaction Error and Rollback

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related