Risk of hanging transactions when using PostgresKit/PostgresNIO

Related Projects Vapor
1

I'm using PostgresKit for an AWS Swift Lambda, and I'm concerned of the possibility for hanging transactions. For example if I were to begin a transaction with the below code, and for whatever reason neither the COMMIT nor the ROLLBACK statements were able to executed, then the transaction would still be active. The defer clause ensures the connection is released once this do block is exited, but does that mean that the active transaction will no longer be able to be continued? Or is it possible that something else can silently pick up where this transaction left off and then inadvertently commit the data that the transaction was originally intended for?

do {
    let connection=try API.shared.pools.requestConnection().wait()
    defer {
        API.shared.pools.releaseConnection(connection)
    }
    let id: PostgresData = .init(uuid: .init())
    let date: PostgresData = .init(date: .init())
    let appleId: PostgresData = .init(string: "apple ID")
    do {
        let _=try connection.simpleQuery("BEGIN").wait()
        let _=try connection.query("INSERT INTO every_user (id, creation_timestamp, verified_user_id) VALUES ($1, $2, $1)", [id, date]).wait()
        let _=try connection.query("INSERT INTO verified_user (id, beginning_apple_user_id, complete_user_id) VALUES ($1, $1, null)", [id]).wait()
        let _=try connection.query("INSERT INTO beginning_apple_user (id, apple_id) VALUES ($1, $2)", [id, appleId]).wait()
    } catch {
        let _=try connection.simpleQuery("ROLLBACK").wait()
    }
    let _=try connection.simpleQuery("COMMIT").wait()
} catch {
    print(error.localizedDescription)
}
2

I would expect that releaseConnection would terminate (i.e. ROLLBACK) any in-flight transactions but we'd need @fabianfett to confirm that.

3

releaseConnection is implemented in AsyncKit's database independent EventLoopConnectionPool. For this reason no domain specific behavior is triggered when requesting or releasing a connection.

@chrisbia as a side note: In case your transaction fails, you will issue a COMMIT after the ROLLBACK. I suspect this is not intended.

4

Does this mean that the statements will still be ongoing on the database after releasing the connection and that on commit of a new transaction whose statements were executed using a new connection that those preexisting statements will be executed along with the new ones — in the same transaction?

5

Thanks for catching that!