Sequence generation is/needs to be transaction independent. The idea is that it always maintains a unique, growing value: they are in sequence but it is not guaranteed that they are contiguous.
Imagine a scenario where you open a transaction, enter 1000 records, at the same time another database user inserts 100 records without any transaction. The ids generated within and outside the transaction cannot be equal, they must be unique. It would be impossible for the database to resolve this issue without locking the insertions in other transactions and this would be disastrous for any type of database: it would detonate with performance.
If you try to implement this sequence on your own, take into account this same problem of parallel access, as a naive implementation will seem to work for both serial and single access scenarios, but will tragically fail for parallel access scenarios, which are the most common scenarios of using a database.
The sequence is also determined before the referential integrity is applied, because the complete data of the row being inserted must be available to perform this verification, and the Primary key of the same is required. For the same reasons of transaction independence, it is not possible to stop the other insertion routines until a referential integrity failure is determined (in this case neither is referential integrity, but rather an index failure with unique restriction). Remember what indexes and "constraints" are not intrinsic data of a table, and can be removed and added without changing the structure. For the sake of performance, scaling and simplicity of implementation the value of Identities are generated before actually inserting the line, and this occurs before the validation of the line, therefore it is not possible to "return" the sequential value a posteriori. For this reason, after the error, either Identity will be "skipped".
You don’t have to worry about this in 99.999999% of scenarios. Requiring them to be contiguous is, as a rule, unnecessary. Nor is it good practice to rely on the generation logic of the Identities of a database, as it can change with the version of the database. Use the Identities as only the unique registry identifier they are. They can also be used to sort the data in order of insertion in the database (remember that this order can be mixed between various inserts and parallel transactions).
If you are worried about the waste of bounced Identities you can stop worrying, it will never (or virtually never) come to harm you.
I answered your question about why this occurs, but it would be interesting for you to explain whether your scenario requires, in fact, that Identities are not only sequential, but also contiguous, so that I can comment with alternatives in the answer.
– Loudenvier
My scenario is that this is a table of a database in which it is implemented by a method of software of which I am building in c# winforms, the column corresponding to the Code is the primary key. At first I worried about this factor only by aesthetics of registering records in gridView but then I kept thinking that it could be a problem if these absurd jumps from one value to the other could decrease the performance of the software since some table like this for example the ID is a smallserial
– lima_t
To not worry about this field, increase it to the type of highest possible serial value. In all my databases, as I cannot predict the size of data before the launch of the system, I usually use
bigserial. This will not decrease the performance of the software, it will only use a few extra bytes per record and in the index. This impact is totally negligible nowadays. Remember: disk space (and cloud space) is cheap, programming and maintenance time is much more expensive :-)– Loudenvier
I think a good stopgap solution to your problem would be to perform the duplicity check before entering into the database. This is interesting anyway as you can add more user-friendly error messages than database restriction errors. However, remember that if there are parallel accesses, it is not guaranteed that by having tested the insertion immediately before sending the "Insert" there was no other Insert in the middle of the path precisely with the duplicate value! But already greatly decreases the chance of id jumps.
– Loudenvier