Re: [HACKERS] Sequences....

"D'Arcy" "J.M." Cain <darcy(at)druid(dot)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 cycles would 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 existed and
were deleted. If we re-use those serial values, we may do fatal
damage to the application's logic, since we 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