Re: concurrency in stored procedures

Merlin Moncure wrote:
> On 3/23/07, Ottavio Campana <ottavio@campana.vi.it> wrote:
>> Ottavio Campana wrote:
>> > What would you to in order to be sure that one function or a part of it
>> > is atomically executed?
>>
>> would it be correct something like? or how would you write this?
>>
>> create or replace function my_function () returs integer as
>> $$
>> declare
>>   ...
>>   status boolean;
>>   ...
>> begin
>>   ...
>>   loop
>>     begin
>>       set transaction isolation level serializable;
>>       ...
>>       do_something();
>>       ...
>>       status := true;
>>     exception serialization_failure
>>       status := false;
>>     end;
>>
>>     if status then exit;
>>   end loop;
>>   ...
>>   return 0;
>> end
>> $$ language plpgsql
>
> you can also use advisory locks if you want to implement 'critical
> section' inside a plpgsql function.

am I wrong or are advisory locks available only in 8.2?

Anyway, apart from a couple of errors in the code i wrote (an in isn't
closed and exception handling is not correctly written), can I be sure
that the code in the sub-block works an a snapshot of the db?

Thank you

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.