2

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?

Artem Larin
  • 142
  • 6

2 Answers2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

You can use two requests in the same transaction:

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

Note: the first command will lock the sequence for other transactions

Serega
  • 1
  • 1