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