Alban Hertroys wrote:
The problem the OP is pointing out seems difficult to solve. A
sequence doesn't know about existing records with a possibly higher
number than the sequence is at.
This may be worked around by keeping a list of numbers used up
beyond the current sequence value so the sequence knows what
numbers to skip, but that has problems of its own (if there are
many such numbers, or if the sequence gets created after data has
been added to the list). It gets ugly.
I would just have a trigger that sets the serial to NEW.id + 1.
Dunno if this has concurrency issues, though, and it may leave huge
gaps in the key space, and (more importantly) use up your sequence
too quickly.
I have, in fact, had situations where I wanted a serial PK, =and= I
needed to insert with external IDs sometimes - essentially a mix of
natural and surrogate keys (shudder). It turned out that the natural
keys were always positive, so I set up the sequence to range
=downward= from 0.
- John D. Burger
MITRE
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
