Re: Increment a sequence by more than one

On Aug 3, 2007, at 14:28 , Steve Midgley wrote:

AIUI, one difference between the solutions Scott and I proposed is  
that while INCREMENT is set at 5000, each time nextval is called the  
sequence is incremented by 5000. For example:

test=# select nextval('foos_foo_id_seq');
nextval
---------       1
(1 row)

test=# select nextval('foos_foo_id_seq');
nextval
---------       2
(1 row)

test=# alter sequence foos_foo_id_seq increment 5000;
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq');
nextval
---------    5002
(1 row)

test=# select nextval('foos_foo_id_seq');
nextval
---------   10002
(1 row)


The only issue with this is that it burns through sequence values  
faster. That may not be a concern, of course.

I wonder if there isn't a way to use ALTER SEQUENCE ... CACHE to  
handle this:

-- Alice's session
test=# select nextval('foos_foo_id_seq');
nextval
---------   15002
(1 row)

-- Bob's session
test=# select nextval('foos_foo_id_seq');
nextval
---------   15003
(1 row)

test=# select nextval('foos_foo_id_seq');
nextval
---------   15004
(1 row)

-- Alice's session
test=# alter sequence foos_foo_id_seq cache 5000;
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq'); nextval
---------   20003
(1 row)

test=# select nextval('foos_foo_id_seq');
nextval
---------   20004
(1 row)

Now you should be able to safely use the values from nextval to  
nextval + cache without worrying that the values in that range are  
going to be used by another backend. It looks like you could even do:



-- Alice's session
test=# select nextval('foos_foo_id_seq');
nextval
---------   30096
(1 row)

test=# alter sequence foos_foo_id_seq cache 10;  -- set cache to  
preallocate
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq'); -- get nextval and  
preallocate the next 10
nextval
---------   30097
(1 row)

-- Bob's session
test=# select nextval('foos_foo_id_seq');
nextval
---------   30107
(1 row)

-- Alice's session
test=# alter sequence foos_foo_id_seq cache 1; -- reset cache so  
other backends aren't burning sequence values unnecessarily
ALTER SEQUENCE
test=# select nextval('foos_foo_id_seq'); -- note that the previously  
cached values (for both Alice and Bob's session) are discarded, and  
available for manual entry
nextval
---------   30117
(1 row)


Again, you can set up another table to keep track of the values that  
are going to be used manually, perhaps something like:

CREATE TABLE preallocated_foo_ids
(    current_value INTEGER NOT NULL    , maximum_value INTEGER NOT NULL    , check (current_value <= maximum_value)
);

Then, to use:

test=# ALTER SEQUENCE foos_foo_id_seq CACHE 10;
ALTER SEQUENCE
test=# DELETE FROM preallocated_foo_ids; -- clear old ones
DELETE 1
test=# INSERT INTO preallocated_foo_ids (current_value, maximum_value)
test-# SELECT val, val + 10
test-# FROM (SELECT nextval('foos_foo_id_seq')) AS seq(val);
INSERT 0 1
test=# ALTER SEQUENCE foos_foo_id_seq CACHE 1;
ALTER SEQUENCE
test=# SELECT * FROM preallocated_foo_ids;
current_value | maximum_value
---------------+---------------         30142 |         30152
(1 row)

You've now got a preallocated range in preallocated_ids. Just use  
standard table locking on preallocated_foo_ids: there shouldn't be  
heavy contention on this table during your load, so I don't think  
performance should suffer too badly.

test=# BEGIN;
BEGIN
test=# SELECT current_value
test-# FROM preallocated_foo_ids
test-# FOR UPDATE;
current_value
---------------         30142
(1 row)

test=# INSERT INTO foos (foo_id) VALUES (30142);
INSERT 0 1
test=# UPDATE preallocated_foo_ids
test-# SET current_value = current_value + 1;
UPDATE 1
test=# COMMIT;
COMMIT
test=# SELECT * FROM preallocated_foo_ids;
current_value | maximum_value
---------------+---------------         30143 |         30152
(1 row)

When you run into an error because of the CHECK constraint, you know  
you've hit the end of your range (if you haven't been checking  
otherwise).

> Regarding Michael's suggestion - I tried messing around with LOCK  
> and similar commands but they're only allowed to run against TABLES  
> not SEQUENCES - too bad - that would have been perfect.

Yeah, I thought that might be the case.

Michael Glaesemann
grzm seespotcode net