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
