Re: guaranteeing that a sequence never skips

A long time ago, in a galaxy far, far away, lists(at)zara(dot)6(dot)isreserved(dot)com (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)?

What is guaranteed is that sequence values will not be repeated
(assuming you don't do a setval() :-).)

If value caching is turned on, then each connection may grab them in
groups of (say) 100, so that one insert, on one (not-too-busy)
connection might add in 5399, and an insert on another connection,
that has been much busier, might add in 6522, and those values differ
quite a bit :-).

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

This is a troublesome scenario...

1. Your requirement makes it MUCH harder to deal with concurrent
updates efficiently.

That "SELECT MAX()" destroys the efficiency achieved by the use of
sequences.

2. It may be difficult to avoid deadlocks of some sort.

Suppose several inserts take place more or less simultaneously. In
that case, they might all get the same value of SELECT MAX(), and only
one of them could therefore succeed. The others would get
"clotheslined" by the UNIQUE constraint, like a hapless fugitive that
runs into a tree branch, and you'll see transactions failing due to
concurrency. Not a good thing.

Another possibiity would be to have _two_ fields, one, call it C1,
using a sequence, and the other, C2, which gets populated later.

Periodically, a process goes through and calculates CURR=SELECT
MAX(C2), and then walks through all of the records populated with
values in C1. For each non-null C1, it assigns C2 based on the value
of CURR, and then empties C1.

That means that there is a period of time during which the "ultimate"
sequence value, C2, is not not populated, which might or might not be
a problem for your application.
--
(format nil "~S(at)~S" "cbbrowne" "linuxfinances.info")
http://linuxfinances.info/info/linuxxian.html
Life's a duck, and then you sigh.