Postgresql autoincrement skips 1 value when Insert is done by a function

Question

I have the following function in Postgresql:

CREATE OR REPLACE FUNCTION ve.geraHistorico()
RETURNS VOID AS 
$$
DECLARE
    i INTEGER := 1;
    id INTEGER;
    dataCad TEXT;
    dataFec TEXT;
    qtd INTEGER := (SELECT COUNT(id) FROM ve.vendas);
BEGIN
    WHILE i <= qtd LOOP
        SELECT id, datacadastro, datafechamento INTO id, dataCad, DataFec FROM ve.vendas WHERE id = i;

        IF dataFec IS NULL THEN
            INSERT INTO ve.historico(id, data)
            VALUES(id, GENERATE_SERIES(dataCad::DATE, CURRENT_DATE::DATE, '1 day'::interval));
        ELSE
            INSERT INTO ve.historico(id, data)
            VALUES(id, GENERATE_SERIES(dataCad::DATE, dataFec::DATE, '1 day'::interval));
        END IF;
        i = i + 1;
    END LOOP;
END
$$ 
LANGUAGE plpgsql;

The table ve.historico is constituted:

CREATE TABLE ve.vendas
(
    idAI SERIAL PRIMARY KEY,
    id INTEGER,
    data DATE
);

The problem happens when I run the function ve.geraHistorico(), the auto increment should create a single and sequential id for each line inserted, but when you change the id of the sale in the function, the auto increment skips 1 value, being as follows:

The first column (gray) is the counter of the pgAdmin console, the second column is the AUTOINCREMENT, the third is the sale id and the fourth column is the dates I am generating. If you notice, you will see that in line 31 the AUTOINCREMENT should be the 31 also, more jumped by 32, well where exchange of sale.

Why does this happen? It would be some problem in the function?

Answer 1

This is completely normal.

When you create a field in a table of type SERIAL or BIGSERIAL, one SEQUENCE is created implicitly to make the auto-increment control of this field.

Fields of the type SERIAL and BIGSERIAL have a value DEFAULT which consists of the function call return nextval().

In a real scenario, several isolated transactions may be calling nextval() for the same SEQUENCE, simultaneously, one will be effective and another will be discarded.

EVEN the discarded transactions (which suffered ROLLBACK), do not decrease the SEQUENCE, causing that sequential number to be lost forever.

The gaps that you are observing, are actually, transactions that called nextval(), however, they suffered ROLLBACK at some point.

To SEQUENCE is never decremented, not even if there was a ROLLBACK in the transaction that incremented it (and doing this decrease manually is not a good idea!).

According to the documentation of PostgreSQL, in the section on Manipulação de Sequências, there is an explanation about this behavior in the function reference nextval(), look at you:

nextval

Advance the Sequence Object to its next value and Return that value. This is done atomically: Even if Multiple Sessions execute nextval concurrently, each will Safely receive a distinct Quence value.

[...]

Important

To avoid blocking Concurrent transactions that obtain Numbers from the same Quence, the nextval Operation is Never Rolled back; that is, Once a value has been fetched it is considered used and will not be returned Again. This is true Even if the surrounding transaction later aborts, or if the Calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will Compute the to-be-inserted tuple, including Doing any required nextval calls, before Detecting any Conflict that would cause it to follow the ON CONFLICT Rule Instead. Such cases will Leave unused "holes" in the Sequence of Assigned values. Thus, Postgresql Sequence Objects cannot be used to obtain "gapless" sequences.

Have you thought about reevaluating the real need to have a sequence field without gaps ?

Consider the following:

CREATE TABLE tb_historico
(
  id BIGSERIAL PRIMARY KEY,
  valor INTEGER,
  datahora TIMESTAMP DEFAULT NOW()
);


BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); ROLLBACK;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); ROLLBACK;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); COMMIT;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); ROLLBACK;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); COMMIT;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); ROLLBACK;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); COMMIT;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); ROLLBACK;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); COMMIT;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); ROLLBACK;
BEGIN; INSERT INTO tb_historico ( valor ) VALUES ( 100 ); COMMIT;

You can use the function row_number() to list your records, see only:

SELECT
    row_number() OVER (ORDER BY id) AS id_gapless,
    id,
    valor,
    datahora
FROM
    tb_historico;

Exit:

| id_gapless | id | valor |                    datahora |
|------------|----|-------|-----------------------------|
|          1 |  3 |   100 | 2018-07-04T19:07:11.651281Z |
|          2 |  5 |   100 | 2018-07-04T19:07:11.654413Z |
|          3 |  7 |   100 | 2018-07-04T19:07:11.657451Z |
|          4 |  9 |   100 | 2018-07-04T19:07:11.660584Z |
|          5 | 11 |   100 |  2018-07-04T19:07:11.66475Z |

See working on Ideone.com