Re: Functions and transactions

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)
>
>