Hi List ... I'm investigating how to port our data and applicationsfrom our in-house developed database server to PostgreSQL.
One of the challenges I'm running into is in replicating an 'autonumber' column type.. we're used to having a primary key that will autogenerate a 31bit integer that will automatically wraparound back to its starting position once it has reached MAXINT, and automatically 'skips' over any IDs that are already in use (even if in uncommited transactions)
Postgresql's SERIAL (and the underlying SEQUENCE stuff) is comparable .. it can be set up to wraparound once it hits the 31-bit INTMAX but from everything i've read it has no option to skip over any IDs that are already in use - so after the first wraparound occurs, transactions risk failing over the unique constraint on the primary key.
I've checked stackoverflow and other forums - as far as I can tell there is no 'easy' fix yet. Setting a 'red line' for the sequence and renumbering the primary key and references once you hit it seems to be the best known solution without requiring changes from downstream/api users (as extending to 64bit or using UUIDs would)
So my questions are:
- Is my description accurate - eg there is no 'standard' solution yet? (I haven't missed any SO article?)
- Has someone already attempted to work around this by fixing/providing an alternative to nextval() which would work around this?
(our own database 'solved' this by looking directly at the btree index for the primary key to find and skip any 'in use' value, even if they wouldn't be committed/visible yet - and storing the 'next' value in a global mutex protected variable. but I presume postgresql's architecture wouldn't make it that easy, or it would have already been implemented)
With regards,
Arnold
