sql - PostgreSQL 序列线程上的 setval nextval 增量是否安全？

Question

我想在 PostgreSQL 中将一个序列递增一些 batchSize，但我不确定这个查询对于对同一序列的并发调用是否安全。

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

鉴于上面的查询，我担心的是对序列的 setval() 的调用会将序列设置回去或者可能中止查询，因为如果另一个线程已经获取它 nextval() 同时进行。

例如:

线程 1.nextval = 1001 batchSize = 100 将序列设置为 1101

胎面 2.nextval = 1002 batchSize = 20 将序列设置为 1022

这意味着对于 1002 到 1101 之间的 ID，序列最终可能会返回重复的 sequenceId。

同样可以使用 generate_series() 函数来实现。缺点是不能保证系列是按顺序排列的，因为其他线程可以同时调用 nextval() 到相同的序列，这意味着我必须获取并解析生成的系列。

Answer 1

来自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.

我假设上述状态是线程安全的。