Actix Rust deadpool_postgres: DB connections not released

Question

I've got an Actix-web server that connects to a Postgres DB.

I've noticed that after a 1000 requests my Postgres DB's RAM usage has spiked.

When I stop actix-web, the RAM held by the db is cleared. This leads me to believe that my code is not releasing the connection.

I cannot find an example of connections actually being released. It looks like it's inferred in everyone else's code.

Here's mine:

async fn hellow_world(a : f32, b : f32, pool: &Pool) -> Result<Value, PoolError> {
    let client: Client = pool.get().await?;
    let sql = format!("select \"json\" from public.table_a WHERE a={} and b={}", a, b);
    let stmt = client.prepare(&sql).await?;
    let row = client.query_one(&stmt, &[]).await?;
    let result : Value = row.get(0);
    Ok(result)
}

#[derive(Deserialize)]
pub struct MyRequest {
   a: f32,
   b: f32
}

#[get("/hello")]
async fn sv_hellow_world(info: web::Query<MyRequest>, db_pool: web::Data<Pool>) -> Result<HttpResponse, Error> {
    let response : Value = hellow_world(info.a, info.b, &db_pool).await?;
    Ok(HttpResponse::Ok().json(response))
}

#[actix_rt::main]
async fn main() -> std::io::Result<()> {
    dotenv().ok();
    let config = Config::from_env().unwrap();
    let pool = config.pg.create_pool(tokio_postgres::NoTls).unwrap();

    env_logger::from_env(Env::default().default_filter_or("info")).init();

    let server = HttpServer::new(move || App::new().wrap(Logger::default()).wrap(Logger::new("%a %{User-Agent}i")).data(pool.clone()).service(sv_hellow_world))
        .bind("0.0.0.0:3000")?
        .run();
    server.await
}

It's not clear from the docs whether prepare() creates a server-side prepared statement. If so, then these would be piling up in a shared connection on the server since you are using string formatting instead of bind parameters. If you can put a debug query in, then see what comes back from select * from pg_prepared_statements — Mike Organek
Post running all my HTTP requests and RAM still being allocated even though all requests have been services, I did that select and got 0 rows back — Werner
Did you do it using the same database connection/session? Prepared statements are visible only within the session where they are declared. If you did, then sorry for the bad guess. — Mike Organek
@MikeOrganek, it seems you were right! A count on pg_prepared_statements after each unique a and b value query, I see in increase. How would you suggest I fix this? — Werner

Mike Organek Mike Organek · Accepted Answer · 2020-07-12T12:38:38

Based on further testing, @Werner determined that the code was piling up server-side prepared statements.

It is not clear whether these statements can be closed using this library.

Either of two approaches can be used to avoid this problem:

Use a single, shared prepared statement
Use the direct query form instead of the prepared statement

I recommend the first approach on principle as it is more efficient and protects against SQL Injection. It should look something like this:

async fn hellow_world(a : f32, b : f32, pool: &Pool) -> Result<Value, PoolError> {
    let client: Client = pool.get().await?;
    let stmt = client.prepare("select \"json\" from public.table_a WHERE a=$1::numeric and b=$2::numeric").await?;
    let row = client.query_one(&stmt, &[&a, &b]).await?;
    let result : Value = row.get(0);
    Ok(result)
}

Using this code, only one prepared statement should be created on each of the pool's connections.

Actix Rust deadpool_postgres: DB connections not released

When I stop actix-web, the RAM held by the db is cleared. This leads me to believe that my code is not releasing the connection.

1 Answers