Re: [GENERAL] using ID as a key

From: "Ross J(dot) Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] using ID as a key
Date: 2000-02-07 16:36:22
Message-ID: 20000207103622.B24062@rice.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message kaiq 2000-02-07 17:03:20 Re: [GENERAL] using ID as a key(REPOST)
Previous Message Frank R Callaghan 2000-02-07 16:35:15 formatting dates?