I'm just curious what's the recommended way to use r2d2-postgres with SslMode::Require, or Preferred? I've been racking my head around this for awhile, and I assume it's something dumb that I'm just not thinking of.

I can successfully get a connection over TLS with the normal postgres crate w/ OpenSSL Contexts, but I'm not sure how to get it to work within this crate. Any insight would be very helpful. If you need to see code I've tried I'd be happy to drop it in.

Hello,

I'm trying to create a little abstraction for the connection pool as follows:

extern crate r2d2;
extern crate r2d2_postgres;
extern crate postgres;

use std::error::Error;
use std::io;

use self::r2d2_postgres::postgres::types::ToSql;
use self::r2d2_postgres::postgres::rows::Rows;
use self::r2d2_postgres::postgres::error;
use self::r2d2_postgres::{PostgresConnectionManager, TlsMode};
use self::r2d2::{Pool, InitializationError};

pub struct DB {
    // I need to initialize it as static somehow ?
    pool: Box<Pool<PostgresConnectionManager>>,
}

impl DB {
    pub fn new(conn_string: &str) -> Result<DB, InitializationError> {
        let config = r2d2::Config::default();
        let manager = PostgresConnectionManager::new(conn_string, TlsMode::None)
            .expect("Failed to establish DB connection");
        match r2d2::Pool::new(config, manager) {
            Ok(conn) => Ok(DB { pool: Box::new(conn) }),
            Err(err) => Err(err),
        }
    }

    pub fn query(&self, query: &str, params: &[&ToSql]) -> Result<Rows, error::Error> {

        match self.pool.get() {
            Ok(ref connection) => connection.query(query, params),
            Err(err) => {
                Err(error::Error::Io(io::Error::new(io::ErrorKind::NotConnected,
                                                    err.description())))
            }
        }
    }

    pub fn execute(&self, query: &str, params: &[&ToSql]) -> Result<u64, error::Error> {
        match self.pool.get() {
            Ok(ref connection) => connection.execute(query, params),
            Err(err) => {
                Err(error::Error::Io(io::Error::new(io::ErrorKind::Other, err.description())))
            }
        }
    }
}

This however makes the compiler unhappy and it complains about the connection variable in the match statement not live long enough.

pub fn query(&self, query: &str, params: &[&ToSql]) -> Result<Rows, error::Error> {

        match self.pool.get() {
            Ok(ref connection) => connection.query(query, params),
            Err(err) => {
                Err(error::Error::Io(io::Error::new(io::ErrorKind::NotConnected,
                                                    err.description())))
            }
        }
    }

Connection is only alive in the scope of the match statement but compiler expects its lifetime to be entire query function. Could you advise on how this can be achieved? Can the DB struct be declared with a 'static lifetime? A very similar abstraction worked on the pure rust-postgres driver.

Thanks!

I'm getting following errors trying to compile the example. My Cargo.toml contents:

[dependencies] postgres = "" r2d2_postgres = ""

userxxx@machine01:~/data/work/rust/db/postgres/pgtest$ cargo build Compiling pgtest v0.1.0 (/home/userxxx/data/work/rust/db/postgres/pgtest) error[E0432]: unresolved imports postgres::NoTls, postgres::Client --> src/main.rs:3:16 | 3 | use postgres::{NoTls, Client}; | ^^^^^ ^^^^^^ no Client in the root | | | no NoTls in the root

error[E0433]: failed to resolve: use of undeclared type or module r2d2 --> src/main.rs:12:16 | 12 | let pool = r2d2::Pool::new(manager).unwrap(); | ^^^^ use of undeclared type or module r2d2

warning: unused import: Client --> src/main.rs:3:23 | 3 | use postgres::{NoTls, Client}; | ^^^^^^ | = note: #[warn(unused_imports)] on by default

error: aborting due to 2 previous errors

Some errors have detailed explanations: E0432, E0433. For more information about an error, try rustc --explain E0432. error: Could not compile pgtest.

To learn more, run the command again with --verbose.

As we just discussed in #23. Rather than a big warning in the README about specific versions of the Postgres library which you'd have to remember to manually update, I just made the examples use the reexported library as you suggested.

I removed the use postgres::Client since that wasn't actually getting used in the example and was generating a warning.

Hello, I have a legacy database that I'm looking to use r2d2-postgres w/ (Teiid) that implements a PostgreSQL compatible API. Unfortunately it doesn't support passing in blank queries as described by the PostgreSQL spec.

Debugging thread here: https://github.com/sfu-db/connector-x/issues/107

Would you be open to switching the probe query for is_valid to something more standard, like SELECT 1;, or at least make this configurable? This would allow me (and others) to retain connection pooling functionality in that case.

I noticed SELECT 1; is actually what is recommended upstream in r2d2 for connection pool implementors, and understand the empty query is probably slightly faster round trip.

Thanks!

I have been working with Diesel a bit using it's PgConnection directly, and I'm trying to actually get a connection pool hooked up via R2D2, but I've run into an error that I can't seem to get around:

the trait `r2d2_postgres::postgres::tls::MakeTlsConnect<r2d2_postgres::postgres::Socket>` is not implemented for `postgres::NoTls`

When trying to instantiate an r2d2_postgres::PostgresConnectionManager like:

PostgresConnectionManager::new(database_url.parse().unwrap(), postgres::tls::NoTls);

I finally just made a new Rust project to run the example code in the README that I was using as my guide, but it seems to be failing with the same error. My standalone project's dependencies block looks like:

[dependencies]          
r2d2 = "0.8.9"          
r2d2_postgres = "0.16.0"
postgres = "0.18.1" 

and my src/main.rs looks like this (ripped straight from this project's README):

use postgres::{Client, NoTls};                                                                 
use r2d2_postgres::PostgresConnectionManager;                                                  
use std::thread;                                                                               
                                                                                               
fn main() {                                                                                    
    let manager =                                                                              
        PostgresConnectionManager::new("host=localhost user=postgres".parse().unwrap(), NoTls);
    let pool = r2d2::Pool::new(manager).unwrap();                                              
                                                                                               
    for i in 0..10i32 {                                                                        
        let pool = pool.clone();                                                               
        thread::spawn(move || {                                                                
            let mut client = pool.get().unwrap();                                              
            client                                                                             
                .execute("INSERT INTO foo (bar) VALUES ($1)", &[&i])                           
                .unwrap();                                                                     
        });                                                                                    
    }                                                                                          
}                                                                                              

Running cargo build results in the following rather lengthy set of errors. You can see the same trait implementation error in this near the top:

   Compiling test-postgres v0.1.0 (/home/dpetersen/dev/test-postgres)
warning: unused import: `Client`
 --> src/main.rs:1:16
  |
1 | use postgres::{Client, NoTls};
  |                ^^^^^^
  |
  = note: `#[warn(unused_imports)]` on by default

error[E0277]: the trait bound `postgres::NoTls: r2d2_postgres::postgres::tls::MakeTlsConnect<r2d2_postgres::postgres::Socket>` is not satisfied
 --> src/main.rs:7:89
  |
7 |         PostgresConnectionManager::new("host=localhost user=postgres".parse().unwrap(), NoTls);
  |                                                                                         ^^^^^ the trait `r2d2_postgres::postgres::tls::MakeTlsConnect<r2d2_postgres::postgres::Socket>` is not implemented for `postgres::NoTls`
  |
  = note: required by `r2d2_postgres::PostgresConnectionManager::<T>::new`

error[E0277]: the trait bound `postgres::NoTls: r2d2_postgres::postgres::tls::MakeTlsConnect<r2d2_postgres::postgres::Socket>` is not satisfied
 --> src/main.rs:8:32
  |
8 |     let pool = r2d2::Pool::new(manager).unwrap();
  |                                ^^^^^^^ the trait `r2d2_postgres::postgres::tls::MakeTlsConnect<r2d2_postgres::postgres::Socket>` is not implemented for `postgres::NoTls`
  |
  = note: required because of the requirements on the impl of `r2d2::ManageConnection` for `r2d2_postgres::PostgresConnectionManager<postgres::NoTls>`
  = note: required by `r2d2::Pool::<M>::new`

error[E0277]: the trait bound `postgres::NoTls: r2d2_postgres::postgres::tls::MakeTlsConnect<r2d2_postgres::postgres::Socket>` is not satisfied
   --> src/main.rs:8:16
    |
8   |     let pool = r2d2::Pool::new(manager).unwrap();
    |                ^^^^^^^^^^^^^^^^^^^^^^^^ the trait `r2d2_postgres::postgres::tls::MakeTlsConnect<r2d2_postgres::postgres::Socket>` is not implemented for `postgres::NoTls`
    | 
   ::: /home/dpetersen/.cargo/registry/src/github.com-1ecc6299db9ec823/r2d2-0.8.9/src/lib.rs:319:8
    |
319 |     M: ManageConnection;
    |        ---------------- required by this bound in `r2d2::Pool`
    |
    = note: required because of the requirements on the impl of `r2d2::ManageConnection` for `r2d2_postgres::PostgresConnectionManager<postgres::NoTls>`

error[E0599]: no method named `clone` found for struct `r2d2::Pool<r2d2_postgres::PostgresConnectionManager<postgres::NoTls>>` in the current scope
   --> src/main.rs:11:25
    |
11  |           let pool = pool.clone();
    |                           ^^^^^ method not found in `r2d2::Pool<r2d2_postgres::PostgresConnectionManager<postgres::NoTls>>`
    | 
   ::: /home/dpetersen/.cargo/registry/src/github.com-1ecc6299db9ec823/r2d2_postgres-0.16.0/src/lib.rs:37:1
    |
37  |   pub struct PostgresConnectionManager<T> {
    |   --------------------------------------- doesn't satisfy `_: r2d2::ManageConnection`
    | 
   ::: /home/dpetersen/.rustup/toolchains/stable-x86_64-unknown-linux-gnu/lib/rustlib/src/rust/library/core/src/clone.rs:122:8
    |
122 |       fn clone(&self) -> Self;
    |          -----
    |          |
    |          the method is available for `std::sync::Arc<r2d2::Pool<r2d2_postgres::PostgresConnectionManager<postgres::NoTls>>>` here
    |          the method is available for `std::rc::Rc<r2d2::Pool<r2d2_postgres::PostgresConnectionManager<postgres::NoTls>>>` here
    | 
   ::: /home/dpetersen/.cargo/registry/src/github.com-1ecc6299db9ec823/r2d2-0.8.9/src/lib.rs:317:1
    |
317 | / pub struct Pool<M>(Arc<SharedPool<M>>)
318 | | where
319 | |     M: ManageConnection;
    | |________________________- doesn't satisfy `_: std::clone::Clone`
    |
    = note: the method `clone` exists but the following trait bounds were not satisfied:
            `r2d2_postgres::PostgresConnectionManager<postgres::NoTls>: r2d2::ManageConnection`
            which is required by `r2d2::Pool<r2d2_postgres::PostgresConnectionManager<postgres::NoTls>>: std::clone::Clone`

error: aborting due to 4 previous errors; 1 warning emitted

Some errors have detailed explanations: E0277, E0599.
For more information about an error, try `rustc --explain E0277`.
error: could not compile `test-postgres`.

To learn more, run the command again with --verbose.

I am running everything locally at the moment and so I can't use TLS, as far as I know. I have checked at all the available changelogs I can find, I've ensured I'm using the newest version of all the relevant crates. I'm still fairly bad at Rust and so I don't really understand how I can get around this issue, but I've spent a couple hours unsuccessfully trying to get this working. I've done a fair amount of Googling and haven't found anyone with a similar issue, so I have a bad feeling I'm screwing something up. I've tried every example I can find on the Internet of somebody trying to set up a connection pool, with no luck.

I really appreciate all the work you've done building such awesome Postgres support in Rust, and I'm sorry to bother you with something this simple. Hopefully, you can point me in the right direction. Thanks!

i have searched for the complete r2d2_postgres connection format, but got nothing, how i declare the connection to the database,because the example only define "host=localhost user=postgres", i really appreciate for any help, thanks

Hello, I am wondering if it's possible to cache statements while using this connection pool?

As I understand it, caching support was removed from the underlying postgres library as Statements are now 'static and so can be cached externally. However, Statements must be used with the same Connection that created them, and so if I build an external Statement Cache, then I have to have a Cache for each Connection in the Pool. Which would be fine, but when I get a Connection from the Pool I don't know which Connection I've got and so I can't retrieve its Statement from the Cache. Am I missing something, or is caching not really possible when using a connection pool?

The postgres crate supports transactions via the Transaction struct which has a similar interface to Client.

Is there any way to get Transaction objects instead of Clients out of the pool?

Hi~ I was failed to get connection from pool in an async function scope. The stack is like below:

thread 'tokio-runtime-worker' panicked at 'Cannot start a runtime from within a runtime. This happens because a function (like `block_on`) attempted to block the current thread while the thread i
s being used to drive asynchronous tasks.', /Users/jeffsky/.cargo/registry/src/github.com-1ecc6299db9ec823/tokio-0.2.21/src/runtime/enter.rs:38:5  

My code snippet like this:

let pool = self.pool.clone();
async move {
            let mut client = pool.get().expect("Get client from pool failed!");
            let row = client
                .query_one("SELECT 'world' AS hello", &[])
                .expect("Execute SQL failed!");
            let result: String = row.get("hello");
            println!("result: {}", result);
        }

Need help, thanks!

Are there any plans to support the latest version of Postgres? In its current state, I am unsuccessfully able to use the Postgres UUID (v0.8) serialization support on alpha.1.

I am not sure if this is the correct place for this question, if not please let me know, and I close it, because clearly this is also my personal learning path. However, I can imagine this is something that others will or might run into.

I want to create a (r2d2, postgres) connection pool that can be dynamically NoTls or Tls (MakeTlsConnector). Currently, I swap function and function arguments, but I would like to make it generic.

So far I have created the following contruction:

pub fn create_pool<T>(url: &str, pool_size: i32, cacert_file: &str) -> T {
   if url.contains("sslmode=require") {
       println!(">> creating ssl pool");

       let mut builder = SslConnector::builder(SslMethod::tls()).expect("unable to create sslconnector builder");
       builder.set_ca_file(cacert_file).expect("unable to load ca.cert");
       builder.set_verify(SslVerifyMode::NONE);
       let connector = MakeTlsConnector::new(builder.build());
       let manager = PostgresConnectionManager::new( url.parse().unwrap(), connector);

       r2d2::Pool::builder()
           .max_size(pool_size as u32)
           .connection_timeout(Duration::from_secs(120))
           .build(manager)
           .unwrap()
   } else {
       println!(">> creating nossl pool");
       let manager = PostgresConnectionManager::new( url.parse().unwrap(), NoTls);

       r2d2::Pool::builder()
           .max_size(pool_size as u32)
           .connection_timeout(Duration::from_secs(120))
           .build(manager)
           .unwrap()
   }
}

However, when I create the pool, the compiler tells me it can't derive the type:

const FULL_PATH_CA_CERT: &str = "/tmp/ca.cert";
const PG_URL: &str = "host=192.168.66.80 port=5434 sslmode=disable user=yugabyte password=yugabyte";


let connection_pool = create_pool(PG_URL, threads, FULL_PATH_CA_CERT);

let connection = connection_pool.get().unwrap();

Error:

error[E0282]: type annotations needed
  --> src/lib.rs:66:22
   |
64 |     let connection_pool = create_pool(PG_URL, threads, FULL_PATH_CA_CERT);
   |         --------------- consider giving `connection_pool` a type
65 | 
66 |     let connection = connection_pool.get().unwrap();
   |                      ^^^^^^^^^^^^^^^ cannot infer type
   |
   = note: type must be known at this point

I do understand what the compiler tells me, but how I create a connection pool that can be dynamically NoTls or Tls?

Our production service will occasionally (maybe a handful of times a week) receive the following error:

postgres connection error: connection closed

What is the best way to check the connection via the pool? Is there a better way to handle these errors then just doing a check and trying the get another connection from the pool again?

Hi,

while upgrading a project from rust_postgres 0.15 to 0.17 and r2d2_postgres 0.14 to 0.16, we have some troubles with the new definition of PostgresConnectionManager<T> that contains a new generic parameter <T>. The problem is that the r2d2 pool returns a PooledConnection that now has the very same generic parameter <T> that, as a consequence, has to be redeclared in every place where the connection is used.

For example, our project structure follows the Domain Driven Design principle, then:

• we have a set of Repositories that use a PooledConnection, so they all have to declare the <T> generic param
• we have a set of Services that use the Repositories, so all Services need to declare <T> too
• we have a set of Controllers that use the Services, so all Controllers need to declare <T>
• and so on...

In the end, this <T> generic is declared literally everywhere.

In addition, the rust_postgres Client has no generic parameters at all so it is not clear why the PooledConnection requires it.

I don't know if it is feasible, but the issue could be fixed by changing the signature in the r2d2 pool from:

    pub fn get(&self) -> Result<PooledConnection<M>, Error>;

to

    pub fn get(&self) -> Result<PooledConnection<M::Connection>, Error>;

Or, maybe, the <T> param can be boxed in the PostgresConnectionManager struct:

#[derive(Debug)]
pub struct PostgresConnectionManager {
    config: Config,
    tls_connector: Box<MakeTlsConnect<Socket, TlsConnect=Send, Stream=Send, ... >>,
}