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

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?