Re: Best approach for a "gap-less" sequence

On Thu, 2006-08-17 at 15:13 -0500, Scott Marlowe wrote:
> On Thu, 2006-08-17 at 15:07, Merlin Moncure wrote:
> > On 8/17/06, Brad Nicholson <bnichols@ca.afilias.info> wrote:
> >
> > > > > Hmm, I think you are wrong.  There is a SELECT ... FOR UPDATE;
> > > > > The first-to-obtain the gapless sequence transaction will establish
> > > > > a lock onthe "tax_id" row.  The other transaction will block until
> > > > > the first transaction finishes (and the row is updated) and will
> > > > > establish the row lock on it.
> > > >
> > > > yes, you are right...i didnt think the problem through properly.
> > >
> > > Lets just hope the performance on a concurrent system is not a
> > > requirement of such a system...
> > >
> >
> > right, if the transations are long running, there is a big problem as
> > they are serialized around access to the sequence.  however this is
> > better than the control record approach because control record have
> > problems with mvcc bloat.  concurrent performance will of course be
> > awful.
> >
> > a good compomise in some cases is to save off canceled transactions
> > ids' in a free list   you would still have to deal with transactions
> > that were not gracefully cancelled though.
>
> Is it not possible in some circumstances to create the invoice first,
> THEN assign a sequential ID after creation?

If speed of access was an issue, that's how I'd look at doing it - batch
assign them after the fact.

Brad.