Thread: Get block of N numbers from sequence
Hi, how can you get N numbers (without holes) from a sequence? Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de
On Tue, May 19, 2009 at 12:45 PM, Thomas Guettler <hv@tbz-pariv.de> wrote: > Hi, > > how can you get N numbers (without holes) from a sequence? generate_series(), or make sure there's only one connection, no transactions rollback. -- GJ
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
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;
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On Tue, May 19, 2009 at 03:32:16PM +0200, Thomas Guettler wrote:
> If other processes run nextval() between "increment by 1000" and "increment by 1",
> they leave big holes in the sequence.
yes. I know. there is no other way.
if you want 1000 ids, but they don't have to be consequtive, you can
simply:
select nextval('qq') from generate_series(1,1000);
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Thomas Guettler írta:
> Hi,
>
> how can you get N numbers (without holes) from a sequence?
>
> Thomas
>
>
# create sequence tmp_seq cache 1000;
CREATE SEQUENCE
From the same client:
# select nextval('tmp_seq');
nextval
---------
1
(1 sor)
# select nextval('tmp_seq');
nextval
---------
2
(1 sor)
# select nextval('tmp_seq');
nextval
---------
3
(1 sor)
# select nextval('tmp_seq');
nextval
---------
4
(1 sor)
... (ad nauseum)
From another psql client:
# select nextval('tmp_seq');
nextval
---------
1001
(1 sor)
# select nextval('tmp_seq');
nextval
---------
1002
(1 sor)
# select nextval('tmp_seq');
nextval
---------
1003
(1 sor)
# select nextval('tmp_seq');
nextval
---------
1004
(1 sor)
# select nextval('tmp_seq');
nextval
---------
1005
(1 sor)
...
You can get up to 1000 (or the number specified as CACHE N)
numbers in a series (without holes) in he same client at once,
you don't even need to be in the same transaction.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler <hv@tbz-pariv.de> wrote:
>
>
> 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.
This is only works if everyone does it this way. If anybody throws a
nextval() without locking the sequence first you have a race. Also,
since alter sequence takes a full lock your concurrency is zero.
Probably the best general way to attack this problem is using advisory
locks. note the code below is untested.
create or replace function my_nextval(_seq text, _count int, _v out
bigint) returns bigint as
$$
begin
if _count = 1 then
perform pg_advisory_lock_shared(999);
_v := nextval(_seq);
perform pg_advisory_unlock_shared(999);
else
perform pg_advisory_lock(999);
_v := nextval(_seq);
perform setval(_seq, _v + _count);
perform pg_advisory_unlock(999);
end if;
end;
$$ language plpgsql;
This complexity is worthwhile if you need to intermix standard
nextval() with block allocations. the number 999 has no significance
except as a lock id...see the docs on advisory locks. The advantage
here is that nextval() calls do not block each other and the full lock
is extremely short term, so you can safely call this during longer
running transaction.
merlin
Merlin Moncure wrote:
> On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler <hv@tbz-pariv.de> wrote:
>>
>> 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.
>
> This is only works if everyone does it this way. If anybody throws a
> nextval() without locking the sequence first you have a race. Also,
> since alter sequence takes a full lock your concurrency is zero.
>
> Probably the best general way to attack this problem is using advisory
> locks. note the code below is untested.
If you want to be REALLY sure your sequence is never accessed without
being locked first, you can deny rights to access it to the usual users,
and write a Pl/PgSQL SECURITY DEFINER function to do all manipulation of
the sequence.
The sample function you posted could be trivially adjusted to operate
SECURITY DEFINER and would suit the purpose. Note that I haven't
examined this in great depth for security issues, and there may be
things I'm missing about the safe use of SECURITY DEFINER functions.
create or replace function my_nextval_for_seqname(
_count int, _v out bigint) returns bigint as
$$
declare
-- Hard code sequence name; we're running SECURITY DEFINER
-- and don't want the caller to be able to mess with any
-- sequence they choose to.
_seq text := 'seqname';
begin
if _count = 1 then
perform pg_advisory_lock_shared(999);
_v := nextval(_seq);
perform pg_advisory_unlock_shared(999);
else
perform pg_advisory_lock(999);
_v := nextval(_seq);
perform setval(_seq, _v + _count);
perform pg_advisory_unlock(999);
end if;
end;
$$ language plpgsql
VOLATILE
STRICT
SECURITY DEFINER;
--
Craig Ringer
Boszormenyi Zoltan schrieb:
> Thomas Guettler írta:
>> Hi,
>>
>> how can you get N numbers (without holes) from a sequence?
> # create sequence tmp_seq cache 1000;
Hi,
"alter SEQUENCE ... cache 100" survives a rollback. That's something I like to avoid:
foo_esg_modw=# select * from foo_isu_isu_id_seq; begin; alter SEQUENCE foo_isu_isu_id_seq cache 100; rollback; select
*
from foo_isu_isu_id_seq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt |
is_cycled | is_called
------------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
foo_isu_isu_id_seq | 90508740 | 1 | 9223372036854775807 | 1 | 1 | 6 | f |
t
BEGIN
ALTER SEQUENCE
ROLLBACK
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt |
is_cycled | is_called
------------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
foo_isu_isu_id_seq | 90508740 | 1 | 9223372036854775807 | 1 | 100 | 6 | f |
t
--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de
Thomas Guettler schrieb: > Hi, > > how can you get N numbers (without holes) from a sequence? > If sequences could be locked like tables, it would be easy. In old versions of postgres it worked: http://archives.postgresql.org//pgsql-hackers/2001-10/msg00930.php Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de
On Wed, May 20, 2009 at 3:00 AM, Thomas Guettler <hv@tbz-pariv.de> wrote: > Thomas Guettler schrieb: >> Hi, > >> how can you get N numbers (without holes) from a sequence? >> > > If sequences could be locked like tables, it would be easy. advisory locks are superior for locking sequences in almost every way.... merlin