In-Reply-To: <20070803192851.0D4449FB1D6@postgresql.org>
References: <20070803165058.020509FBF2F@postgresql.org>
	<dcc563d10708031201r1e741c13od9abd2eadb0e95cf@mail.gmail.com>
	<20070803192851.0D4449FB1D6@postgresql.org>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <F61ADF3B-57D2-49BD-9161-DEFD46F81DA2@seespotcode.net>
Cc: "Scott Marlowe" <scott.marlowe@gmail.com>,
 pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Increment a sequence by more than one
Date: Fri, 3 Aug 2007 15:31:04 -0500
To: Steve Midgley <public@misuse.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