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

On Aug 3, 2007, at 11:50 AM, Steve Midgley 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.
>
> 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.

Is there actually a requirement that the block of 5000 values not
have gaps?  If not, why not make the versioned table's id column
default to nextval from the same sequence?  Then when the data is
copied over to the live table, as long as you supply the the id it
won't generate a new id and you'll maintain your row-row
relationships.  If you do require that the block not have gaps, check
out the article on how to do this here: http://www.varlena.com/
varlena/GeneralBits/130.php

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com