CODEWITHSUNDEEP
postgresqlconcurrencytriggerslocking
IamIC
IamIC

Reputation: 18269

PostgreSQL BEFORE INSERT trigger locking behavior in a concurrent environment

I have a general function that can manipulate the sequence of any table (why is irrelevant to my question). It reads the current value, works out the new value, sets it, and returns its calculation, which is what's inserted. This is obviously a multi-step process.

I call it from a BEFORE INSERT trigger on tables where I need it.

All I need to know is am I guaranteed that the function will be called by only one caller at a time in a multi-user environment?

Specifically, does the BEFORE INSERT trigger have to complete before it is called again by another caller?

Logically, I would assume yes, but one never knows what may be going on under the hood.

If the answer is no, what minimal locking would I need on the function to guarantee I can read and write the sequence in a "thread-safe" manner?

I'm using PG 10.

EDIT

Here is the function updated with a lock:

CREATE OR REPLACE FUNCTION public.uts_set()                                     
RETURNS TRIGGER AS                                      
$$                                      
DECLARE                                     
  sv        int8;                               
  seq       text := format('%I.%I_uts_seq', tg_table_schema, tg_table_name);                                
BEGIN
  EXECUTE   format('LOCK TABLE %I IN ROW EXCLUSIVE MODE;', tg_table_name);
  EXECUTE   'SELECT last_value+1 FROM ' || seq INTO sv; -- currval(seq) isn't useable           
  PERFORM   setval(seq, GREATEST(sv, (EXTRACT(epoch FROM localtimestamp) * 1000000)::int8), false);                             
  RETURN    NULL;
END;                                        
$$ LANGUAGE plpgsql;

However, a SELECT already acquires ROW EXCLUSIVE, so this statement may be redundant and a stronger lock may be needed. Or, conversely, it may mean no lock is needed.

UPDATE

If I am reading this SO question correctly, my original version without the LOCK should work since the trigger acquires the same lock my updated function is redundantly taking.

Upvotes: 1

Views: 3630

Answers (2)

Michel Milezzi
Michel Milezzi

Reputation: 11175

All I need to know is am I guaranteed that the function will be called by only one caller at a time in a multi-user environment?

No. Not related to calling functions itself, but you can achieve this behaviour with SERIALIZABLE transaction isolation level:

This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently

But this approach would introduce several tradeoffs, such preparing your application to retry transactions with serialization failure.

Maybe a missed something, but I really believe that you just need NEXTVAL, something like below:

CREATE OR REPLACE FUNCTION public.uts_set()                                     
RETURNS TRIGGER AS                                      
$$                                      
DECLARE                                     
  sv        int8;      
  -- First, use %I wildcard for identifiers instead of %s
  seq       text := format('%I.%I', tg_table_schema, tg_table_name || '_uts_seq');                                
BEGIN              
  -- Second, you couldn't call CURRVAL on a session 
  -- that you didn't issued NEXTVAL before
  sv := NEXTVAL(seq);                         

  -- Do your logic here...

  -- Result is ignored since this is an STATEMENT trigger   
  RETURN    NULL;                               
END;                                        
$$ LANGUAGE plpgsql;

Remember that CURRVAL acts on session local scope and NEXTVAL on global scope, so you have a reliable thread-safe mechanism in hands.

Upvotes: 1

danjuggler
danjuggler

Reputation: 1320

The sequence itself handles thread safety with concurrent sessions. So it real comes down to the code that is interacting with the sequence. The following code is thread safe:

SELECT nextval('myseq');

If the sequence is doing much fancier things like setval and currval, I would be more worried about that being done in a high transaction/multi-user environment. Even so, the sequence itself should be locked from other queries while the sequence is being manipulated.

Upvotes: 0

Related Questions