1

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
}
Werner Raath
  • 1,322
  • 3
  • 16
  • 34
  • 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 Jul 10 '20 at 11:41
  • 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 Raath Jul 10 '20 at 13:08
  • 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 Jul 10 '20 at 13:41
  • @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 Raath Jul 12 '20 at 11:28

1 Answers1

1

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:

  1. Use a single, shared prepared statement
  2. 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.

Mike Organek
  • 11,647
  • 3
  • 11
  • 26
  • I've been trying that for a bit now. I'm getting an error stating `ERROR: could not determine data type of parameter $1` Note: You code needs to de-reference `a` and `b`. – Werner Raath Jul 12 '20 at 12:50
  • @Werner Can you please try it with casts as in my updated answer? Also, is the dereferencing correct? If not, you can edit my answer, and I will approve. – Mike Organek Jul 12 '20 at 13:01
  • thanks so much for all the help! Getting a new error, my type I have to cast to is `int`, and thus `Internal Server Error: PoolError(Backend(Error { kind: ToSql(1), cause: Some(WrongType { postgres: Int4, rust: "i16" }) }))` – Werner Raath Jul 12 '20 at 13:14
  • @Werner I assumed that `f32` was a float type. Can you try casting to either `real` or `int2` in the query? If you get to the point that you want to punt, then you can use the `client.query()` technique with your formatted string: https://rust-lang-nursery.github.io/rust-cookbook/database/postgres.html. – Mike Organek Jul 12 '20 at 13:20