PgClient. Commit transaction after exception.

[Tiikara]

Questions

I have encountered some strange transaction behaviour. If I execute a query after the transaction starts, create a checkpoint, then this query terminates with an exception (in my case an index constraint error). Then I rollback the checkpoint, and after that when commit transaction the system hangs. At the same time rollback works. In Debug I saw that there is a flag failed in the transaction object. Is this how it should be or am I doing something wrong?

Version

4.4.6

Context

Language

Kotlin

Packages

• io.vertx:vertx-core
• io.vertx:vertx-lang-kotlin-coroutines
• io.vertx:vertx-lang-kotlin
• io.vertx:vertx-pg-client

Location

Vertx Verticle

Code

@OptIn(DelicateCoroutinesApi::class)
fun <T> launchCoroutineOnEventLoopAsVertxFuture(block: suspend () -> T) : Future<T> {
  val vertx = Vertx.currentContext()

  val promise = Promise.promise<T>()

  GlobalScope.launch(vertx.dispatcher()) {
    try {
      promise.complete(block())
    } catch (ex: Exception) {
      promise.fail(ex)
    }
  }

  return promise.future()
}

suspend fun <T, Error> withSavepoint(sqlConnection: SqlConnection, block: suspend () -> OkOrError<T, Error>): OkOrError<T, Error> {
    sqlConnection
      .query("SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling")
      .execute()
      .await()

    try {
      val result = block()

      if(result.isError) {
        sqlConnection
          .query("ROLLBACK TO SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling")
          .execute()
          .await()
      }

      return result
    }
    catch (e: PgException) {
      sqlConnection
          .query("ROLLBACK TO SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling")
          .execute()
          .await()

      throw e
    }
    finally {
      sqlConnection
          .query("RELEASE SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling")
          .execute()
          .await()
    }
  }

pgPool.withConnection { sqlConnection ->
  launchCoroutineOnEventLoopAsVertxFuture {
    val transaction = sqlConnection.begin().await()

      try {
        val result = 
           try {
              withSavepoint(sqlConnection) {
                 functionWithDatabaseException()
              }
           } catch(e: PgException) {
              ....
             Ok(...)
           }

        if(result.isOk) {
          transaction.commit().await() // here problem
        } else {
          transaction.rollback().await()
        }

        return result
      } catch (e: Exception) {
        transaction.rollback().await()
        throw e
      }
  }
}.await()

After stuck on the line transaction.commit().await(). With a query in PostgreSQL, I can see that the last query from the application to the database is ROLLBACK.

SELECT *
FROM pg_stat_activity
WHERE application_name = 'vertx-pg-client'

PS Everything works fine if I manually make BEGIN/COMMIT requests with SqlConnection instead sqlConnection.begin()/transaction.commit().

[tsegismont]

Can you please create a small reproducer and share it on a public git repo? Ideally with testcontainers for the db setup

[Tiikara]

I'll try to make time for that next week

[vietj]

it would be good also for the reproducer to be in java and avoid kotlin

[Tiikara]

https://github.com/Tiikara/vertx_sql_client_issue1369/blob/master/src/main/java/bug/reproduce/vertx/sql/client/issue1369/ReproduceVerticle.java

I tried to reproduce the error, but in pure Java the system doesn't hang on commit. Unfortunately, I could not get the same behavior as in Kotlin. But nevertheless, after rolling back a checkpoint, the transaction is not committed, but the transaction is rolled back.

Because after the checkpoint rollback the transaction must be valid and the transaction commit must be allowed.

There are no plans to implement such behavior in the future?

withConnection: START
query("INSERT INTO users (name) VALUES ('test')"): START
query("INSERT INTO users (name) VALUES ('test')"): SUCCESS
query("SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): START
query("SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): SUCCESS
query("INSERT INTO users (name) VALUES ('test')"): START
query("INSERT INTO users (name) VALUES ('test')"): FAIL. That was planned fail on unique index: ERROR: duplicate key value violates unique constraint "users_name_key" (23505)
Now we should rollback savepoint and commit transaction
query("ROLLBACK TO SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): START
query("ROLLBACK TO SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): SUCCESS
query("RELEASE SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): START
query("RELEASE SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): SUCCESS
commit(): START
commit(): FAIL: io.vertx.sqlclient.TransactionRollbackException: Rollback
query("RELEASE SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): FAIL: Rollback
query("ROLLBACK TO SAVEPOINT AsyncSqlConnectionPgSavepointWithErrorHandling"): FAIL: Rollback
begin: FAIL: io.vertx.sqlclient.TransactionRollbackException: Rollback
withConnection: FAIL: io.vertx.sqlclient.TransactionRollbackException: Rollback