From: Michael Glaesemann <grzm@seespotcode.net>
To: Steve Midgley <public@misuse.org>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Increment a sequence by more than one
Date: Fri, 3 Aug 2007 15:31:04 -0500
Message-ID: <F61ADF3B-57D2-49BD-9161-DEFD46F81DA2@seespotcode.net> (raw)
In-Reply-To: <20070803192851.0D4449FB1D6@postgresql.org>
References: <20070803165058.020509FBF2F@postgresql.org>
<dcc563d10708031201r1e741c13od9abd2eadb0e95cf@mail.gmail.com>
<20070803192851.0D4449FB1D6@postgresql.org>
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
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: grzm@seespotcode.net, public@misuse.org, scott.marlowe@gmail.com
Subject: Re: Increment a sequence by more than one
In-Reply-To: <F61ADF3B-57D2-49BD-9161-DEFD46F81DA2@seespotcode.net>
* 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