Re: Enforcing serial uniqueness?

> That way they really can't touch the sequence; otherwise they still
> could call nextval multiple times erroneously (people do that...). It
> doesn't matter much to the sequence, of course... It just leaves the
> ugly gaps out :P

The sequence facility was NOT designed with no-gap sequencing in mind,
but with good parallel performance in mind.

For example you will never be able to recover a sequence value if the
transaction which got it rolls back... no matter what other gimmicks you
do. And you will always get gaps when you delete some rows, so there's
really not much point in caring at all about the gaps.

I do understand that there are applications where the operators feel
more comfortable memorizing the ids they often use and typing in those
directly instead of choosing from drop-downs... I've been there, seen
that. I could do mostly nothing against that habit, typing is always
faster than choosing from drop-downs. In those cases it makes sense to
provide some special support for making the frequently used IDs small,
or otherwise streamlining the GUI so that choosing is not often needed,
and possibly do it by aliasing so that the aliases are not easily
overlapping when mistyped... in any case, typing in IDs is always error
prone even if it is faster. In the case I dealt with, there was a huge
mess of mistakenly introduced IDs (coming from the inherited application
where the only way to navigate was through typing in IDs), and I ended
up by coding some heuristics for searching for IDs in the DB which could
be a possible mistaken version of some given ID... interesting
experience regarding what are the most common mistypings, but the only
real conclusion was to avoid such stuff in any application I get the
chance to code from scratch.

So the real answer here is: you don't need IDs without gaps, and if you
think you need them, use letter codes instead, and even those so chosen
that mistyping won't get you to another existing code.

Cheers,
Csaba.