Transaction aborted after catched error

Related Projects Vapor
1

Hi all,

I'm trying to import a csv-based textfile into a PostgreSQL database with Swift, Vapor and Fluent.

While importing data, several distinct errors might occur, some of them are acceptable, others aren't. Because of the latter, I'm running the import within a transaction, so I'm able to rollback all inserts, updates and deletes in case one of those non-acceptable errors occurs.

My code catches the acceptable errors, for example if a file is reimported and a duplicate key leads to a constraint violation. At least, the code should catch them.

Unfortunately I'm currently facing the problem, that - after one of those constraint violation errors - the transaction seems to be aborted although I've caught the error.

Here is a shortened code snippet:

…
do {
    try await productColorSize.save(on: transaction)
} catch let databaseError as DatabaseError {
    if databaseError.isConstraintFailure {
        print("CAUTION: Constraint error: productColorSize already exists.")
    } else {
        print(databaseError)
    }
}
                                
do {
    try await gtin.save(on: transaction)
} catch let databaseError as DatabaseError {
    if databaseError.isConstraintFailure {
        print("error while saving gtin")
    } else {
        print("error: " + String(reflecting: databaseError))
    }
}
…

The call of gtin.save results in the following message, when the preceding productColorSize.save has led to a contraint violation:

error: PSQLError(code: server, serverInfo: [sqlState: 25P02, file: postgres.c, line: 1498, message: current transaction is aborted, commands ignored until end of transaction block,…

My expectation is, that the transaction should still be available as long as I catch an error. Am I wrong?

Thanks for hints
Lars

2

A Google search about PostgreSQL constraint failures and aborted transactions makes me conclude that this is a feature (that some people find annoying). The workaround seems to be savepoints, because a failed savepoint can be rollbacked without aborting the whole transaction.

Another Google search for Fluent and savepoints does not give a lot of results. You may have to perform raw SQL and handle savepoints manually, or to avoid triggering the conflict failures by checking if a save would trigger one before running it.

3

Confirmed.

First a transaction aborted by a failure:

# CREATE TABLE test (a INT);
# CREATE UNIQUE INDEX test_a ON test(a);
# BEGIN;

*# INSERT INTO test VALUES (1); // Make a valid insert

*# INSERT INTO test VALUES (1); // Trigger a failure
ERROR:  duplicate key value violates unique constraint "test_a"
DETAIL:  Key (a)=(1) already exists.

!# INSERT INTO test VALUES (2); // Make another valid insert
ERROR:  current transaction is aborted, commands ignored until end of transaction block

!# COMMIT; // Attempt to commit
ROLLBACK // <- Nope: PG won't commit.
# SELECT * FROM test;
(0 rows) // <- first insert has failed

Then a transaction which is not aborted by rollbacked savepoints:

# CREATE TABLE test (a INT);
# CREATE UNIQUE INDEX test_a ON test(a);
# BEGIN;

*# SAVEPOINT s;
*# INSERT INTO test VALUES (1);
*# RELEASE SAVEPOINT s;

*# SAVEPOINT s;
*# INSERT INTO test VALUES (1); // Trigger a failure, but inside a savepoint this time
ERROR:  duplicate key value violates unique constraint "test_a"
DETAIL:  Key (a)=(1) already exists.
!# ROLLBACK TO SAVEPOINT s; // Rollback the failed savepoint

*# SAVEPOINT s;
*# INSERT INTO test VALUES (2); // Make another valid insert
*# RELEASE SAVEPOINT s;

*# COMMIT; // Attempt to commit
COMMIT // <- Success!
# SELECT * FROM test;
(2 rows) // <- both valid inserts did succeed
1 Like
4

I notice that the PostgreSQL Fluent driver looks like it supports nested transactions. But it does not take the opportunity to open a savepoint there:

Maybe some inspiration can be taken from other ORMs here. Both ActiveRecord and Django ORM, for example, allow the user to specify that nesting should create a savepoint. Other, such as GRDB, turn nested transactions into runtime errors, but provide a dedicated savepoint api.