Re: [GENERAL] easy task: concurrent select-updates

[Andy Colson]

Kevin McConnell wrote:

CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql
AS $function$
declare
      rec record;
begin
      for rec in select id from msg where busy = false order by id loop
              update msg set busy = true where id = rec.id and busy = false;
              if found then
                      return rec.id;
              end if;
      end loop;
      return -1;
end;
$function$

I think you could also do something roughly similar in a statement by
using a RETURNING clause on the update, such as:

  update msg set busy = true where id = (select min(id) from msg where
busy = false) returning *;

Cheers,
Kevin

I had thought of that, but you'd need to add one thing, in the update ' and 
busy = false ', cuz two people may get the same id from the select min(id).

update msg set busy = true where busy = false and id = (select min(id) from msg 
where busy = false) returning *;

but then you'd have to fire it over-and-over until you actually got a row 
updated.

Seemed easer to put the loop in function, then you can:

select id from getmsg();

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general