Re: SERIAL datatype

Thanks. I thought it was a bit counter-intuitive to have a BIGSERIAL
while I will only have a few thousands of entries, which are updated (by
DELETE and INSERT) constantly.

Thanks Scott,

Peter

Scott Marlowe schreef:
> On Thu, Aug 21, 2008 at 1:08 PM, Peter Billen <peter@clueless.be> wrote:
>
>> Hi all,
>>
>> I would like to ask a question about the serial datatype. Say I have a field
>> of type serial, and say for the sake of example that the range of a serial
>> goes from 1 to 5 (inclusive). I insert 5 (ed) entries into the table, so the
>> table is 'full':
>>
>> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
>> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
>> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
>> INSERT INTO my_table (my_serial) VALUES (DEFAULT);
>>
>> Next I delete a random entry, say the one with value 3:
>>
>> DELETE FROM my_table WHERE my_serial = 3;
>>
>> Is it possible to insert a new entry? Will the serial sequence somehow be
>> able to find the gap (3)?
>>
>
> No, sequences do not fill in gaps.
>
>
>> The reason why I am asking is because I have a table in which constantly
>> entries are being deleted and inserted. What happens if the serial sequence
>> is exhausted? If it is not able to go the the next gap, how is it possible
>> to keep inserting and deleting entries once the serial sequence has been
>> exhausted? I can't find this anywhere in docs.
>>
>
> Regular SERIAL type is limited to a 32 bit int.  BIGSERIAL uses a 64
> bit int.  That will give you an upper limit of 2^63, assuming positive
> values only in the sequence.  If you run out of that many values
> you're running a seriously busy database over a very long time.
>
> My rough guesstimate is that at 2000 inserts per second, it would take
> approximately 145,865,043 years to exhaust a BIGSERIAL.  I might be
> off by a factor of ten or so there.  But I don't think I am.  Note
> that an insert rate of 2000 per second would exhaust a regular SERIAL
> type (2^31 size) in 12 days.
>
>
>> To me, it is perfectly possible that there is only one entry in the table,
>> with a serial value equal to its upper limit.
>>
>
> That's fine too.  If you need gapless sequences, be prepared to pay
> more in terms of overhead costs.  If you don't need gapless sequences
> (and usually you don't) then use either SERIAL or BIGSERIAL.
>
>