Re: guaranteeing that a sequence never skips (fwd)

> 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.
--
Mike Nolan