On Fri, Aug 25, 2000 at 11:49:32AM +0300, Cedar Cox wrote:
>
>
> One detail that seems to have an advantage over the MAX+1 method is that
> in the case of adding a new record, deleting it, then adding another new
> record the ID will not be reused (correct?).
>
> Question: What happens when transactions enter the picture? Will you get
> duplicate values, or holes, or does it work just fine?
Well, in my opinion it works just fine, but that does mean you get holes.
This has been discussed do death over on GENERAL a number of times. In
order to avoid locking on the serial seequence generator, multiple
transacations each get to increment the counter, but do not roll it
back on transaction failure. And each connection maintains it's own
state of the underlying sequence, so that currval('table_field_seq')
is multiuser safe.
And as you mention above, not reusing numbers also leads to holes, so
your app needs to be able to handle that, anyway.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005