Três consultas SQL que perdem linha ou rodam por linha sem dar erro (LIMIT, NOT EXISTS, função VOLATILE)

Existem três consultas SQL que funcionam, não dão erro e entregam o resultado errado ou caro: LIMIT aplicado antes do filtro, NOT EXISTS dentro de um INSERT ... SELECT e função VOLATILE no WHERE. As três apareceram num sistema em produção que mantenho, e todas passam em teste simples porque o dado de teste é pequeno. Abaixo estão elas, com o SQL que rodei no Postgres 16 e a correção de cada uma.

1. Listar com LIMIT e filtrar depois perde a cauda

A ordem de avaliação do SELECT, segundo a documentação, é: o WHERE elimina linhas, depois vem a ordenação e só no fim o LIMIT. Isso vale dentro de uma consulta. Quando você pede "os 1.000 mais recentes" numa consulta e filtra no código ou numa consulta de fora, o filtro age depois do corte.

create table pedidos(id serial primary key, cliente int, status text, criado timestamptz default now());
insert into pedidos(cliente, status)
select 1 + (g % 3), case when g % 50 = 0 then 'pago' else 'novo' end
from generate_series(1, 5000) g;

-- filtra depois de cortar
select count(*) from (
  select * from pedidos order by criado desc, id desc limit 1000
) t where status = 'pago';   -- 20

-- filtra na consulta
select count(*) from pedidos where status = 'pago';   -- 100

Na minha execução, a primeira devolveu 20 e a segunda 100. Quatro quintos dos pedidos pagos sumiram e nada avisou. Em produção, vi o mesmo desenho numa rotina que pedia à origem dos dados os últimos registros de tudo, com um limite fixo, e filtrava por tipo depois: parte dos registros de um tipo nunca aparecia. Isso também acontece quando o limite é aplicado por uma API ou pelo ORM.

Correção: coloque o filtro na própria consulta (where status = 'pago'), e se o resultado ainda for maior que o limite, pagine até o fim. A documentação avisa que LIMIT sem ORDER BY único devolve um subconjunto imprevisível, então ordene por uma coluna com desempate, como order by criado, id.

2. NOT EXISTS num INSERT SELECT duplica dentro do mesmo comando

A ideia parece segura: inserir um aviso por conversa, só se ainda não existir aviso daquela conversa.

create table eventos(id serial primary key, conversa int, texto text);
create table avisos(conversa int, texto text);
insert into eventos(conversa, texto) values (10, 'a'), (10, 'b'), (11, 'c');

insert into avisos(conversa, texto)
select conversa, texto from eventos e
where not exists (select 1 from avisos a where a.conversa = e.conversa);

select count(*) from avisos;   -- 3

O resultado foram 3 linhas, duas delas para a conversa 10. A documentação do Postgres diz que, em Read Committed, um SELECT enxerga um retrato do banco tirado quando a consulta começa. O NOT EXISTS é avaliado com esse retrato, que não inclui as linhas que o próprio comando está inserindo; essa é a minha leitura do comportamento, e foi o que o teste mostrou. Portanto a checagem só vale entre execuções, nunca dentro da mesma. Isso é o que importa quando o mesmo lote contém duas linhas da mesma chave, caso comum quando o mesmo cliente manda duas mensagens seguidas.

Correção 1: reduza o lote a uma linha por chave antes de testar.

truncate avisos;
insert into avisos(conversa, texto)
select distinct on (conversa) conversa, texto from eventos e
where not exists (select 1 from avisos a where a.conversa = e.conversa)
order by conversa, id;
-- 2 linhas; rodar de novo insere 0

O DISTINCT ON mantém a primeira linha de cada conversa, e a documentação lembra que "a primeira" só é previsível com ORDER BY, por isso o order by conversa, id.

Correção 2: deixe o banco impedir a duplicata com um índice único e ON CONFLICT DO NOTHING.

truncate avisos;
create unique index on avisos(conversa);
insert into avisos(conversa, texto)
select conversa, texto from eventos order by id
on conflict (conversa) do nothing;
-- 2 linhas: (10, 'a') e (11, 'c')

Essa segunda forma também protege contra duas execuções simultâneas, que o NOT EXISTS não cobre de jeito nenhum.

3. Função VOLATILE no WHERE roda uma vez por linha

Uma função que devolve um valor e registra a data da última leitura precisa gravar no banco, então é VOLATILE. Usada direto no WHERE, ela vira um custo por linha.

create table leituras(nome text primary key, cliente int, ultima timestamptz);
insert into leituras values ('abc', 1, null);
create table lancamentos(id serial primary key, cliente int);
insert into lancamentos(cliente) select 1 + (g % 1000) from generate_series(1, 10000) g;
create index on lancamentos(cliente);
analyze lancamentos;

create function cliente_atual(p_nome text) returns int language sql volatile as
$$ update leituras set ultima = now() where nome = p_nome returning cliente $$;

explain select count(*) from lancamentos where cliente = cliente_atual('abc');

O plano saiu com Seq Scan e Filter: (cliente = cliente_atual('abc'::text)), sem uso do índice. A documentação explica o motivo: uma consulta com função volátil a reavalia em cada linha onde o valor é necessário, e uma condição de index scan avalia o valor de comparação uma vez só, por isso não é válido usar função VOLATILE ali. No meu teste, com 10.000 linhas, a consulta levou cerca de 2 segundos e fez 10.000 atualizações na tabela leituras (conferi pela estatística n_tup_upd). Em produção, a mesma armadilha multiplicava as gravações pelo número de linhas lidas.

Correção: envolva a chamada numa subconsulta escalar.

select count(*) from lancamentos
where cliente = (select cliente_atual('abc'));

Esse plano mostra InitPlan e um Bitmap Index Scan com Index Cond: (cliente = $0): a função roda uma vez e o índice volta. No mesmo teste, levou menos de 10 milissegundos e fez uma única atualização. Em plpgsql, o equivalente é guardar o valor numa variável antes da consulta. Se a função apenas lê e nunca grava, declare-a STABLE, que a documentação diz ser seguro em condição de index scan.

Como pegar as três antes de ir para produção

Compare sempre o total de uma tela ou relatório com uma contagem feita direto no banco, rode EXPLAIN em toda consulta que usa função, e teste inserções com um lote que tenha chave repetida de propósito. Resultado plausível passa em todo teste que só olha se a consulta rodou.

Se você tem totais que não batem ou telas que ficaram lentas sem motivo, é o tipo de investigação que eu faço em painéis e dados.