Re: Sequence question - Mailing list pgsql-general

Hi Andrew,
   I had basically started working on an idea like the second approach,
but had not been able to put the status element so clearly.  I really
like the statuses of available, pending, and granted.

There's one more twist I think I can use to optimize this: once a number
is assigned, it cannot be reassigned.  So I think I can add:

  - 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.
  - push off getting new numbers into the preallocation table with a
full-table search until convenient times

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

After that one can proceed with writing the number into my data table.
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.

Any thoughts?

This is point is definitely important
 > 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.

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.

Thanks,

Eric


Andrew Sullivan wrote:
> 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
>