Sqlx: Cannot run two fetches within a transaction

[sam701]

Hi,

I want to run multiple fetches within a single transaction.

pub async fn run1(mut tx: &mut sqlx::Transaction<PoolConnection<PgConnection>>) -> sqlx::Result<u64> {
    sqlx::query("abc")
        .fetch(&mut tx);

    sqlx::query("abc")
        .fetch(&mut tx);

    Ok(3)
}

But I get an error

error[E0277]: the trait bound `&mut sqlx_core::transaction::Transaction<sqlx_core::pool::connection::PoolConnection<sqlx_core::postgres::connection::PgConnection>>: std::marker::Copy` is not satisfied
  --> src/store/abc.rs:45:16
   |
45 |         .fetch(&mut tx);
   |                ^^^^^^^ the trait `std::marker::Copy` is not implemented for `&mut sqlx_core::transaction::Transaction<sqlx_core::pool::connection::PoolConnection<sqlx_core::postgres::connection::PgConnection>>`
   |
   = note: `std::marker::Copy` is implemented for `&sqlx_core::transaction::Transaction<sqlx_core::pool::connection::PoolConnection<sqlx_core::postgres::connection::PgConnection>>`, but not for `&mut sqlx_core::transaction::Transaction<sqlx_core::pool::connection::PoolConnection<sqlx_core::postgres::connection::PgConnection>>`
   = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut &mut sqlx_core::transaction::Transaction<sqlx_core::pool::connection::PoolConnection<sqlx_core::postgres::connection::PgConnection>>`

Is it currently possible to have multiple fetches within a single transaction?

[mehcode]

pub async fn run1(mut tx: &mut sqlx::Transaction<PoolConnection<PgConnection>>) -> sqlx::Result<u64> {
    sqlx::query("abc")
        .fetch(&mut *tx);

    sqlx::query("abc")
        .fetch(&mut *tx);

    Ok(3)
}

---

The key is you need to manually re-borrow the transaction. &mut *tx will take a &mut Transaction and produce a &mut Transaction that is held for the lifetime required by the function.

This is _normally_ done implicitly by Rust. It's not in this case due to fetch() being generic over its parameter (allowing both connections and a pool to passed in).

---

A tip, you can define your function like this:

pub async fn run1(conn: &mut PgConnection)

and call as such (assuming that tx is a Transaction):

run1(&mut tx)

This works because Transaction implements Deref<Target = DB::Connection>.

[sam701]

Great, thanks a lot.

[sam701]

@mehcode Is it possible to define a function that can take either Transaction or PgPool, so that I can decide later if I want to call it within a transaction or without?

[shssoichiro]

I'm encountering this same issue but it also applies to &mut PgConnection. i.e. if I change my function to take &mut PgConnection, I still get use of moved value, move occurs because variable has type &mut PgConnection, which does not implement the Copy trait. (I'm using fetch_one and fetch_all rather than fetch, but I'm assuming the root cause is the same.)

Ideally I'd like to make my function generic so it can take any RefExecutor, but if I want to make two fetches within the same function, I have to add the + Copy bound, which ends up making it only able to take &PgPool.

[mehcode]

If your function has a parameter of say conn: &mut PgConnection , you must manually re-borrow when using it like .fetch_one(&mut *conn).

[mehcode]

As for generalizing over Pool _or_ Connection, that's difficult without GATs in Rust because we're talking about & _ or &mut _.

For now, I would recommend acquiring connections manually and then passing those around (so don't pass the pool around to functions that may want a transaction). It's trivial to generalize between Transaction and Connection because the former can deference into the latter.