Thread: Getting consecutive sequence values

(The first copy of this got stalled because of internal addressing issues,
hopefully it will not be duplicated.)

I'm using a sequence to generate unique 8 digit record keys which will be
given out as membership ID's.

Because we want to be able to pre-assign blocks of membership numbers
for use in off-line membership registration situations, I need to be able
to ensure that a block of sequences are in consecutive order even though
they may be requested concurrently with other activity on the sequence.

If I use SQL such as "select nextval('id'),nextval('id'),...,nextval('id')"
to allocate 50 or 100 sequence numbers, will the backend ensure that
those sequence numbers are a consecutive block?
--
Mike Nolan
nolan@tssi.com

try

select setval('id',nextval('id')+100)

nolan@celery.tssi.com wrote:

>(The first copy of this got stalled because of internal addressing issues,
>hopefully it will not be duplicated.)
>
>I'm using a sequence to generate unique 8 digit record keys which will be
>given out as membership ID's.
>
>Because we want to be able to pre-assign blocks of membership numbers
>for use in off-line membership registration situations, I need to be able
>to ensure that a block of sequences are in consecutive order even though
>they may be requested concurrently with other activity on the sequence.
>
>If I use SQL such as "select nextval('id'),nextval('id'),...,nextval('id')"
>to allocate 50 or 100 sequence numbers, will the backend ensure that
>those sequence numbers are a consecutive block?
>--
>Mike Nolan
>nolan@tssi.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>

you might try multiple sequences started with different values.

seq_from800
seq_from900,

etc.

A better way would be to use an intermediate table with the sequence showing
which values have been used. Not clearly thought out in my head though.

nolan@celery.tssi.com wrote:
> (The first copy of this got stalled because of internal addressing issues,
> hopefully it will not be duplicated.)
>
> I'm using a sequence to generate unique 8 digit record keys which will be
> given out as membership ID's.
>
> Because we want to be able to pre-assign blocks of membership numbers
> for use in off-line membership registration situations, I need to be able
> to ensure that a block of sequences are in consecutive order even though
> they may be requested concurrently with other activity on the sequence.
>
> If I use SQL such as "select nextval('id'),nextval('id'),...,nextval('id')"
> to allocate 50 or 100 sequence numbers, will the backend ensure that
> those sequence numbers are a consecutive block?
> --
> Mike Nolan
> nolan@tssi.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

> select setval('id',nextval('id')+100)

That's certainly tidier than my lengthy SQL statement, but I don't know
that it solves the concurrent access issue.  What happens if a 2nd call
to nextval is issued at the same time?
--
Mike Nolan

I'm assuming that the setval would create an exclusive lock on the
sequence until it is complete. Tom/Bruce?


nolan@celery.tssi.com wrote:

>>select setval('id',nextval('id')+100)
>>
>>
>
>That's certainly tidier than my lengthy SQL statement, but I don't know
>that it solves the concurrent access issue.  What happens if a 2nd call
>to nextval is issued at the same time?
>--
>Mike Nolan
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

On Friday April 4 2003 1:53, nolan@celery.tssi.com wrote:
> > select setval('id',nextval('id')+100)
>
> That's certainly tidier than my lengthy SQL statement, but I don't know
> that it solves the concurrent access issue.  What happens if a 2nd call
> to nextval is issued at the same time?

Have you considered creating a sequence object with a cache size of 100?

    CREATE SEQUENCE id CACHE 100;

This will cause the sequence number to be handed in groups of 100, whatever
the next available group of 100 is, and when that is exhausted, it'll do
what you'd expect.  So if session 1 grabs a value, it gets ids 1-100, and
then session 2 gets 101-200, and then if session 1 runs out and grabs
another, it automatically gets 201-300.

Ed

Dave Smith <dave.smith@candata.com> writes:
> I'm assuming that the setval would create an exclusive lock on the
> sequence until it is complete. Tom/Bruce?

But the setval() doesn't start to execute until some time after the
nextval() completes.  The window during which you're executing the
addition of 100 would easily allow some other process to perform a
nextval().  Mike's right, this doesn't work.

            regards, tom lane

> Have you considered creating a sequence object with a cache size of 100?
>
>     CREATE SEQUENCE id CACHE 100;

The cache resides at the connection level (ie, psql or equivalent).

If the front end is a web engine which does not use persistent connections,
wouldn't that waste a lot of sequence values?

The organization is already committed to an eight digit ID number from
its legacy environment, so I've got to be at least a little parsimonious
when allocating sequence value/ID's.

The need to allocate a block of sequence values is the exception in this
application, not the rule.

Another suggestion was made to use multiple sequences, presumably so that
blocks are allocated from one range and individual values from the other.
I don't think that resolves the problem, as in theory there could just
as easily be more than one user simutaneously requesting blocks of values
as users simultaneously requesting a single value.

One possibility I have already considered is using a table lock (probably
on a dummy table) for the duration of the interval during which individual
or blocks of sequence numbers are being requested.

For those curious, this application is for the United States Chess
Federation.  They're a small enough organization that the concurrent
access issue for sequence blocks is more of a theoretical issue than a
practical one .  It is probably going to be more of an issue when I
start considering ways to lock rows against simultaneous update,
especially if we use a web-based front end for a query/maintenance tool.
--
Mike Nolan