How to prevent concurrent function invocations?

Question

I'm having a problem:

I have a PostgreSQL function that performs a FOR loop over table_a records and inserts into table_b using data from both tables, and all records are linked by the same process number.

Example:

DECLARE
......

BEGIN

FOR rec in 

(select * from table_a where *some_condition*)

LOOP

some_value := (select value from table_b where some_condition_2);

insert into table_b (some_field, process) VALUES (some_value + rec.other_value, some_process);


END LOOP

COMMIT;

END;

if I call that function simultaneously in two sessoins, the expected result in table_a should be:

(process 1) record 1
(process 1) record 2
(process 1) record 3
(process 1) record 4
(process 1) record 5
(process 2) record 6
(process 2) record 7
(process 2) record 8
(process 2) record 9
(process 2) record 10

because I guess when I call the function, all inserts should be executed in a row without interruption.

But sometimes they get executed in this way:

(process 1) record 1
(process 1) record 2
(process 1) record 3
(process 2) record 4 (*)
(process 1) record 5
(process 2) record 6 (*)
(process 1) record 7
(process 2) record 8
(process 2) record 9
(process 2) record 10

and the value of some_field in table_a is wrong.

why is this happening?

Laurenz Albe · Accepted Answer · 2020-12-18 17:43:03Z

3

There is nothing that keeps the function from running concurrently in multiple sessions and concurrently selecting and inserting data.

To make sure that no two invocations of the function can run at the same time, you best use an advisory lock:

CREATE FUNCTION ... AS
$$BEGIN
   PERFORM pg_advisory_lock(42);

   /* processing */

   PERFORM pg_advisory_unlock(42);
END;$$;

The key 42 is a number of your choosing.

edited Dec 18, 2020 at 17:43

answered Dec 18, 2020 at 17:37

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to prevent concurrent function invocations?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related