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