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

On Thu, 2006-08-17 at 16:07 -0400, Merlin Moncure wrote:
> On 8/17/06, Brad Nicholson <bnichols(at)ca(dot)afilias(dot)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.

This effect will be magnified if there other long running transactions
(pg_dump and pre 8.2 vacuum, I'm looking at you), as the dead tuples
from the updates will start to pile up, and reads to the table slow
down, locks persist for longer...