pg: Replacing Connection::Transaction() with TransactionBuilder::run()? Modify Transaction Isolation Level to REPEATABLE READ

[kairoswater-jason]

Backend

PostgreSQL

Diesel version

2.3.0

Diesel Features

postgres, uuid, chrono, serde

Operating System Version

No response

Third party libraries

No response

What do you want to do?

I have a handler function called retry_transaction that I want to use the REPEATABLE READ level of transaction isolation on in postgreSQL. I also use pooled connections.

I currently use this:

    //connection() returns a r2d2 PooledConnection
    let ret = context.connection().transaction::<T, E, _>(|connection| {
        //normal: connection is of type PooledConnection
        let mut context = MyContext::new(extra_stuff, connection);
        f(&mut context)
    });

I tried this:

    //connection() returns a r2d2 PooledConnection
    context.connection().build_transaction()
        .repeatable_read().run::<T, E, _>(|connection| {
        //issue: connection changed from PooledConnection to PgConnection
        let mut context = MyContext::new(extra_stuff, connection);
        f(&mut context)
    });

I encountered an error where the type of connection that is passed inside of TransactionBuilder::run() has been changed from the pooled connection type into a regular connection. Unfortunately, all of my internal APIs require the pooled type (maybe they shouldn't? I didn't think they were interchangeable or equivalent).

Corollary questions:

• is it possible to convert a PooledConnection to a PgConnection outside of a transaction?
• is it possible to ask a PooledConnection's transaction to operate at the REPEATABLE READ isolation level without having my PooledConnection turned into PgConnection?

[1] https://docs.rs/diesel/latest/diesel/r2d2/struct.PooledConnection.html
[2] https://docs.diesel.rs/2.0.x/diesel/pg/struct.TransactionBuilder.html

Compile time error

mismatched types
expected mutable reference `&mut PooledConnection<ConnectionManager<diesel::PgConnection>>`
   found mutable reference `&mut diesel::PgConnection`

Additional details

I could manually emit a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; but I feel like that isn't the right solution when I'm already (so close to) interacting with a transaction manager that could do it if the api call was exposed. (Or if it is exposed, if I knew how to call it without changing my connection type)

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[weiznich]

This is expected behaviour. The corresponding transaction methods just expose the underlying connection types.

[kairoswater-jason]

For those reading along the answer appears to be that R2D2's pooled connection type is a smart pointer. And if you find yourself in my situation (where you have a database abstraction layer that expects a certain specific/non-generic type of connection) you can change a &mut PooledConnection<ConnectionManager<PgConnection>> into a &mut PgConnection via the deref trait ie. my_pooled_connection.deref_mut()

Thankfully in my internal library all I need to do is change

pub type DbConnection = PooledConnection<ConnectionManager<PgConnection>>;

into

pub type DbConnection = PgConnection;

and call .deref_mut() in a few places. And then I'll be able to use the TransactionBuilder API without running into an incompatible connection type.

[kairoswater-jason]

~~Actually, I might be wrong.

it looks like I probably still do have to rip R2D2 out of the project (or at the very least do something different about how I pass around connection references) due the TransactionBuilder API combined with having to cross an async boundary~~

edit: With some finagling, if you are careful, you can pass around references to a &mut PgConnection in a semi-async environment rather than directly moving a PooledConnection smart pointer into the async context