Unable to abstract over `Pool` and `Transaction`

[Wopple]

I've been fighting with this for some time now, and I've only been able to manage partial solutions. I have not reached a full solution for what should logically be possible.

I understand there is a fundamental difference between a pool and a transaction. A pool can spawn multiple connections which would be reasonable to schedule as separate async blocks running in parallel. A transaction is a single connection which cannot support parallel execution. However, given the common pattern of async and then immediately await before the next async, it should be able possible to use a transaction the same as a pool in that scenario.

What I've found that works is:

pub async fn foo(conn: &mut PgConnection) -> Result<i32, sqlx::Error> {
    sqlx::query("SELECT 1 AS v")
        .map(|row: PgRow| row.get("v"))
        .fetch_one(conn)
        .await
}

let pool: PgPool;
let pool_conn = &mut *pool.acquire().await?;
let mut tx = pool.begin().await?;

foo(pool_conn).await();
foo(pool_conn).await();

foo(&mut tx).await();
foo(&mut tx).await();

Despite being able to call the foo function multiple times, the limitation here is the &mut PgConnection inside foo can only be used once. When you call fetch_one, it gets derefed and moved internally. There's an idea where you can abstract over Acquire<Connection = Connection<Postgres>> so you can pass in something that can acquire a connection, but pools implement Acquire for PoolConnection which is different from Connection. I have not found a way to smooth over that discrepancy.

[abonander]

Firstly, you can avoid the consumption of the &mut PgConnection if you reborrow:

pub async fn foo(conn: &mut PgConnection) -> Result<i32, sqlx::Error> {
    sqlx::query("<do something else>")
        .execute(&mut *conn)
        .await?;

    sqlx::query("SELECT 1 AS v")
        .map(|row: PgRow| row.get("v"))
        .fetch_one(&mut *conn)
        .await?
}

Secondly, if you use `impl Acquire<Database = Postgres>` (so don't constrict the `Connection` associated type) then you should be able to use either:

```rust
pub async fn foo(conn: impl Acquire<Database = Postgres>) -> Result<i32, sqlx::Error> {
    // no-op for `PgConnection`, `Transaction`
    let mut conn = conn.acquire().await?;

    sqlx::query("SELECT 1 AS v")
        .map(|row: PgRow| row.get("v"))
        .fetch_one(conn)
        .await
}