Hikari connection pool does not recover from sql error : current transaction is aborted, commands ignored until end of transaction block

Question

I am using the below function to execute a transaction of the for BEGIN; QUERY 1; QUERY 2; COMMIT

  def transform(transformFile: String): Unit = {
    val db = PostgresqlHikari.db
    try {
      val dbConfig = ConfigFactory.load().getConfig("db.dwh")
      val schema = dbConfig.getString("schema")
      logger.info("Set connection schema ----> " + schema)

      val query = Source.fromResource("transformation/" + transformFile + ".sql")
        .getLines().mkString(System.lineSeparator())
      val preparedStatement: PreparedStatement = db.prepareStatement(query)
      preparedStatement.execute()
      //db.close()
    } catch {
      case ex: Throwable ⇒
        {
          logger.error(s"Error while processing $transformFile. Detailed error log ---" + ex.toString)
          //db.close()
        }
    }
  }

When there is no sql error, it runs fine. But, if I encounter an error, the actor just stops processing. The error that I continously get is :

  org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

If I restart the service, it again starts processing correctly until the next query error occurs.

I am using the Hikari connection pool.

Answer 1

The actor uses the db connection to execute a transaction. Now, if there is a sql error, the connection should rollback the changes. There are only 2 path to end a sql transaction, either commit or rollback.

I added the rollback logic in my catch block and now the error is not occurring any longer:

  def transform(transformFile: String): Unit = {
    val db = PostgresqlHikari.db
    try {
      val dbConfig = ConfigFactory.load().getConfig("db.dwh")
      val schema = dbConfig.getString("schema")
      logger.info("Set connection schema ----> " + schema)

      val query = Source.fromResource("transformation/" + transformFile + ".sql")
        .getLines().mkString(System.lineSeparator())
      val preparedStatement: PreparedStatement = db.prepareStatement(query)
      preparedStatement.execute()
      //db.close()
    } catch {
      case ex: Throwable ⇒
        {
          logger.error(s"Error while processing $transformFile. Detailed error log ---" + ex.toString)
          //db.close()
          val rollback: PreparedStatement = db.prepareStatement("rollback;")
          rollback.execute()
        }
    }
  }