Re: Sequence question - Mailing list pgsql-general

On Tue, Oct 19, 2004 at 11:19:05AM -0400, Eric E wrote:

> My users will draw a number or numbers from the sequence and write to
> the field.  Sometimes, however, these sequence numbers will be discarded
> (after a transaction is complete), and thus available for use. During
> the transaction, however, any drawn numbers need to be unavailable.
> I would like the next user who draws a number to draw the lowest number
> she can, starting with the holes in the sequence.

There are two ways I've seen to do this.  One is the low-concurrency
way.  Another is a sort of clever approach that isn't theoretically
perfect, but which provides slightly better concurrency.

The low-concurrency approach is pretty much what you'd expect: keep
the value in a table which is locked by each transaction which is
incrementing it, and complete the incrementing in the transaction
scope.  That way, if it rolls back, the value hasn't been
incremented, and is ready for the next user.  The problem, of course,
is that this forces every transaction to stand in line.

An alternative approach I've heard is to pre-allocate numbers from a
sequence into a table:

create table seq_allocation (
    serialno int8 not null unique,
    grant_status int
        constraint status_limiter check (grant_status in
        (1,2,3)) );

The idea is that a grant_status of 1 means the serial number is
unallocated, a grant_status of 2 means it's pending, and 3 means it's
granted.  When you start, in one transaction you pick the next
available serialno with a status of 1.  Then you update that row to
set it to 2 (make sure you use "where grant_status = 1" to avoid a
race condition), and then commit.  Now you have your serial number.
Use it, and then at the end of your transaction where you are
committing, set the grant_status to 3, so you know it's really used.

Now, how do you handle the cases where either the transaction fails
so you can't set it to 3?  Simple: your client captures errors and
then sets the value back to 1 later.  For client errors, you need yet
another process which will go around periodically and check for
grant_status = 2, and make sure nobody's actually in the middle of
trying to use them.  (You could refine the seq_allocation table by
storing the pid of the allocating back end.  Then your maintenance
script could look for such a back end while cleaning up.)

The savepoints features of 8.0 will make some of this even easier for
you.

Note that this second method is not completely bulletproof, but it
might be good enough for the cases you want.  I have a feeling,
however, that you're creating a new problem for yourself by not being
able to skip sequence values.  My bet is that you actually need to
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.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to.  That actually seems sort of quaint now.
        --J.D. Baldwin