In this posting I would like to describe some important aspects of PostgreSQL sequences. In our daily work we have noticed that some people are not fully aware of those implications described in this section.
Table of Contents
Database sequences are database objects from which multiple users can generate unique numbers. Unique means that there are no duplicates – it does not mean that you cannot have gaps.
Before we dive head on into transactions we can simply create a sequence:
|
1 2 |
test=# CREATE SEQUENCE seq_a; CREATE SEQUENCE |
In the next step we can simply increment the sequence and fetch values:
|
1 2 3 4 5 |
test=# SELECT nextval('seq_a'); nextval --------- 1 (1 row) |
PostgreSQL will return one value after the other from the sequence:
|
1 2 3 4 5 |
test=# SELECT nextval('seq_a'); nextval --------- 2 (1 row) |
The most important thing you have to keep in mind when using sequences is that they provide ascending numbers, BUT those numbers are in no way uninterrupted. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# BEGIN; BEGIN test=# SELECT nextval('seq_a'); nextval --------- 3 (1 row) test=# ROLLBACK; ROLLBACK |
We start a transaction and perform a ROLLBACK. Now, most people would simply assume that the sequence will also do a rollback and continue with 3. This is not the case:
|
1 2 3 4 5 |
test=# SELECT nextval('seq_a'); nextval --------- 4 (1 row) |
Why is it that way? While a transaction is running, there is no way for the database to know whether the transaction is actually going to commit or not. Just imagine a thousand long running transactions – some of them doing a COMMIT and some of them failing. How would you ever be able to track holes and gaps? This is totally impossible. Therefore it is essential to keep in mind that sequences can indeed produce gaps.
When you are dealing with invoices and accounting, avoiding gaps is especially important. Sequences are no suitable tool to achieving what is needed in this area.
----------
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Thanks for the article, Hans-Jürgen. I am currently dealing with invoices and need to generate uninterrupted sequences of numbers. What is, in your experience, the best way to achieve this?