Re: Does the block of code in a stored procedure execute - Mailing list pgsql-general

From
Subject Re: Does the block of code in a stored procedure execute
Date
Msg-id 65394.66.212.203.144.1059535932.squirrel@$HOSTNAME
Whole thread Raw
In response to Re: Does the block of code in a stored procedure execute  (Dima Tkach <dmitry@openratings.com>)
List pgsql-general
> btober@seaworthysys.com wrote:
>
>>Thank you very much.
>>
>>Further clarification on two points, though, please.
>>
>>1) When I add the FOR UPDATE clause to the SELECT statement, do also
>> have to add a COMMIT statement somewhere?
>>
> *no* Don't even think about it.:-)
> You are running this from inside a trigger, right?

So, to re-iterate then, will simply adding the FOR UPDATE clause to my
original select statement make the procedure as originally written with
the SELECT first multi-user safe?

>
>>2) I don't see how doing UPDATE first helps. What if the other user,
>> calling the same function, happens to have their UPDATE statement
>> execute between my UPDATE and SELECT statements? Then we again both
>> get the same new "sequence" value, don't we?
>>
> When you UPDATE a row, it gets locked (just like when you do
> select...for update), and stays locked until the end of the
> transaction.  So, once you UPDATE it, nobody else can until your
> transaction is finished.
>
> I hope, it helps...

Now I see. That helps a lot. I was not aware of that behavior. Thank you.

~Berend Tober




pgsql-general by date:

Previous
From: Dima Tkach
Date:
Subject: Re: Does the block of code in a stored procedure execute
Next
From: "Castle, Lindsay"
Date:
Subject: SQL SUM query limited by dates