Re: concurrency in stored procedures

Merlin Moncure wrote:
> On 3/23/07, Ottavio Campana <ottavio(at)campana(dot)vi(dot)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.