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