Is setval nextval increment on PostgreSQL sequence thread safe?

user_7765677 0
Viewed 777

I want to increment a sequence by some batchSize in PostgreSQL but I am not sure if this query is safe from concurrent calls made to the same sequence.

select setval('my_sequence', nextval('my_sequence') + batchSize);

What I am worried bout given the query above is that the call for setval() of the sequence would set the sequence back or perhaps abort the query since if another thread has fetched it nextval() concurrently.

For example:

Thread 1. nextval = 1001 batchSize = 100 sets sequence to 1101

Tread 2. nextval = 1002 batchSize = 20 sets sequence to 1022

This would mean that the sequence could ultimately return duplicate sequenceIds for for ids between 1002 and 1101.

The same could be achieved using the generate_series() function. The drawback would be that the series is not guaranteed to be in sequence since other threads can be calling nextval() concurrently to the same sequence which means that I have to fetch and parse the generated series.

sequence postgresql sql
3 Answers

From the PG Documentation

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused “holes” in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain “gapless” sequences. Likewise, any sequence state changes made by setval are not undone if the transaction rolls back.

I would assume the above states that is thread safe.

user_76057620

According to the PostgreSQL documentation, it is:

This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value

Which means, it's totally safe to call NEXTVAL concurrently, every single invocation will get its own unique value, guarranted to not to repeat.

user_25572630

According to answer in PostgreSQL mailing lists SELECT setval(nextval() + N) isn't safe in concurrent scenario. Safe and still somewhat performant alternative would be:

SELECT nextval('my_seq')
FROM generate_series(1, N)
user_21225290
Related