Geek Answers Handbook

A function that permanently performs a large number of records

I created the following function in Postgres 9.3.5:

CREATE OR REPLACE FUNCTION get_result(val1 text, val2 text)
RETURNS text AS 
$BODY
$Declare

result text;

BEGIN

select min(id) into result from table 
where id_used is null and id_type = val2;

update table set 
id_used = 'Y', 
col1 = val1,  
id_used_date = now() 
where id_type = val2 
and id = result;

RETURN result;

END;

$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

When I run this function in a loop of more than 1000 or more entries, it just freezes and just says “query started”. When I check my table nothing is updated. When I run it for one or two entries, it works fine.

Example function at startup:

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 anyone help?

0
source share
1 answer

, . 1000 , :

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
                                          ...

SQL:

CREATE OR REPLACE FUNCTION get_result(val1 text, val2 text)
  RETURNS text AS 
$func$
   UPDATE table t
   SET    id_used = 'Y'
        , col1 = val1
        , id_used_date = now() 
   FROM  (
      SELECT id
      FROM   table 
      WHERE  id_used IS NULL
      AND    id_type = val2
      ORDER  BY id
      LIMIT  1
      FOR    UPDATE   -- lock to avoid race condition! see below ...
      ) t1
   WHERE  t.id_type = val2
   -- AND    t.id_used IS NULL -- repeat condition (not if row is locked)
   AND    t.id = t1.id
   RETURNING  id;
$func$  LANGUAGE sql;

:

  • SQL. . UPDATE .

  • PL/pgSQL . PL/pgSQL, UPDATE .

  • . , , :

, ; .

  • . , min(id) ORDER BY/LIMIT 1, . .

  • , id. , id PRIMARY KEY, . , , :

    CREATE INDEX foo_idx ON table (id_type, id)
WHERE id_used IS NULL;

, :

:

+2

More articles:


All Articles