Re: Increment a sequence by more than one

On 8/3/07, Steve Midgley <public@misuse.org> wrote:
>
>  Hi,
>
>  I'm writing an import app in a third party language. It's going to use
> "copy to" to move data from STDIN to a postgres (8.2) table. There are some
> complexities though: it's going to copy the records to a "versioned" table
> first, and then at a later time the records will be copied by a different
> procedure to the "live" table. The live table and versioned table are
> identical in terms of their field definitions. But there is no sequence
> associated with the versioned table (whose primary key is "id" plus
> "import_group_id", whereas the live table's pk is just "id"). So all
> versioned table entries must already "know" what their id would be in the
> live table. (This makes sense for other business process we have, but it's a
> bit of a problem in this instance).
>
>  My problem: I'd like to be able to grab a block of id's from the live
> table's pk sequence. So let's say my importer has 5,000 new rows to import
> and the current max pk in the live table is 540,203. I'd like to be able to
> increment the primary key sequence in such a way that I get a block of ids
> all to myself and the sequence is reset to 545,203 with a guarantee that all
> the id's between 540203 and 545203 are unused.

The real danger in doing this is race conditions.  Most anything you
do involves a possible race condition.  As long as the race condition
doesn't result in an id getting used twice, you're safe.

So:

test=# create sequence m;
CREATE SEQUENCE
test=# select nextval('m');nextval
---------      1
(1 row)

test=# alter sequence m increment by 5000;
ALTER SEQUENCE
test=# select nextval('m');nextval
---------   5001
(1 row)

test=# alter sequence m increment by 1;
ALTER SEQUENCE
test=# select nextval('m');nextval
---------   5002
(1 row)

In this method, the only possible race condition is that someone might
run a nextval('m') between the time you set the increment to 5000 and
1 again.  If that happens, you'd have 5,000 missing ids, but since
sequences are designed to prevent dupes, not holes, that's ok.

>  But since I've seen so much magic on display from people on this list, I'm
> going to ask if it's possible to do this solely from PG SQL sent from a
> third party language? The main tricky bit seems to be ensuring that
> everything is locked so two such increment calls at the same time don't
> yield overlapping blocks of ids. Is there a way to "lock" the sequence
> generator for the duration of a "nextval" and "setval" call?

Avoiding the setval is the real key.  It doesn't scale.  Missing 5,000
ids is no big deal.  repeating them IS a big deal.  Not using setval
is the best answer.