Issue when trying to reconnect to a restarted Docker postgres DB

[berendsliedrecht]

It is an issue inside of sqlx, but I will also open it here to keep track of it.

issue: https://github.com/launchbadge/sqlx/issues/2763

Short version of it is that when we use a Postgres database within a Docker container, we cannot reuse the session when the container restarts. This all works when it is a local database however.

Small snippet with askar to reproduce:

cargo.toml

[package]
name = "askar-reconnect"
version = "0.1.0"
edition = "2021"

[dependencies]
aries-askar = { version = "0.2.9"}
env_logger = "0.10.0"
tokio = { version = "1.32.0", features = ["full"] }
uuid = { version = "1.4.1", features = ["v4", "fast-rng"] }

src/main.rs

use aries_askar::{
    kms::{KeyAlg, LocalKey},
    postgres::PostgresStoreOptions,
    PassKey,
};

use tokio::{process::Command};
use uuid::Uuid;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    Command::new("/usr/local/bin/docker-compose").args(["up","-d"]).output().await.unwrap();
    env_logger::init();

    let pass_key = PassKey::from("7Z8ftDAzMvoyXnGEJye8DurzgQQXLAbYCaeeesM7UKHa");
    let id = Uuid::new_v4();

    let db = PostgresStoreOptions::new("postgres://postgres:postgres@localhost/my-new-db")?
        .provision(aries_askar::StoreKeyMethod::RawKey, pass_key.clone(), None, true)
        .await?;

    let profile_name = db.create_profile(Some(id.to_string())).await?;
    println!("new profile: {profile_name}");

    let mut sesh = db.session(Some(profile_name.clone())).await?;

    let key = LocalKey::from_seed(KeyAlg::Ed25519, &[0;32], None)?;
    sesh.insert_key("MYKEY", &key, None, None, None).await?;

    Command::new("/usr/local/bin/docker-compose").arg("restart").output().await.unwrap();

    // Enabling this line of code makes everything work again.
    // let mut sesh = db.session(Some(profile_name)).await?;

    let fetched_key = sesh.fetch_key("MYKEY", false).await?.unwrap();

    println!("Fetched key: {}", fetched_key.name());

    Ok(())
}

[TimoGlastra]

we cannot reuse the session when the container restarts

I think this is expected right, but that we would expect Askar / SQLx to automatically handle closed sessions / connections rather than us having to manually recreate all the connections to the database?

[berendsliedrecht]

I think this is expected right, but that we would expect Askar / SQLx to automatically handle closed sessions / connections rather than us having to manually recreate all the connections to the database?

Yeah it is expected, but the weird part is is that it works with a local database when that is restarted. There is a slight issue there because the docker container is restarted with docker-compose restart and the local db with brews services stop postgresql@15 && brew services start postgresql@15 which might have different shutdown procedures.

[TimoGlastra]

I think this is expected right, but that we would expect Askar / SQLx to automatically handle closed sessions / connections rather than us having to manually recreate all the connections to the database?

Yeah it is expected, but the weird part is is that it works with a local database when that is restarted. There is a slight issue there because the docker container is restarted with docker-compose restart and the local db with brews services stop postgresql@15 && brew services start postgresql@15 which might have different shutdown procedures.

Could it have something to do with this issue? Not sure, but it also has some different behaviour depending on local vs remote: https://github.com/launchbadge/sqlx/issues/2698

[andrewwhitehead]

Each session wraps a PoolConnection, which does not (to my knowledge) support reconnection. Sessions are expected to be held for a short time before releasing the connection back to the pool, as otherwise you would likely hit the maximum number of open connections. It's strange to me that it would work with a local DB, as it would still need to reestablish the (TLS) connection and recreate any prepared statements. I'm not sure I totally understand where you're seeing this problem, though.

[TimoGlastra]

The problem we're experiencing is with neon.tech which will drop connections / restart sometimes.

We are closing sessions immediately for tenants, but the root session for the main wallet is kept open indefinitely. It's on our roadmap to work better with sessions in AFJ, but currently it's not so easy to change this behaviour.

So would you suggestion be to fix this in AFJ by improving the session handling logic?

[andrewwhitehead]

This may be resolved by the update to sqlx 0.7.3