Re: Does setval(nextval()+N) generate unique blocks of IDs?

On Mon, Aug 20, 2012 at 6:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Craig James <cjames(at)emolecules(dot)com> writes:
>> I want to do this:
>
>> select setval('object_id_seq', nextval('object_id_seq') + 1000, false);
>
>> Now suppose two processes do this simultaneously. Maybe they're in
>> transactions, maybe they're not. Are they guaranteed to get distinct
>> blocks of IDs?
>
> No, because the setval and the nextval are not indivisible.
>
>> Or is it possible that each will execute nextval() and
>> get N and N+1 respectively, and then do setval() to N+1000 and N+1001,
>> resulting in two overlapping blocks.
>
> Exactly.
>
>> If the answer is, "This won't work," then what's a better way to do this?
>
> AFAIK the only way at the moment is
>
> * acquire some advisory lock that by convention you use for this sequence
> * advance the sequence
> * release advisory lock
>
> There have been previous discussions of this type of problem, eg
> http://archives.postgresql.org/pgsql-hackers/2011-09/msg01031.php
> but the topic doesn't seem to have come up quite often enough to
> motivate anybody to do anything about it. Your particular case could be
> handled by a variant of nextval() with a number-of-times-to-advance
> argument, but I'm not sure if that's enough for other scenarios.

If the OP could live with large gaps in his sequence, he could set it
to advance by say 1000 at a time, and then use the numbers in that gap
freely. Just a thought.