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.
> 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
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.
regards, tom lane
In response to
pgsql-performance by date
|Next:||From: Scott Marlowe||Date: 2012-08-21 00:59:43|
|Subject: Re: Does setval(nextval()+N) generate unique blocks of IDs?|
|Previous:||From: Craig James||Date: 2012-08-20 23:32:27|
|Subject: Does setval(nextval()+N) generate unique blocks of IDs?|