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

Jan Wieck <wieck(at)debis(dot)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(at)pro-ns(dot)net
mark_wright(at)datacard(dot)com