From: Steve Midgley <public@misuse.org>
To: pgsql-sql@postgresql.org
Subject: Increment a sequence by more than one
Date: Fri, 03 Aug 2007 17:50:34 +0100
Message-ID: <20070803165058.020509FBF2F@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: public@misuse.org
Subject: Re: Increment a sequence by more than one
In-Reply-To: <20070803165058.020509FBF2F@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox