Re: guaranteeing that a sequence never skips (fwd)

On Sun, 2004-10-03 at 11:48, Mike Nolan wrote:
> > On Sun, 2004-10-03 at 08:58, David Garamond wrote:
> > > Am I correct to assume that SERIAL does not guarantee that a sequence
> > > won't skip (e.g. one successful INSERT gets 32 and the next might be 34)?
> > >
> > > Sometimes a business requirement is that a serial sequence never skips,
> > > e.g. when generating invoice/ticket/formal letter numbers. Would an
> > > INSERT INTO t (id, ...) VALUES (SELECT MAX(col)+1 FROM t, ...) suffice,
> > > or must I install a trigger too to do additional checking?
> >
> > You will have to lock the whole table and your parallel performance will
> > be poor.
>
> Locking the table isn't sufficient to guarantee that a sequence value
> never skips. What if a transaction fails and has to be rolled back?
>
> I've written database systems that used pre-numbered checks, what's usually
> necessary is to postpone the check-numbering phase until the number of
> checks is finalized, so that there's not much chance of anything else
> causing a rollback.
> --

I didn't mean to use a sequence, sorry for being vague. I meant this:

lock table
select max(idfield)+1
insert new row
disconnect.