Supabase "duplicate key value violates unique constraint", explicado

Alguém preenche o formulário da sua lista de espera, espera e aperta o botão de novo. O primeiro clique já tinha passado. O segundo volta como erro, e o console do navegador mostra tudo:

{
  "code": "23505",
  "details": "Key (email)=(maria@example.com) already exists.",
  "hint": null,
  "message": "duplicate key value violates unique constraint \"waitlist_email_key\""
}

Supabase "duplicate key value violates unique constraint" significa que o Postgres se recusou a adicionar a linha porque outra linha já tem o mesmo valor em uma coluna que aceita cada valor uma vez só. Aqui a coluna é email, e a Maria já está na sua lista.

O conselho que circula para esse erro é trocar o insert por um upsert. Às vezes isso está certo. Numa tabela em que seus visitantes só podem adicionar, porém, um upsert pelo navegador precisa de regras que deixem eles lerem a tabela, e a linha com o endereço da Maria é algo que seu app nunca deveria mostrar a um visitante.

O que significa "duplicate key value violates unique constraint"?

Significa que o valor que você queria salvar já está em uma coluna que aceita cada valor uma vez, então o Postgres recusou a linha inteira. Nada foi gravado, e a linha que chegou primeiro continua igual.

Pense na tabela como a lista de convidados de uma porta, uma linha por nome. Uma restrição única é a regra do porteiro: um nome entra na lista uma vez só. Seu app chegou com um nome que já estava escrito, e ele barrou.

Cada parte do erro diz uma coisa:

O texto é do próprio Postgres. O código que dispara o erro só acrescenta a linha Key ... already exists quando tem uma descrição da chave para dar, e uma seção mais abaixo mostra quando ele não tem.

Qual coluna causou o erro?

O nome entre aspas diz, porque o Postgres monta o nome a partir da tabela e das colunas, a não ser que alguém tenha escolhido outro. O código de nomes dele dá três formatos:

A mensagem diz "unique constraint" nos três casos. Para ver exatamente o que um nome cobre, rode isto no SQL Editor com o nome da sua mensagem:

select tablename, indexdef
from pg_indexes
where indexname = 'waitlist_email_key';

O indexdef volta como algo assim: CREATE UNIQUE INDEX waitlist_email_key ON public.waitlist USING btree (email), e as colunas entre parênteses são as que não podem se repetir. Uma regra em duas colunas, como (team_id, email), deixa o mesmo endereço entrar em dois times e o recusa na segunda vez no mesmo time.

"Key (id)=(1) already exists" quando você nunca enviou um id

Esse significa que o contador de id da tabela está atrás das linhas que ela já tem, então ele entregou um número ocupado. Seu app não enviou id nenhum. O Postgres pegou o próximo número do contador, e já existia uma linha com esse número.

Na lista de convidados, o porteiro também distribui pulseiras numeradas de um rolo. Alguém copiou à mão os duzentos primeiros convidados, números das pulseiras incluídos, e ninguém girou o rolo. Ele ainda começa no 1.

Uma coluna que se numera sozinha pega cada número de uma sequência, o nome que o Postgres dá a esse contador. A página do Postgres sobre colunas identity diz que uma coluna assim "does not guarantee uniqueness", e cita este caso entre os motivos: valores que "could be inserted manually into the identity column". Linhas chegam com o número já preenchido de uma importação de CSV que inclui a coluna id, de uma cópia de outro projeto ou de um script de seed. O contador não fica sabendo de nenhuma. A página de solução de problemas do Supabase sobre sequências de id trata exatamente essa mensagem em um _pkey, e a correção dela também é um setval.

A correção é uma instrução só. Troque orders e id pela sua tabela e pela sua coluna; a tabela é a parte do nome antes de _pkey:

select setval(
  pg_get_serial_sequence('public.orders', 'id'),
  (select max(id) from public.orders)
);

O pg_get_serial_sequence acha o contador por trás da coluna, e a documentação do Postgres diz que ele funciona para os dois tipos: "If the column is an identity column, the associated sequence is the sequence internally created for that column." Depois, o setval com dois argumentos ajusta o contador para que "the next nextval will advance the sequence before returning a value". A próxima linha nova recebe o maior id mais um. Se o Postgres responder que a relação não existe, a tabela se escreve de outro jeito, tem uma letra maiúscula ou está em outro schema, e esse erro tem um post só dele.

Uma restauração a partir da saída do pg_dump não traz você até aqui. A documentação dele lista o que um dump de dados leva: "Table data, large objects, and sequence values are dumped." Cada contador volta com as linhas dele.

As três formas de lidar com um duplicado

Decida o que deve acontecer quando o valor já está lá: recusar a linha nova e dizer isso, deixar que ela substitua a antiga ou parar de enviá-la.

Recuse, com as suas palavras. Serve quando uma segunda linha seria um erro: um endereço já cadastrado, um nome de usuário já em uso. O insert falha, seu app lê o código e a pessoa vê uma frase escrita por você:

const { error } = await supabase.from('profiles').insert({ id: user.id, username })

if (error?.code === '23505') {
  setMessage('Esse nome de usuário já está em uso. Tente outro.')
} else if (error) {
  setMessage('Algo deu errado. Tente de novo.')
}

Deixe substituir a antiga. Serve quando a versão mais nova deve ganhar: uma linha de configurações por usuário, um carrinho por sessão. Isso é um upsert, e a próxima seção trata de quando ele dá errado.

Pare de enviar. Às vezes a segunda linha nunca deveria ter existido, e ela vem de um destes lugares:

• um botão que envia o formulário de novo quando é apertado de novo. Desative o botão até a primeira requisição responder.
• uma nova tentativa depois de uma resposta lenta, quando a primeira tinha passado
• dois escritores para a mesma linha, como um trigger que cria o perfil de cada usuário novo e código do app que insere o mesmo perfil depois do cadastro

Vale reconhecer o caso do trigger de cara. Um trigger que copia usuários novos para uma tabela sua falha com esse erro quando o valor copiado já está lá, e o Supabase informa o cadastro inteiro como "Database error saving new user". O 23505 em si fica nos logs do Postgres, e esse post mostra onde.

Você deve usar upsert?

Use quando a linha nova deve substituir a antiga, e confira três coisas antes de contar com ele.

O .upsert() do supabase-js é um insert que faz outra coisa quando a linha existe. O código-fonte dele diz isso em duas linhas: "If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows." Essa é a opção ignoreDuplicates, e o Postgres roda as duas versões como um insert ... on conflict.

1. O onConflict tem que nomear a coluna do seu erro.A documentação do PostgREST diz: "By default, upsert operates based on the primary key columns". Um upsert de { email } numa tabela cuja chave primária é id procura um conflito em id, não acha porque o id é novo, insere e esbarra na regra de email, com o mesmo 23505. A referência de INSERT do Postgres diz que a ação alternativa acontece "if an arbiter constraint or index specified by conflict_target is violated". Um conflito em qualquer outra coluna recusa a linha como antes.

await supabase
  .from('settings')
  .upsert({ user_id: user.id, theme: 'dark' }, { onConflict: 'user_id' })

2. Essa coluna precisa de uma regra única própria. Sem ela, o Postgres não tem com o que comparar e responde there is no unique or exclusion constraint matching the ON CONFLICT specification, código 42P10 (a linha que dispara isso).

3. Pelo navegador, a Row Level Security lê mais regras suas. A Row Level Security é o conjunto de regras por linha que o Supabase confere em cada requisição feita com a sua chave pública. Para um upsert, a referência do Postgres sobre policies diz: "SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies." Quando a linha existe e o upsert mescla, a linha existente também é conferida contra as regras de atualização da tabela. Uma linha que falha em qualquer uma dessas conferências encerra a requisição com new row violates row-level security policy.

Então, na lista de espera, um upsert pelo navegador falha enquanto a tabela não tiver uma regra de leitura que um visitante cumpra. Uma regra de leitura que um estranho cumpre para qualquer endereço é uma regra que deixa estranhos lerem a sua lista. Na porta, reescrever uma linha quer dizer que antes mostraram a lista para você. Numa tabela assim, prefira recusar o duplicado, ou use a função da próxima seção.

É seguro mostrar esse erro aos seus usuários?

Não. Mostre uma frase sua e guarde o erro cru nos seus logs, por dois motivos: a linha de detalhe pode devolver o valor, e o erro confirma que o valor existe, seja lá o que você mostre.

A linha de detalhe repete o valor. Key (email)=(maria@example.com) é inofensiva na tela da Maria e é outra história num print postado num fórum de suporte, numa ferramenta de rastreamento de erros ou na tela de alguém que digitou o endereço de outra pessoa no seu formulário. O Postgres já protege essa linha. A função que monta a linha (BuildIndexValueDescription) confere primeiro: "if RLS is enabled for the relation. If so, return NULL to avoid leaking data." Uma requisição feita com a sua chave pública para uma tabela com Row Level Security ligada recebe "details": null.

Lida ao contrário, a linha diz algo sobre a tabela. Se o console do seu navegador mostra Key (...)=(...) para uma requisição que seu app fez com a chave pública, a Row Level Security está desligada nessa tabela. A única exceção é uma requisição que seu app envia por código de servidor com a chave secreta, que pula todas as regras. O null prova menos: o Postgres também tira a linha quando quem chamou não pode ler todas as colunas da chave. Ligar a Row Level Security em toda tabela mostra a chave e a primeira regra.

Uma tabela com as regras desligadas responde a um estranho com a mesma facilidade com que respondeu a você. Nosso scan gratuito lê seu app no ar com a mesma chave pública que um visitante recebe, informa quais tabelas devolvem linhas e não pede conta.

O erro responde a uma pergunta que talvez você não queira responder. Mesmo sem a linha de detalhe, um 409 conta a quem enviou a linha que o valor já está lá. A página do Postgres sobre segurança de linhas diz que restrições únicas "always bypass row security to ensure that data integrity is maintained", e pede que você desenhe tabelas e regras para que essas verificações não virem um canal escondido. Na lista de espera, qualquer pessoa descobre se um endereço está na sua lista tentando adicioná-lo. O porteiro nunca lê o nome em voz alta, e quando ele barra alguém, a fila fica sabendo mesmo assim que o nome estava lá.

Se isso importa para a sua lista, dê ao navegador a mesma resposta nos dois casos. Uma função no banco pode adicionar o endereço, pular se ele já estiver lá e não devolver nada em nenhum dos dois casos:

create function public.join_waitlist(new_email text)
returns void
language sql
security definer
set search_path = ''
as $$
  insert into public.waitlist (email)
  values (new_email)
  on conflict (email) do nothing;
$$;

Seu app chama supabase.rpc('join_waitlist', { new_email: email }). O security definer executa o insert como o dono da função, então os visitantes não precisam de regra nenhuma na tabela, e a regra que deixava eles inserirem pode sair, senão o caminho antigo continua aberto ao lado do novo. O que "Database error saving new user" diz sobre security definer explica o que ele entrega, e chamar uma função do Supabase mostra como casar os nomes dos argumentos.

Antes de mudar como seu app grava

Guarde uma cópia do banco de antes de trocar um insert por upsert ou de acertar um contador, e fique sabendo quando uma tabela começar a responder a estranhos.

Reeve Care guarda uma cópia do seu banco no Supabase:

• uma cópia criptografada num cronograma, no armazenamento próprio da Reeve na UE, fora da sua conta do Supabase
• cada tabela com as linhas como estavam antes de uma mesclagem gravar por cima
• o contador de id de cada tabela volta com as linhas dela numa restauração, para que a próxima linha nova receba um número livre
• cada cópia verificada antes de valer, contando as linhas de cada tabela
• seus arquivos enviados também, assim que você conecta uma credencial do Storage
• uma restauração com um clique, com uma cópia de segurança do estado atual feita antes
• tudo o que o Monitor faz

Reeve Monitor continua conferindo de fora:

• se seu app está no ar, conferido a cada minuto
• a verificação de segurança completa a cada hora, em até três apps
• uma mensagem quando um resultado muda, para que uma tabela que começa a responder a estranhos depois de um deploy chegue até você sem você ter que ir olhar
• um relatório mensal, em linguagem simples, do que ele viu

O que fazer agora

Comece pelo nome entre aspas, porque ele diz qual regra recusou a linha e qual coluna essa regra protege. Antes de rodar um setval, um upsert ou qualquer outra coisa que reescreva linhas das quais as pessoas dependem, guarde uma cópia do banco de antes da mudança.