Re: allocate chunk of sequence

PFC wrote:
>
>> The chunk to be allocated is not the same size, so to set the
>> increment value will not help.
>
>     I'm sometimes not that subtle, so I'd just use a BIGINT sequence.
> Think about the largest chunk you'll ever get (probably less than 2^30
> rows, yes ?), set this sequence increment to this very high value
> (this will still let you have 2^32 chunks at least), and make each
> chunk be (big_sequence_value + N).
And that's only if they need to be contiguous.  If they don't, then just
grab however many you need one after the other.
>
>> I'm not sure how the nextval function to handle this internally, if
>> it has to read and update the sequence object. Does it use some kind
>> of lock ? Otherwise the problem mentioned here should happen to
>> nextval function also.
>
>     Yes it takes a lock, but the lock is held for a very short time
> (just increment the value and release the lock).
And that lock will be WAY shorter and bothersome than the lock you'll
need if you do select setval('seqname',select max(id)+1 from sometable,
FALSE); in the middle of your UDF.

>     In InnoDB, the AUTO_INCREMENT lock is held for the entire duration
> of the SQL statement, including any triggers that might fire. This
> means if you have ON INSERT triggers which take some time to process,
> INSERT concurrency is horrendous. Not so with Postgres sequences.
Note that that hasn't been true for some time now, according to Heikki
Tuuri (sp?).  He told me they changed the way that worked about halfway
through the 5.0 dev cycle so that they use a kind of internal sequence
much like postgresql.  Except in postgresql the sequence is exposed.

Again, needing contiguous ids is gonna cause a problem.  It is going to
serialize all inserts to your database, and slow it down in any kind of
large parallel environment.

If you can get away without them then do so.