[SOLVED] Is it necessary to retrieve rows for lock to apply?

Is it necessary to retrieve rows for lock to apply?

Is it necessary to retrieve all the locked for update rows for the lock to apply correctly?

In particular I'm interested in the Rust Sqlx case. I guess fetch is lazy, so it won't even send a request until iteration the over results. But what about fetch_optional is it enough, or is fetch_all a must?

let _ = sqlx::query!(
    r#"SELECT recipe FROM recipe_ingredient WHERE recipe = $1 FOR UPDATE"#,
    recipe_id as _
)
.fetch_optional(&mut *tx)  // Is it enough?
.await?;

Solution

From the PostgreSQL SELECT documentation:

Similarly, if FOR UPDATE ... is used in a cursor's query, only rows actually fetched or stepped past by the cursor will be locked.

So it sounds like yes, you'd need .fetch_all to ensure all rows are iterated; fetch_optional only cares about the first row. Or to save you from loading it all into a buffer, you can use .fetch and iterate over the Stream until completion (an empty .for_each(|_| {}) can be used for that).

If you don't care about the data, you could just do SELECT 1 ... since the answer here says it is sufficient since only the WHERE that causes the row to be SELECT-ed is what matters.