Sequência que dá saltos e colide: nextval, rollback e IDs que não são contínuos

A migração do marketplace antigo precisava manter os números de pedido que os clientes já conheciam. O script de importação usou COPY com a coluna id preenchida, de 200.001 a 205.000. A tabela tinha id GENERATED BY DEFAULT AS IDENTITY, que aceita valor explícito sem reclamar, e a sequência estava em 198.400. Nada falhou na importação. A sequência simplesmente não ficou sabendo que aqueles 5.000 números agora estavam ocupados.

Seg 09:00  importação de 5.000 pedidos do marketplace antigo, com os IDs originais
           IDs importados: 200.001 a 205.000   |   sequência de pedidos em 198.400
           COPY com coluna id explícita: a sequência não se move

Seg 09:00..13:10  checkout cria pedidos 198.401 ... 200.000 normalmente

Seg 13:11  nextval = 200.001 -> ERROR 23505 duplicate key value violates "pedidos_pkey"
           cada tentativa falha, mas consome um valor da sequência
           13:11..15:40  ~5.000 checkouts com erro 500 até a sequência passar de 205.000
           15:41  "voltou sozinho": ninguém corrigiu nada, os erros queimaram os valores

Ter 02:10  failover da primária para a réplica
           último pedido antes: 205.388   |   primeiro depois: 205.421 (salto de 32)

Qua        financeiro abre chamado: "faltam 33 números de pedido entre segunda e terça"

O detalhe que confundiu o plantão foi a "cura espontânea". Cada INSERT que falhava com 23505 tinha chamado nextval antes de bater na chave primária, e o valor consumido não voltava. Depois de cerca de 5.000 checkouts com erro, a sequência passou de 205.000 e os inserts voltaram a funcionar. O incidente terminou sozinho, mas custou duas horas e meia de pedidos perdidos e deixou uma bomba armada para a próxima importação.

Os 33 números que o financeiro procurava não eram pedidos apagados. Um veio de um checkout cancelado por falta de estoque depois do INSERT, que fez rollback. Os outros 32 vieram do failover de terça de madrugada. Nenhum dos dois casos é anomalia: é o contrato da sequência funcionando como foi desenhado.

Uma sequência é um contador que vive fora do controle transacional. Quando uma sessão chama nextval, o valor é reservado na hora e nenhuma outra sessão vai recebê-lo, mesmo que a transação que pediu faça rollback. Essa é a razão de a sequência escalar: mil transações concorrentes pegam mil valores diferentes sem esperar umas pelas outras. Se o valor só fosse confirmado no COMMIT, cada INSERT teria de esperar o anterior terminar, e a tabela inteira viraria uma fila única.

CREATE TABLE pedidos (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  codigo_loja text UNIQUE,
  total       numeric(12, 2) NOT NULL
);

-- Sessao A: pega o proximo valor e desiste.
BEGIN;
INSERT INTO pedidos (codigo_loja, total) VALUES ('A-1', 50) RETURNING id;  -- id = 101
ROLLBACK;

-- Sessao B: o 101 nao volta para a fila. O proximo valor e 102.
INSERT INTO pedidos (codigo_loja, total) VALUES ('A-2', 80) RETURNING id;  -- id = 102

-- ON CONFLICT tambem consome: o default e calculado antes da checagem de conflito.
INSERT INTO pedidos (codigo_loja, total) VALUES ('A-2', 80)
ON CONFLICT (codigo_loja) DO NOTHING;  -- conflito: nada inserido, mas o 103 foi consumido
INSERT INTO pedidos (codigo_loja, total) VALUES ('A-3', 30) RETURNING id;  -- id = 104

O INSERT ... ON CONFLICT DO NOTHING é a fonte de buracos mais subestimada. O default da coluna, que chama nextval, é avaliado antes de o Postgres verificar o conflito. Um upsert que conflita em 90% das execuções, como uma sincronização que reenvia os mesmos registros a cada minuto, consome a sequência dez vezes mais rápido do que a tabela cresce. Já vi uma tabela com 3 milhões de linhas e id passando de 2 bilhões por causa disso, a poucos meses de estourar o limite de integer.

• Rollback explícito ou por erro: o valor pedido dentro da transação se perde.
• INSERT que falha por constraint (unique, check, foreign key): o nextval já aconteceu.
• ON CONFLICT DO NOTHING ou DO UPDATE: consome um valor por linha tentada, não por linha inserida.
• Sequência com CACHE maior que 1: cada sessão reserva um bloco de valores, e o que sobrar quando a conexão fecha é descartado.

Gravar uma entrada de WAL a cada nextval seria caro demais. Por isso o Postgres registra no WAL a sequência já adiantada em 32 valores e só escreve de novo quando esses 32 forem usados. Na operação normal isso é invisível. Depois de um crash, a recuperação reaplica o último registro, e a sequência volta já adiantada, pulando o que não tinha sido entregue. Numa réplica acontece a mesma coisa: ela só conhece o que veio pelo WAL, então, ao ser promovida, entrega números à frente do último usado na primária antiga.

O CACHE merece atenção extra porque também quebra a ordem. Com CACHE 50, a sessão 1 pode receber 1 a 50 e a sessão 2, 51 a 100. Se a sessão 2 inserir primeiro, o id 51 aparece antes do id 1. Quem usa "ORDER BY id" como sinônimo de "ordem de criação" passa a ver uma lista fora de ordem. Mesmo sem CACHE, o id reflete a ordem do nextval, não a do COMMIT: uma transação lenta que pegou o id 1000 pode ficar visível depois do 1001. Para ordem temporal, use uma coluna de data.

A sequência só colide quando alguém escreve na coluna sem passar por ela. Com serial ou com GENERATED BY DEFAULT AS IDENTITY, um INSERT ou COPY com o id preenchido grava o valor e deixa a sequência onde estava. O erro aparece horas ou dias depois, quando nextval alcança a faixa ocupada, e por isso quase nunca é ligado à importação que o causou.

• Importação ou seed com IDs explícitos, como na migração do marketplace.
• Cópia de dados entre ambientes com COPY ou ferramentas de ETL que levam a coluna id, mas não o estado da sequência. Um pg_dump completo inclui o setval; uma cópia só de dados de algumas tabelas, não.
• Replicação lógica nativa: até o Postgres 18, ela não replica o estado das sequências. No dia do corte para o banco novo, todas as sequências estão no valor inicial e o primeiro INSERT colide.
• setval manual feito com o valor errado, por exemplo depois de restaurar uma tabela a partir de backup antigo.
• Duas aplicações gravando na mesma tabela, uma usando a sequência e outra calculando max(id) + 1.

O diagnóstico precisa olhar todas as sequências, não só a que deu erro, porque a mesma importação costuma tocar várias tabelas. A função abaixo encontra cada sequência ligada a uma coluna pelo catálogo (pg_depend com deptype a para serial e i para identity) e compara o maior valor da tabela com o próximo valor que a sequência vai entregar. Cada max(id) usa o índice da chave primária, então a consulta é rápida mesmo em tabelas grandes.

-- Todas as sequencias ligadas a uma coluna (serial = a, identity = i),
-- com o maior valor da tabela e o proximo valor que a sequencia vai entregar.
CREATE OR REPLACE FUNCTION sequence_health()
RETURNS TABLE (seq regclass, tbl regclass, col name, max_id bigint, next_value bigint)
LANGUAGE plpgsql AS $
DECLARE
  r record;
  v_last bigint;
  v_called boolean;
  v_inc bigint;
BEGIN
  FOR r IN
    SELECT s.oid::regclass AS seq_oid, t.oid::regclass AS tbl_oid, a.attname
      FROM pg_class s
      JOIN pg_depend d ON d.objid = s.oid
                      AND d.classid = 'pg_class'::regclass
                      AND d.refclassid = 'pg_class'::regclass
                      AND d.deptype IN ('a', 'i')
      JOIN pg_class t ON t.oid = d.refobjid
      JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
     WHERE s.relkind = 'S'
  LOOP
    seq := r.seq_oid;
    tbl := r.tbl_oid;
    col := r.attname;
    EXECUTE format('SELECT max(%I)::bigint FROM %s', r.attname, r.tbl_oid) INTO max_id;
    EXECUTE format('SELECT last_value, is_called FROM %s', r.seq_oid) INTO v_last, v_called;
    SELECT seqincrement INTO v_inc FROM pg_sequence WHERE seqrelid = r.seq_oid;
    next_value := CASE WHEN v_called THEN v_last + v_inc ELSE v_last END;
    RETURN NEXT;
  END LOOP;
END;
$;

SELECT seq, tbl, col, max_id, next_value,
       max_id >= next_value AS vai_colidir
  FROM sequence_health()
 ORDER BY vai_colidir DESC NULLS LAST, tbl;

Com o diagnóstico em mãos, a correção é um setval por sequência. O bloco abaixo usa a mesma função, roda primeiro em modo de leitura para listar o que seria alterado e só aplica quando a variável aplicar vira true. Como setval é imediato e também fora da transação, não há janela de lock: o próximo INSERT já recebe um valor livre.

-- Ajusta toda sequencia cujo proximo valor ja existe na tabela.
DO $
DECLARE
  aplicar constant boolean := false;  -- Troque para true para aplicar. Com false, so lista o que seria corrigido.
  r record;
BEGIN
  FOR r IN
    -- Proximo valor que nextval vai devolver (considera is_called e o incremento).
    SELECT seq, tbl, max_id, next_value
      FROM sequence_health()
     WHERE max_id IS NOT NULL AND max_id >= next_value
  LOOP
    RAISE NOTICE '% (%): max_id = %, next_value = %', r.seq, r.tbl, r.max_id, r.next_value;
    IF aplicar THEN
      -- setval(seq, max_id, true): o proximo nextval devolve max_id + incremento.
      PERFORM setval(r.seq, r.max_id, true);
    END IF;
  END LOOP;
END;
$;

1. Rode o bloco de correção como etapa final de toda importação, cópia entre ambientes ou corte de replicação lógica. Coloque isso no runbook, não na memória de quem faz a migração.
2. Prefira GENERATED ALWAYS AS IDENTITY em tabelas novas. Um INSERT com id explícito passa a falhar na hora, e quem realmente precisa gravar o valor tem de escrever OVERRIDING SYSTEM VALUE, o que torna a exceção visível no código de importação.
3. Em upserts de alto volume, verifique antes se a linha existe ou use MERGE com a condição certa, para não queimar a sequência a cada tentativa.
4. Use bigint em toda chave gerada por sequência. Buracos são normais, e uma sequência que queima valores rápido chega aos 2,1 bilhões do integer muito antes do que o número de linhas sugere.
5. Inclua sequence_health() no monitoramento diário e alerte se alguma linha tiver vai_colidir = true.

Há casos em que o buraco é problema de verdade: numeração de nota fiscal por série, número de recibo, protocolo de atendimento auditado. Nesses casos a sequência é a ferramenta errada, e a saída não é tentar "consertar" a sequência, e sim separar duas coisas que costumam ser confundidas. O id técnico continua vindo da sequência, com buracos e sem importância para ninguém. O número de negócio vem de um contador transacional, incrementado na mesma transação que grava o documento.

-- schema.sql
-- Um contador por serie. A linha e o ponto de serializacao.
CREATE TABLE numeracao_fiscal (
  serie         text PRIMARY KEY,
  ultimo_numero integer NOT NULL DEFAULT 0
);

CREATE TABLE notas_fiscais (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  serie     text NOT NULL REFERENCES numeracao_fiscal (serie),
  numero    integer NOT NULL,
  pedido_id bigint NOT NULL UNIQUE,
  valor     numeric(12, 2) NOT NULL,
  emitida_em timestamptz NOT NULL DEFAULT now(),
  UNIQUE (serie, numero)
);

INSERT INTO numeracao_fiscal (serie) VALUES ('1');

// emitir-nota.js
import pg from 'pg';

export const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

export async function emitirNota({ serie, pedidoId, valor }) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query("SET LOCAL lock_timeout = '2s'");

    // O UPDATE trava a linha da serie ate o COMMIT ou ROLLBACK.
    const { rows } = await client.query(
      `UPDATE numeracao_fiscal
          SET ultimo_numero = ultimo_numero + 1
        WHERE serie = $1
    RETURNING ultimo_numero`,
      [serie],
    );
    if (rows.length === 0) throw new Error(`serie ${serie} nao cadastrada`);
    const numero = rows[0].ultimo_numero;

    // Se o INSERT falhar, o ROLLBACK desfaz o incremento: nenhum numero se perde.
    await client.query(
      `INSERT INTO notas_fiscais (serie, numero, pedido_id, valor)
       VALUES ($1, $2, $3, $4)`,
      [serie, numero, pedidoId, valor],
    );

    // O numero so e visivel para os outros depois do COMMIT.
    await client.query('COMMIT');
    return numero;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

O preço é a serialização. Enquanto uma transação emite a nota da série 1, todas as outras que querem a mesma série esperam o COMMIT. Isso é aceitável quando a transação é curta e só faz o necessário: sem chamada HTTP, sem envio para a SEFAZ e sem geração de PDF dentro dela. Esses passos vêm depois, a partir da nota já gravada. Se o volume de uma única série ficar alto demais, a saída é dividir em séries, cada uma com sua linha de contador, e não voltar para a sequência.