Re: [GENERAL] using ID as a key

On Mon, Feb 07, 2000 at 09:58:57AM -0600, Ed Loehr wrote:

<snip mention of coordination vi DB server>

> These are resolvable problems. One way to do a programmatic ID
> generation as David B. suggests is to have a DB server whose purpose
> is to issue "seed numbers" which designate, through use of a
> pre-determined multiplier, a *range* of allocated IDs guaranteed to be
> unique across all seed requesters. This avoids both of the problems
> raised above (throughput and synchronization).
>

<snip detailed explanation>

Hmm, Ed, that sounds like a very nice description of the pgsql sequence
object. See:

http://www.postgresql.org/docs/user/sql-createsequence.htm

In particular, the 'cache' option sets the number of sequence numbers
allocated to a particular backend for any call to 'nextval' on that
sequence, exactly as you described. The only difference I see is your
description wasn't clear about how your serial allocations interact
with transactions. Sounds like you want the 'cache' to be server specific
(a1-a4) and span transactions. The postgresql solution has been to make
the cache of numbers connection specific, so any that don't get used
for a particular connection get 'wasted' when that connection closes.

Also, cache size is a property of the sequence object, rather than the
connection or individual call, so even if your program knows it's going
to need, say 1000 sequence values at a crack, but another backend is
only going to need one at a time, and rarely, there's no way to optimize
the cache size for both backends. Might be an intersting extension to
the sequence object: a call that increases the cache size for this
connection only: that way, if your doing a bulk load, for example,
you can grab numbers in large blocks, and just fire away.

>
> This removes the bottleneck on serial generation at the expense of
> (infrequent) dependencies on the seed server S, and the serials are
> not time-ordered. A few more details must be handled, and there is
> some maintainance overhead, but it seems to work fairly well.
>

Yes, it does. ;-)

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005