Should I lock a PostgreSQL table when invoking setval for sequence with the max ID function?

Question

I have the following SQL script which sets the sequence value corresponding to max value of the ID column:

SELECT SETVAL('mytable_id_seq', COALESCE(MAX(id), 1)) FROM mytable;

Should I lock 'mytable' in this case in order to prevent changing ID in a parallel request, such in the example below?

 request #1    request #2

 MAX(id)=5

              inserted id 6

 SETVAL=5

Or setval(max(id)) is an atomic operation?

Answer 1

Your suspicion is right, this approach is subject to race conditions.

But locking the table won't help, because it won't keep a concurrent transaction from fetching new sequence values. This transaction will block while the table is locked, but will happily continue inserting once the lock is gone, using a sequence value it got while the table was locked.

If it were possible to lock sequences, that might be a solution, but it is not possible to lock sequences.

I can think of two solutions:

1. Remove all privileges on the sequence while you modify it, so that concurrent requests to the sequence will fail. That causes errors, of course.

2. The pragmatic way: use

   SELECT SETVAL('mytable_id_seq', COALESCE(MAX(id), 1) + 100000) FROM mytable;
   

   Here 100000 is a value that is safely bigger than the number rows that might get inserted while your operatoin is running.