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