Re: [SQL] Are PL/pgSQL calls atomic?

Jan Wieck <wieck@debis.com> wrote:

|Mark Wright asked:
|
|>
|> If I call a PL/pgSQL function, and another client makes the same call,
does
|> the second client have to wait for the first to complete execution before
it
|> begins?  If not, is there some sort of mechanism I can use to prevent
more
|> than one call to a function from happening at the same time (something
like
|> a mutex in Win32).
|>
|> I need to select a row and then mark it as unavailable for other clients
to
|> use.  The table looks like:
|>     create table xyz (id serial, status char default 'N', ...);
|>
|> My function finds a row by doing:
|>     select id into my_id_variable from xyz where id
|>         = (select min(id) from xyz where status = 'N');
|>
|> and then marks that row as unavailable by setting status:
|>     update xyz set status = 'Y' where id = my_id_variable;
|>
|> Obviously, if a second client calls this function before the UPDATE takes
|> place, it will pick up the same row as the first client, causing rending
of
|> hair and gnashing of teeth.
|>
|> How do I prevent this?

[very helpful discussion deleted]

|
|    What  you  could  do (if the number of rows with status = 'N'
|    isn't high) is the following:
|
|    CREATE FUNCTION my_func .... AS '
|    DECLARE
|        xyz_rec       RECORD;
|    BEGIN
|        FOR xyz_rec IN SELECT * FROM xyz WHERE status = ''N''
|                       ORDER BY id FOR UPDATE OF xyz
|        LOOP
|            -- If more changes in xyz are to be made than just setting
|            -- status to Y, do them all in one UPDATE. The record is
|            -- locked now and the lock will release only when our entire
|            -- transaction commits or rolls back - not when we update it.
|
|            UPDATE xyz SET status = ''Y'' WHERE id = xyz_rec.id;
|            ...
|
|            -- Now we return from inside the loop at the first
|            -- row processed. This ensures we will process one
|            -- row at max per call.
|            RETURN _whatever_my_func_returns_;
|        END LOOP;
|
|        -- If we reach here, we did not find any row (left) with
|        -- status N. Hmmm - is this an error or not?
|
|        RAISE ERROR ''no (more) xyz rows with status N found'';
|    END;' LANGUAGE 'plpgsql';
|
|
|    Why it's important that the number of rows  having  status  =
|    'N'  isn't very big is because at the FOR xyz_rec IN time all
|    of them are fetched into memory, even if the loop will  break
|    at the first one got.


Any off-the-cuff estimates for what 'too many' is?  I would have anywhere
from 1 to 20 sessions executing the function, the number of rows should be
less than 10,000, and I'll be running this on a small to mid-range Pentium
linux box.
---
Mark Wright
mwright@pro-ns.net
mark_wright@datacard.com