| I know how to make a simple sequence, that will start at a value, and
| increment that value. What I am needing is something like this however:
| [...] I want [a gap in a sequence resulting from a deleted record]. So
| instead of a "next available" sequence, I want a "first available" [...]
Unfortunately, there's no way for a sequence to accomodate this, since a
sequence consists of a single record that basically has the following fields:
name of sequence
increment
last given value
(There's more, but not important.) In order to track gaps, you'd have to also
keep records for every deleted record, something that sequences were *not*
intended to do.
You can simulate this behavior, though, by having a table called deletednum,
for example; your record-deletion function could then add the number into that
table whenever you deleted a record. You'd then have to write a new function
you'd use in place of nextval() that went something like this:
select the minimum number in deletednum
if there isn't one
return the result of nextval(sequence)
else
delete the number from deletednum
return the number you just deleted
But even this won't catch all the gaps, because as far as I know, sequences
don't revert if a transaction is aborted. If you use nextval() and then abort
the transaction, the sequence has still been incremented.
Why? Sequences are more worried about providing unique values rather than
running out of space. But you have to go through a couple of billion
nextval()s to run a sequence out, so I don't think you need to worry too much.
(If that were the case, if I were you, I'd be a lot more worried about running
out of oids.)
---Ken
