Streaming and updating in same transaction

[jasonish]

I'm migrating a Rusqlite workflow when I'm doing some data transformation on a very large table. In a transaction, I use a prepared statement, loop over the results in a stream fashion and do an update for each row.

With SQLx and using a futures stream this does not seem possible. I suppose one option is to bulk get a set of records, and do my updates. Essentially paginating the job, but is there no one due to updates on the same tx streaming the results?

Thanks.

[megamichelle]

Hi, did you ever figure this out? I'm stymied by the same problem.

I have a transaction I created with

let transaction = pool.begin().await.unwrap();

I want to do something like;

    let mut rows = sqlx::query_as::<_, (String,)>("some select statement")
        .fetch(&mut *transaction)
    ;

    while let Ok(Some((id,))) = rows.try_next().await {
        let (txt,): (String,) = sqlx::query_as("a second query based on the contents of this row that I fetched from the first query").fetch_one(&mut *transaction).await.unwrap();

        println!("Got {txt}");
    }

But I can't do this because I cannot borrow *transaction as mutable more than once at a time. It's still borrowed when I want to make my second query.

Has anyone ever concocted a way around this other than the one proposed above? I could bulk get a bunch of records with like a LIMIT query and a fetch_all. If I did that, the transaction would not still be borrowed when I wanted to make my second transaction. But there should be a way to stream through the cursor from the first query, while making new queries, so I don't have to hold all the results in memory.