Hi guys,
I have a question regarding postgresql sequences.
For instance, for bigserial datatype, is it true that the sequence is advanced, then the number is retrieved and even if the insertion/committing is not successful, the sequence doesn't backtracks. Which means the next time I might be doing insertion to the table, that might be a gap in the sequence number.
Theres a before insert row trigger on my table and Im using psycopg2.
thanks in advance.
even if the insertion/committing is not successful, the sequence doesn't backtracks. Which means the next time I might be doing insertion to the table, that might be a gap in the sequence number.
Yes, that's true, And that's fine. One usually wants a sequence to get values in a table that are unique (typically for a PK) and gaps don't matter at all.
If you are curious: this is natural behaviour if one thinks about concurrency. Suppose a transaction T1 inserts a row, getting a PK1 from a sequence, uses that value to build another records in other tables... in the meantime (before T1 commits) another transaction T2 inserts a row in the same table. Then T1 rollbacks and T2 commits...
BTW: If you want a "gap-less" sequence ... first ask yourself if you really want that (usually you really don't - and requiring that frequently points to a conceptual problem in your design)... but if you really need it, you can read this.
Backtracking would require locking until completion. This would be bad, especially when 10 tables can all utilize the same sequence. If you want an order don't use a sequence use a window function like row_number().