After a number of database errors when using Postgres, the connection pool times out in trying to form new connections

[KimEbert42]

After a number of database errors, connections no longer can be formed to the database, and Askar no longer functions. The error message we are seeing is

Caused by: pool timed out while waiting for an open connection

When a database error occurs, it appears that the connection is not returned to the pool properly. This can be verified by examining the open connections to the database are in an idle state, but appears no new connections an be made.

Since the database errors may be timing related, it was difficult to reproduce the issue. I found that by injecting random errors into Askar where we may see database errors triggered the same problem. By injecting the error here, we are simulating an error that the database may encounter. We would expect after handling the error, that the connection would be returned to the connection pool, but it is not.

diff --git a/src/backend/postgres/mod.rs b/src/backend/postgres/mod.rs
index 8a8019f..82e279a 100644
--- a/src/backend/postgres/mod.rs
+++ b/src/backend/postgres/mod.rs
@@ -1,3 +1,6 @@
+use rand::Rng;
+
+
 use std::collections::BTreeMap;
 use std::fmt::{self, Debug, Formatter};
 use std::sync::Arc;
@@ -585,6 +588,12 @@ async fn perform_insert<'q>(
     expiry_ms: Option<i64>,
     new_row: bool,
 ) -> Result<(), Error> {
+
+    let num = rand::thread_rng().gen_range(0..10);
+    if num == 0 {
+        return Err(err_msg!(Unsupported, "asdf!"));
+    }
+
     let row_id = if new_row {
         trace!("Insert entry");
         sqlx::query_scalar(INSERT_QUERY)

After modifying Askar with the above random error, I ran the following queries against aca-py. After about 4000 iterations Askar would no longer function, as Askar could no longer connect to the database.

#!/bin/sh

i=0
while [ $i -ne 5000 ]
do
        i=$(($i+1))

curl -X 'POST' \
  'http://localhost:3001/multitenancy/wallet' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d "{
  \"key_management_mode\": \"managed\",
  \"label\": \"Alice\",
  \"wallet_dispatch_type\": \"default\",
  \"wallet_key\": \"MySecretKey123\",
  \"wallet_name\": \"MyNewWallet$i\",
  \"wallet_type\": \"askar\"

}"

done

[KimEbert42]

It appears this is an issue with aca-py. See openwallet-foundation/acapy#1777