Re: Sequence question - Mailing list pgsql-general

On Wed, Oct 20, 2004 at 02:59:27PM -0400, Eric E wrote:
>  - have the sequence preallocation table hold only numbers with status
> being available or pending, i.e., delete numbers once they have been
> allocated.  This leaves on two possible statuses: available and pending.

I would argue that you're best to record everything.  That is, your
proposal moves from a table which covers all the possible states --
granted, pending, and available -- to a table which entails ambiguous
answers to some questions.  Since you're not going to preallocate
every logically possible id, then if an id isn't in the table, you
can't tell if it simply has never been allocated, or if it has
already been used.  If you have that stored elsewhere, though, you
can get it from a join, so perhaps there's no need for this.  (I note
that a real normalisation freak, like the one I occasionally play on
TV, would require you to use a REFERENCES constraint on the status
value, and use the referenced table as a control for what status
values you can use.  This has the not inconsiderable benefit that if
you have a new status -- say, "storage burned down" or "impounded by
SCO for copyright violation" or something else -- you have a
completely trivial way to add it.  It's certainly the way I'd
actually do this.)

> I also liked your point about the atomicity of :
> get number, change status to pending, commit

The real problem with it is that you do have the possibility of
orphaned pending actions.

> My thought was that the you could set the status ussing sessionID.  That
> way a server-side job could look for expired sessions and remark those
> numbers available.

That's something like what I'd do, yes.  It mostly depends on what's
available to your application.  I tend to be very belt-and-suspenders
about this sort of thing.  Probably I'd put a wall-clock timestamp on
the field, too, to give me clues about when things might be going
wrong, &c.

> > find a better way to solve the "other serious problems" you have
> > rather than banging on sequences to get them to fit your intended
> > use.
>
> I just haven't really seen anyway around the need to use all of our
> storage rows that doens't involve a complicated mapping to boxes.

I was more concerned that you were trying to do this for invoice
numbers, another place where people often require serial numbers.  In
that case, I usually think they're wrong, because I can think of
plenty of better ways to solve that one (unless it's a legal
requirement, which is sometimes is).  But mapping data points to
places in space is one of those cases where you probably _do_ need
this sort of preallocation mechanism.  It's what hotels do, after
all.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun