Date: Fri, 03 Aug 2007 17:50:34 +0100
To: pgsql-sql@postgresql.org
From: Steve Midgley <public@misuse.org>
Subject: Increment a sequence by more than one
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=====================_559060328==.ALT"
Message-Id: <20070803165058.020509FBF2F@postgresql.org>

--=====================_559060328==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

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.

I'm guessing this can be done with a stored procedure, but if possible 
I'd like to be able to run this command from my third party app without 
calling a stored procedure (we try not to use stored procedures here 
b/c we code entirely in this third party language - if we had to, it's 
possible we could install a stored procedure though).

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? 
Since pk sequence functions like nextval cannot be rolled back, I'm 
guessing that "begin/end" won't do the job?

I.e:

-- need "magic lock" statement on pk sequence here
nextval
-- returns 540203
setval(545203)
-- now sequence is set to where I want it and I "own" 5000 id's
-- release magic lock here

My fallback is to just have a routine that calls "nextval" a bunch of 
times and stores all the id's it gets - they may or may not be 
sequential but they'll be unique. This is going to be a really slow way 
to get a large number of id's of course and just seems plain 
wrongheaded in many ways.

Any insights? All help is appreciated and input on a better way to 
solve the problem completely is of course welcome as well.

Sincerely,

Steve

--=====================_559060328==.ALT
Content-Type: text/html; charset="us-ascii"

<html>
<body>
Hi,<br><br>
I'm writing an import app in a third party language. It's going to use
&quot;copy to&quot; 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
&quot;versioned&quot; table first, and then at a later time the records
will be copied by a different procedure to the &quot;live&quot; 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 &quot;id&quot; plus &quot;import_group_id&quot;,
whereas the live table's pk is just &quot;id&quot;). So all versioned
table entries must already &quot;know&quot; 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).<br><br>
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.<br><br>
I'm guessing this can be done with a stored procedure, but if possible
I'd like to be able to run this command from my third party app without
calling a stored procedure (we try not to use stored procedures here b/c
we code entirely in this third party language - if we <i>had </i>to, it's
possible we could install a stored procedure though). <br><br>
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 &quot;lock&quot; the
sequence generator for the duration of a &quot;nextval&quot; <b>and
</b>&quot;setval&quot; call? Since pk sequence functions like nextval
cannot be rolled back, I'm guessing that &quot;begin/end&quot; won't do
the job? <br><br>
I.e:<br><br>
-- need &quot;magic lock&quot; statement on pk sequence here<br>
nextval<br>
-- returns 540203<br>
setval(545203)<br>
-- now sequence is set to where I want it and I &quot;own&quot; 5000
id's<br>
-- release magic lock here<br><br>
My fallback is to just have a routine that calls &quot;nextval&quot; a
bunch of times and stores all the id's it gets - they may or may not be
sequential but they'll be unique. This is going to be a really slow way
to get a large number of id's of course and just seems plain wrongheaded
in many ways.<br><br>
Any insights? All help is appreciated and input on a better way to solve
the problem completely is of course welcome as well.<br><br>
Sincerely,<br><br>
Steve<br>
</body>
</html>

--=====================_559060328==.ALT--