Interesting. That makes sense, though. So, is there a good way to lock
a set of rows using SELECT FOR UPDATE in plpgsql? I assume using
PERFORM would yield the same problem, because it immediately discards
the results.
Thanks!
Kris
Tom Lane wrote:
>Kris Kiger <kris@musicrebellion.com> writes:
>
>
>>In your second paragraph, I think that you are saying that SELECT FOR
>>UPDATE only locks one row, even though the select itself may return
>>many. Am I mis-interpreting you?
>>
>>
>
>No, I'm saying that plpgsql's SELECT INTO operation only reads one row.
>The fact that the SELECT might have found more rows if allowed to run
>to completion doesn't enter into it. If the first row read doesn't have
>active = true then it won't conflict against concurrent UPDATEs, because
>you are carefully not UPDATEing rows with active = false. It's the
>combination of those two things that creates the hazard.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
