Increment a sequence by more than one - Mailing list pgsql-sql

Hi,<br /><br /> I'm writing an import app in a third party language. It's going to use "copy to" to move data from
STDINto a postgres (8.2) table. There are some complexities though: it's going to copy the records to a "versioned"
tablefirst, and then at a later time the records will be copied by a different procedure to the "live" table. The live
tableand versioned table are identical in terms of their field definitions. But there is no sequence associated with
theversioned table (whose primary key is "id" plus "import_group_id", whereas the live table's pk is just "id"). So all
versionedtable entries must already "know" what their id would be in the live table. (This makes sense for other
businessprocess we have, but it's a bit of a problem in this instance).<br /><br /> My problem: I'd like to be able to
graba 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
currentmax pk in the live table is 540,203. I'd like to be able to increment the primary key sequence in such a way
thatI 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
540203and 545203 are unused.<br /><br /> I'm guessing this can be done with a stored procedure, but if possible I'd
liketo be able to run this command from my third party app without calling a stored procedure (we try not to use stored
procedureshere b/c we code entirely in this third party language - if we <i>had </i>to, it's possible we could install
astored procedure though). <br /><br /> But since I've seen so much magic on display from people on this list, I'm
goingto ask if it's possible to do this solely from PG SQL sent from a third party language? The main tricky bit seems
tobe 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" <b>and </b>"setval" call? Since pk
sequencefunctions like nextval cannot be rolled back, I'm guessing that "begin/end" won't do the job? <br /><br />
I.e:<br/><br /> -- need "magic lock" 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 "own" 5000 id's<br /> -- release magic lock here<br
/><br/> My fallback is to just have a routine that calls "nextval" a bunch of times and stores all the id's it gets -
theymay 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'sof course and just seems plain wrongheaded in many ways.<br /><br /> Any insights? All help is appreciated and
inputon a better way to solve the problem completely is of course welcome as well.<br /><br /> Sincerely,<br /><br />
Steve<br/>