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.