Re: Does setval(nextval()+N) generate unique blocks of IDs?

On Tue, Aug 21, 2012 at 2:45 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> sometimes I hate my laptops touchpad. Ran something similar in php
> got similar performance. By comparison, running select 1 instead of
> nextval() took ~0.160s to run.

you're mostly measuring client overhead i think:

postgres=# explain analyze select nextval('s') from
generate_series(1,1000); explain analyze select nextval('s') from
generate_series(1,1000);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..12.50 rows=1000
width=0) (actual time=0.149..1.320 rows=1000 loops=1)
Total runtime: 1.806 ms

postgres=# do
$$
begin
for x in 1..1000 loop
perform nextval('s');
end loop;
end;
$$ language plpgsql;
DO
Time: 4.333 ms

Anyways, the only reason to do advisory locking is if you
a) strictly need contiguous blocks of ids
and
b) are worried about concurrency and the id is fetched early in a
non-trivial transaction

If a) isn't true, it's better to do looped nextval, and if b) isn't
true, IMO it's better to maintain a value in a table and let mvcc
handle things. Being able to grab sequences in a block without manual
locking would be a nice feature but only if it could be done without
adding an iota of overhead to standard usage :-).

merlin