I'm having an issue where getting database connections from my connection pool causes the program to deadlock after a few queries. I can't tell if my connections are just not being released or if something else is going on.
I use a OnceCell to initialize the pool and afterwards I use the following to get connections from that pool:
...
let pgclient = &POOL.get().unwrap().get().await?;
...
I found the source of my problem, but I don't know how to fix it. It's related to the way I spawn a Tokio task with tokio::spawn I believe.
Here is an example of the problem. In this example the db query inside the loop in main will block until get_db_val is finished, will run, then block again as get_db_val runs again.
use bb8_postgres::bb8::Pool;
use bb8_postgres::tokio_postgres::{Client, NoTls};
use bb8_postgres::{bb8, PostgresConnectionManager};
use once_cell::sync::OnceCell;
use std::time::{Duration, Instant};
use std::{time, thread};
use chrono::{DateTime, Datelike, Timelike, Utc};
pub(crate) static POOL: OnceCell<Pool<PostgresConnectionManager<NoTls>>> = OnceCell::new();
pub async fn start_pool() {
let pool: Pool<PostgresConnectionManager<NoTls>> = bb8::Pool::builder()
.max_size(1)
.min_idle(Some(1))
.build(
PostgresConnectionManager::new_from_stringlike(
format!(
"postgresql://{}:{}@{}:{}/{}",
"username", "password", "localhost", "5432", "mydb",
),
bb8_postgres::tokio_postgres::NoTls,
)
.unwrap(),
)
.await
.unwrap();
POOL.set(pool);
}
#[tokio::main]
async fn main() -> Result<(), anyhow::Error> {
start_pool().await;
tokio::spawn(get_db_val());
loop {
let now = Instant::now();
let pgclient = &POOL.get().unwrap().get().await?;
for n in 1..=1000 {
let _ = pgclient
.query("SELECT server_uuid FROM server WHERE is_public = true", &[])
.await?;
}
let elapsed = now.elapsed();
println!("Elapsed: {:.2?}", elapsed);
}
Ok(())
}
async fn get_db_val() -> Result<(), anyhow::Error>{
loop {
let pgclient = &POOL.get().unwrap().get().await?;
for n in 1..=100 {
let _ = pgclient
.query("SELECT server_uuid FROM server WHERE is_public = true", &[])
.await?;
}
thread::sleep(Duration::from_secs(2))
}
}
You have set the max pool size to 1. This means that while either main or get_db_val has acquired a single connection, the other has to wait for it to be released again. You should probably increase the pool size. Using a connection pool when you are using just a single connection is kind of pointless.