Alexis Palma Espinosa wrote:
Hello everyone:
We are working with serials fields and we found a problem with then:
When we insert in a table that has e unique restrict, and this makes
insert fails, the sequence increments anyway...¿What we can do about
it?
Nothing. The whole point of sequences is that they don't lock. They *do*
guarantee unique numbers, but they *do not* guarantee no gaps.
If you really want a series of ID numbers with no gaps you'll want to do
something like:
1. Begin transaction
2. Lock table exclusively
3. Find highest existing ID (...ORDER BY id DESC LIMIT 1)
4. Add one to it
5. Store new row.
6. commit transaction, freeing the lock
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
