Node Postgresql How to Prevent Transaction from Aborting

Question

I am using pg/transaction to handle a complex process. It can be simplified as this:

async() => {
    const client = await new Pool().connect();
    try {
        await client.query("BEGIN");
        await client query("query 1");
        try {
            await client query("query 2");
            // Some error happen
        } catch(err) {
            console.log(err);
        }
        await client query("query 3");
        await client query("COMMIT");
    } catch(err) {
        await client.query("ROLLBACK");
    } finally {
        client.release
    }
}

The problem is that when query2 returns an error, the client fails to execute query3 even I don't Rollback the process. The error it returns is current transaction is aborted, commands ignored until end of transction block. Is there any way to prevent the transaction from being aborted when a query error happends?

Richard Huxton · Accepted Answer · 2019-02-01 21:05:03Z

2

This is the default behaviour because it is the whole point of a transaction. If you don't want queries 1,2,3 to all succeed or all fail together you probably don't want them in the same transaction.

However, if what you want is really "1 and (2 or 3)" then you should put a SAVEPOINT after query 1 and rollback to that savepoint in the event of an error in query 2.

answered Feb 1, 2019 at 21:05

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Node Postgresql How to Prevent Transaction from Aborting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related