postgresql - Function taking forever to run for large number of records -

i have created following function in postgres 9.3.5:

create or replace function get_result(val1 text, val2 text) returns text  $body $declare  result text;  begin  select min(id) result table  id_used null , id_type = val2;  update table set  id_used = 'y',  col1 = val1,   id_used_date = now()  id_type = val2  , id = result;  return result;  end;  $body$ language plpgsql volatile cost 100; 

when run function in loop of on 1000 or more records freezing , says "query running". when check table nothing being updated. when run 1 or 2 records runs fine.

example of function when being run:

select get_result('123','idtype'); 

table columns:

id character varying(200), col1 character varying(200), id_used character varying(1), id_used_date timestamp without time zone, id_type character(200) 

id table index.

can help?

most running race conditions. when run function 1000 times in quick succession in separate transactions, happens:

t1            t2            t3            ... select max(id) -- id 1               select max(id)  -- id 1                             select max(id)  -- id 1                                           ...               row id 1 locked, wait ...                             row id 1 locked, wait ... update id 1                                           ...   commit               wake up, update id 1 again!               commit                             wake up, update id 1 again!                             commit                                           ...  

largely rewritten , simplified sql function:

create or replace function get_result(val1 text, val2 text)   returns text  $func$    update table t    set    id_used = 'y'         , col1 = val1         , id_used_date = now()      (       select id         table         id_used null       ,    id_type = val2       order  id       limit  1          update   -- lock avoid race condition! see below ...       ) t1     t.id_type = val2    -- ,    t.id_used null -- repeat condition (not if row locked)    ,    t.id = t1.id    returning  id; $func$  language sql; 

related question lot more explanation:

• atomic update .. select in postgres

explain

• don't run 2 separate sql statements. more expensive , widens time frame race conditions. 1 update subquery better.

• you don't need pl/pgsql simple task. still can use pl/pgsql, update stays same.

• you need lock selected row defend against race conditions. cannot aggregate function head because, per documentation:

the locking clauses cannot used in contexts returned rows cannot identified individual table rows; example cannot used aggregation.

• bold emphasis mine. luckily, can replace min(id) equivalent order by / limit 1 provided above. can use index well.

• if table big, need index on id @ least. assuming id indexed primary key, help. additional partial multicolumn index a lot more:

  create index foo_idx on table (id_type, id) id_used null; 

alternative solutions

advisory locks may superior approach here:

• postgres update ... limit 1

or may want lock many rows @ once:

• how mark nr of rows in table on concurrent access