Re: concurrency in stored procedures

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.

see:
http://merlinmoncure.blogspot.com/2006/12/advisory-locks-part-2.html

merlin