Re: Get block of N numbers from sequence

hubert depesz lubaczewski schrieb:
> On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
>> how can you get N numbers (without holes) from a sequence?
>
> alter sequence XXX increment by 1000;
> select nextval('XXX');
> alter sequence XXX increment by 1;

If other processes run nextval() between "increment by 1000" and "increment by 1",
they leave big holes in the sequence.

Unfortunately begin; ... rollback; does not help. the "alter sequence" command gets
executed, even if the transaction gets rolled back.

db=# begin; alter SEQUENCE foo_seq increment by 100; rollback;
BEGIN
ALTER SEQUENCE
ROLLBACK

db=# select * from foo_seq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt |
is_cycled | is_called
------------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
foo_seq | 90508740 | 100 | 9223372036854775807 | 1 | 1 | 6 | f | t

db=# select version();
PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux)

Thomas

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de