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®
[EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
