Re: guaranteeing that a sequence never skips

>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?
>

If id is defined unique it should be ok but if two of those statments
happen
to run concurrently you could get duplicate key violations and be
prepared
to retry the transaction. Savepoints in 8.0 will probably come on handy.

Of course concurrency is determined by the length of time you
insert a new value and commit. So it would help if you had all
other values/statements ready/done by the time you
insert into this table, and can do commit straight after
this statement.

Andre