Deadlock in bb8 Postgres connection pool

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?;
...

Here is how the Pool is created:

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(5)
        .min_idle(Some(1))
        .build(
            PostgresConnectionManager::new_from_stringlike(
                format!(
                    "postgresql://{}:{}@{}:{}/{}",
                    "myuser", "password", "localhost", "5432", "mydb",
                ),
                bb8_postgres::tokio_postgres::NoTls,
            )
            .unwrap(),
        )
        .await
        .unwrap();
    POOL.set(pool);
}

I'm a bit stuck and not sure where to go from here.

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.

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.