In-Reply-To: <20070803165058.020509FBF2F@postgresql.org>
References: <20070803165058.020509FBF2F@postgresql.org>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <BA0AEA3C-A9C1-462E-9F86-34755121673D@myemma.com>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik@myemma.com>
Subject: Re: Increment a sequence by more than one
Date: Fri, 3 Aug 2007 15:27:25 -0500
To: Steve Midgley <public@misuse.org>

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 =20
> use "copy to" to move data from STDIN to a postgres (8.2) table. =20
> There are some complexities though: it's going to copy the records =20
> to a "versioned" table first, and then at a later time the records =20
> will be copied by a different procedure to the "live" table. The =20
> live table and versioned table are identical in terms of their =20
> field definitions. But there is no sequence associated with the =20
> versioned table (whose primary key is "id" plus "import_group_id", =20
> whereas the live table's pk is just "id"). So all versioned table =20
> entries must already "know" what their id would be in the live =20
> table. (This makes sense for other business process we have, but =20
> 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 =20
> live table's pk sequence. So let's say my importer has 5,000 new =20
> rows to import and the current max pk in the live table is 540,203. =20=

> I'd like to be able to increment the primary key sequence in such a =20=

> way that I get a block of ids all to myself and the sequence is =20
> reset to 545,203 with a guarantee that all the id's between 540203 =20
> and 545203 are unused.
>
> I'm guessing this can be done with a stored procedure, but if =20
> possible I'd like to be able to run this command from my third =20
> party app without calling a stored procedure (we try not to use =20
> stored procedures here b/c we code entirely in this third party =20
> language - if we had to, it's possible we could install a stored =20
> procedure though).
>
> But since I've seen so much magic on display from people on this =20
> list, I'm going to ask if it's possible to do this solely from PG =20
> SQL sent from a third party language? The main tricky bit seems to =20
> be ensuring that everything is locked so two such increment calls =20
> at the same time don't yield overlapping blocks of ids. Is there a =20
> way to "lock" the sequence generator for the duration of a =20
> "nextval" and "setval" call? Since pk sequence functions like =20
> nextval cannot be rolled back, I'm guessing that "begin/end" won't =20
> 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 =20
> of times and stores all the id's it gets - they may or may not be =20
> sequential but they'll be unique. This is going to be a really slow =20=

> way to get a large number of id's of course and just seems plain =20
> wrongheaded in many ways.
>
> Any insights? All help is appreciated and input on a better way to =20
> solve the problem completely is of course welcome as well.

Is there actually a requirement that the block of 5000 values not =20
have gaps?  If not, why not make the versioned table's id column =20
default to nextval from the same sequence?  Then when the data is =20
copied over to the live table, as long as you supply the the id it =20
won't generate a new id and you'll maintain your row-row =20
relationships.  If you do require that the block not have gaps, check =20=

out the article on how to do this here: http://www.varlena.com/=20
varlena/GeneralBits/130.php

Erik Jones

Software Developer | Emma=AE
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