Grant wrote:
>
> Please see below for my table schema. I have two questions.
>
> (1) Why is a sequence limited to 2147483647, it seems very small?
That's 2 billion(ish) - the largest signed 32 bit integer.
> (2) If I reset the sequence, then try another insert. It will not insert
> anything until it cycles through all sequences and finds an unused one. It
> will give the following error each time it tries to insert a row with a
> used sequence:
>
> PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique
> index releases_pkey
>
> How can I possibly get around this issue so that I can be sure to always
> have a free id without getting the error above?
Well - you've told PG to only allow unique id values and then reset the
sequence that generates its values. It's doing pretty much what you'd
expect, I'd say.
How fast are you inserting these bookings? According to my calculations
that's a sustained rate of 68 inserts/sec over a whole year.
If you just want unique records, put the primary key over id,added and
let the id_seq cycle. If you want more than 2 billion unique id values
you'll need to combine the sequence with another value - see my
postgresql notes at techdocs.postgresql.org and perhaps look into the
plpgsql cookbook (www.brasileiro.net from memory)
- Richard Huxton
