On Aug 3, 2007, at 11:50 , Steve Midgley wrote:
> 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.
Setting the next number that will be taken is generated is
straightforward.
ALTER SEQUENCE foo_seq RESTART WITH 545203;
Perhaps doing something like (untested):
ALTER SEQUENCE foo_seq RESTART WITH (SELECT last_value + CASE WHEN
is_called THEN 1 ELSE 0 END);
You'll need to manage the skipped values yourself though, of course.
Perhaps set up a table to hold the current number used in the skipped
range. Basically this would be the same approach as that used by
people who need to guarantee gapless sequences: you can check the
archives for details, but basically you need to make sure the table
is properly locked when you're planning to use a new number.
> 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'm not sure, but perhaps calling SELECT FOR UPDATE on the sequence
itself (i.e., not using nextval/setval) would give the appropriate
lock, though I wouldn't be surprised if it isn't possible. As you've
noted, sequences are designed for performance to ignore transactions,
so this may not be possible (though perhaps that's just values
returned via the nextval function).
Hope this gives you some additional ideas on how to handle this.
Michael Glaesemann
grzm seespotcode net