DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=slXUuYqb4wgDbFQgTsipNd8hHBGgeEqo+JqjoMWm/mtnXRJqFzr3EIBCpYi0DGyh9pc6PCWGCh2FFYElpXAYT1/+6B3x+tDgh+nnD1/VoFuFuMiTWYXsFbM7sDdW7Re/revmGu65+eE10S2H7FCfpPWGeTqhgNxqGqak9bTKUYQ=
Message-ID: <dcc563d10708031201r1e741c13od9abd2eadb0e95cf@mail.gmail.com>
Date: Fri, 3 Aug 2007 14:01:38 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Steve Midgley" <public@misuse.org>
Subject: Re: Increment a sequence by more than one
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20070803165058.020509FBF2F@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20070803165058.020509FBF2F@postgresql.org>

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.