"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
>> I think better would be that the sequence value is silently forced to
>> be at least as large as the inserted number, whenever a specific number
>> is inserted into a SERIAL field. That would ensure we never generate
>> duplicates, but not require keeping any extra state.
> Hmm. Are you suggesting that if I insert a number higher than the next
> sequence that the intervening numbers are never available?
Right. Seems to me that the cost of keeping track of "holes" in the
assignment sequence would vastly exceed the value of not wasting any
sequence numbers. (Unless you have some brilliant idea for how to do
it with a minimal amount of storage?)
Also, the major real use for loading specific values into a SERIAL
column is for a database dump and reload, where you need to be able
to preserve the original serial number assignments. In this situation,
trying to keep track of "holes" would be counterproductive for two reasons:
1. During the incoming COPY we'd very likely not see the tuples in their original order of creation; so a lot of
cycleswould be wasted keeping track of apparent holes that would get filled in shortly later.
2. After we're done loading, any remaining gaps in the usage of serial numbers very likely reflect tuples that once
existedand were deleted. If we re-use those serial values, we may do fatal damage to the application's logic,
sincewe have then violated the fundamental guarantee of a SERIAL column: never generate any duplicate serial
numbers.
You could get around problem #2 if the extra state needed to keep track
of holes could itself be saved and reloaded by pg_dump. But this is
getting way past the point of being an attractive alternative, and the
implementation no longer looks very much like a SEQUENCE object...
regards, tom lane