Re: Column with recycled sequence value

On Thu, 2005-01-13 at 15:43, Andrew Sullivan wrote:
> On Thu, Jan 13, 2005 at 03:31:54PM -0600, Scott Marlowe wrote:
> > Any method that tries to reuse sequence numbers is a bad idea (TM) and
>
> Why? I can think of a dozen cases where it can be useful. It just
> depends on the application.

The usual, if it's a PK of a FK relationship it's possible to have an
orphaned set at some point pointing back to it, race conditions on
trying to find a reusable sequence number, and the poor performance
needed to lock it to reuse it.

What cases are you thinking of? I've seen some very limited ones, like
using a short range for managing a queue, but that avoids a lot of the
performance issues since it's a small set you're trundling through to
find the next one available. But they're not that common, and most of
the time someone is thinking of doing so it's because a boss who doesn't
get it wants a pretty list with no holes in the sequence or something
equally silly.