Catch and format SQLException

4 files changed, 43 insertions, 18 deletions

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/Main.kt b/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
index f3fffcbe..1f9a172d 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
@@ -44,6 +44,7 @@ import io.ktor.utils.io.jvm.javaio.*
 import java.time.Duration
 import java.util.zip.DataFormatException
 import java.util.zip.Inflater
+import java.sql.SQLException
 import kotlin.system.exitProcess
 import kotlinx.coroutines.*
 import kotlinx.serialization.ExperimentalSerializationApi
@@ -53,6 +54,7 @@ import net.taler.common.errorcodes.TalerErrorCode
 import org.slf4j.Logger
 import org.slf4j.LoggerFactory
 import org.slf4j.event.Level
+import org.postgresql.util.PSQLState
 import tech.libeufin.bank.AccountDAO.*
 import tech.libeufin.util.getVersion
 import tech.libeufin.util.initializeDatabaseTables
@@ -194,16 +196,36 @@ fun Application.corebankWebApp(db: Database, ctx: BankConfig) {
                 message = cause.talerError
             )
         }
+        exception<SQLException> { call, cause ->
+            val err = when (cause.sqlState) {
+                PSQLState.SERIALIZATION_FAILURE.state -> libeufinError(
+                    HttpStatusCode.InternalServerError,
+                    "Transaction serialization failure",
+                    TalerErrorCode.BANK_SOFT_EXCEPTION
+                )
+                else -> libeufinError(
+                    HttpStatusCode.InternalServerError,
+                    "Unexpected sql error with state ${cause.sqlState}",
+                    TalerErrorCode.BANK_UNMANAGED_EXCEPTION
+                )
+            }
+            logger.error(err.talerError.hint)
+            call.respond(
+                status = err.httpStatus,
+                message = err.talerError
+            )
+        }
         // Catch-all branch to mean that the bank wasn't able to manage one error.
         exception<Exception> { call, cause ->
-            cause.printStackTrace()
-            logger.error(cause.message)
+            val err = libeufinError(
+                HttpStatusCode.InternalServerError,
+                cause.message,
+                TalerErrorCode.BANK_UNMANAGED_EXCEPTION
+            )
+            logger.error(err.talerError.hint)
             call.respond(
-                status = HttpStatusCode.InternalServerError,
-                message = TalerError(
-                    code = TalerErrorCode.GENERIC_INTERNAL_INVARIANT_FAILURE.code,
-                    hint = cause.message
-                )
+                status = err.httpStatus,
+                message = err.talerError
             )
         }
     }
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
index 892e0602..417d0275 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
@@ -21,6 +21,7 @@ package tech.libeufin.bank
 
 import org.postgresql.jdbc.PgConnection
 import org.postgresql.ds.PGSimpleDataSource
+import org.postgresql.util.PSQLState
 import org.slf4j.Logger
 import org.slf4j.LoggerFactory
 import java.io.File
@@ -150,16 +151,16 @@ class Database(dbConfig: String, internal val bankCurrency: String, internal val
             try {
                 return@conn lambda(conn);
             } catch (e: SQLException) {
-                logger.error(e.message)
-                if (e.sqlState != "40001") // serialization_failure
-                    throw e // rethrowing, not to hide other types of errors.
+                if (e.sqlState != PSQLState.SERIALIZATION_FAILURE.state)
+                    throw e
             }
         }
-        throw libeufinError(
-            HttpStatusCode.InternalServerError,
-            "Transaction serialization failure",
-            TalerErrorCode.BANK_SOFT_EXCEPTION
-        )
+        try {
+            return@conn lambda(conn)
+        } catch(e: SQLException) {
+            logger.warn("Serialization failure after $SERIALIZATION_RETRY retry")
+            throw e
+        }
     }
 
     /** Apply paging logic to a sql query */
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
index 82df529c..d80fd665 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
@@ -3,6 +3,7 @@ package tech.libeufin.nexus
 import kotlinx.coroutines.Dispatchers
 import kotlinx.coroutines.withContext
 import org.postgresql.jdbc.PgConnection
+import org.postgresql.util.PSQLState
 import com.zaxxer.hikari.*
 import tech.libeufin.util.*
 import java.sql.PreparedStatement
@@ -137,7 +138,7 @@ private fun PreparedStatement.maybeUpdate(): Boolean {
         this.executeUpdate()
     } catch (e: SQLException) {
         logger.error(e.message)
-        if (e.sqlState == "23505") return false // unique_violation
+        if (e.sqlState == PSQLState.UNIQUE_VIOLATION.state) return false
         throw e // rethrowing, not to hide other types of errors.
     }
     return updateCount > 0
diff --git a/util/src/main/kotlin/DB.kt b/util/src/main/kotlin/DB.kt
index ebc7d7e2..1a7571df 100644
--- a/util/src/main/kotlin/DB.kt
+++ b/util/src/main/kotlin/DB.kt
@@ -25,6 +25,7 @@ import kotlinx.coroutines.coroutineScope
 import net.taler.wallet.crypto.Base32Crockford
 import org.postgresql.ds.PGSimpleDataSource
 import org.postgresql.jdbc.PgConnection
+import org.postgresql.util.PSQLState
 import org.slf4j.Logger
 import org.slf4j.LoggerFactory
 import java.io.File
@@ -171,7 +172,7 @@ fun PreparedStatement.executeUpdateViolation(): Boolean {
         executeUpdateCheck()
     } catch (e: SQLException) {
         logger.error(e.message)
-        if (e.sqlState == "23505") return false // unique_violation
+        if (e.sqlState == PSQLState.UNIQUE_VIOLATION.state) return false
         throw e // rethrowing, not to hide other types of errors.
     }
 }
@@ -184,7 +185,7 @@ fun PreparedStatement.executeProcedureViolation(): Boolean {
         true
     } catch (e: SQLException) {
         connection.rollback(savepoint);
-        if (e.sqlState == "23505") return false // unique_violation
+        if (e.sqlState == PSQLState.UNIQUE_VIOLATION.state) return false
         throw e // rethrowing, not to hide other types of errors.
     }
 }