Supabase "duplicate key value violates unique constraint", spiegato

Qualcuno compila il modulo della tua lista d'attesa, aspetta e preme di nuovo il pulsante. Il primo clic era già passato. Il secondo torna come errore, e la console del browser lo mostra tutto:

{
  "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 che Postgres si è rifiutato di aggiungere la riga perché un'altra riga ha già lo stesso valore in una colonna che accetta ogni valore una sola volta. Qui la colonna è email, e Maria è già nella tua lista.

Il consiglio che gira per questo errore è trasformare l'insert in un upsert. A volte è giusto. Su una tabella dove i tuoi visitatori possono solo aggiungere, però, un upsert dal browser ha bisogno di regole che li lascino leggere la tabella, e la riga con l'indirizzo di Maria è qualcosa che la tua app non dovrebbe mai mostrare a un visitatore.

Che cosa significa "duplicate key value violates unique constraint"?

Significa che il valore che volevi salvare è già in una colonna che accetta ogni valore una volta, quindi Postgres ha rifiutato tutta la riga. Non è stato scritto niente, e la riga arrivata per prima è rimasta com'era.

Pensa alla tabella come alla lista degli invitati a una porta, una riga per nome. Un vincolo unique è la regola del buttafuori: un nome entra nella lista una volta sola. La tua app si è presentata con un nome già scritto, e lui l'ha respinta.

Ogni parte dell'errore dice una cosa:

Il testo è di Postgres stesso. Il codice che solleva l'errore aggiunge la riga Key ... already exists solo quando ha una descrizione della chiave da dare, e una sezione più sotto spiega quando non ce l'ha.

Quale colonna ha causato l'errore?

Te lo dice il nome tra virgolette, perché Postgres lo costruisce dalla tabella e dalle colonne, a meno che qualcuno non ne abbia scelto un altro. Il suo codice per i nomi dà tre forme:

Il messaggio dice "unique constraint" in tutti e tre i casi. Per vedere esattamente che cosa copre un nome, esegui questo nel SQL Editor con il nome del tuo messaggio:

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

indexdef torna come qualcosa tipo CREATE UNIQUE INDEX waitlist_email_key ON public.waitlist USING btree (email), e le colonne tra parentesi sono quelle che non possono ripetersi. Una regola su due colonne, come (team_id, email), lascia che lo stesso indirizzo entri in due team e lo rifiuta la seconda volta nello stesso.

"Key (id)=(1) already exists" quando non hai mai mandato un id

Questo significa che il contatore degli id della tabella è indietro rispetto alle righe che contiene già, quindi ha dato un numero occupato. La tua app non ha mandato nessun id. Postgres ha preso il numero successivo dal contatore, e una riga con quel numero c'era già.

Sulla lista degli invitati, il buttafuori distribuisce anche braccialetti numerati da un rotolo. Qualcuno ha ricopiato a mano i primi duecento invitati, numeri dei braccialetti compresi, e nessuno ha fatto avanzare il rotolo. Riparte ancora da 1.

Una colonna che si numera da sola prende ogni numero da una sequenza, il nome che Postgres dà a quel contatore. La pagina di Postgres sulle colonne identity dice che una colonna così "does not guarantee uniqueness", e indica questo caso tra i motivi: valori che "could be inserted manually into the identity column". Arrivano righe con il numero già compilato da un'importazione CSV che include la colonna id, da una copia di un altro progetto o da uno script di seed. Il contatore non viene a saperlo per nessuna. La pagina di risoluzione dei problemi di Supabase sulle sequenze degli id tratta proprio questo messaggio su un _pkey, e anche la sua correzione è un setval.

La correzione è un'istruzione sola. Metti la tua tabella e la tua colonna al posto di orders e id; la tabella è la parte del nome prima di _pkey:

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

pg_get_serial_sequence trova il contatore dietro la colonna, e la documentazione di Postgres dice che funziona per entrambi i tipi: "If the column is an identity column, the associated sequence is the sequence internally created for that column." Poi setval con due argomenti imposta il contatore in modo che "the next nextval will advance the sequence before returning a value". La prossima riga nuova riceve l'id più alto più uno. Se Postgres risponde che la relazione non esiste, la tabella si scrive in un altro modo, ha una maiuscola o sta in un altro schema, e quell'errore ha un articolo tutto suo.

Un ripristino dall'output di pg_dump non ti porta qui. La sua documentazione elenca che cosa contiene un dump dei dati: "Table data, large objects, and sequence values are dumped." Ogni contatore torna con le sue righe.

I tre modi di gestire un duplicato

Decidi che cosa deve succedere quando il valore c'è già: rifiutare la riga nuova e dirlo, lasciare che sostituisca quella vecchia, o smettere di mandarla.

Rifiutala, con parole tue. Va bene quando una seconda riga sarebbe un errore: un indirizzo già registrato, un nome utente già preso. L'insert fallisce, la tua app legge il codice, e la persona vede una frase scritta da te:

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

if (error?.code === '23505') {
  setMessage('Questo nome utente è già preso. Provane un altro.')
} else if (error) {
  setMessage('Qualcosa è andato storto. Riprova.')
}

Lascia che sostituisca quella vecchia. Va bene quando deve vincere la versione più recente: una riga di impostazioni per utente, un carrello per sessione. È un upsert, e la prossima sezione dice quando va storto.

Smetti di mandarla. A volte la seconda riga non doveva esistere, e arriva da una di queste fonti:

• un pulsante che rimanda il modulo quando lo si preme di nuovo. Disattivalo finché la prima richiesta non risponde.
• un nuovo tentativo dopo una risposta lenta, quando il primo era passato
• due autori per una stessa riga, come un trigger che crea il profilo di ogni nuovo utente e codice dell'app che inserisce lo stesso profilo dopo la registrazione

Il caso del trigger conviene riconoscerlo al volo. Un trigger che copia i nuovi utenti in una tua tabella fallisce con questo errore quando il valore che copia c'è già, e Supabase segnala tutta la registrazione come "Database error saving new user". Il 23505 vero e proprio è nei log di Postgres, e quell'articolo mostra dove.

Dovresti usare upsert?

Usalo quando la riga nuova deve sostituire quella vecchia, e controlla tre cose prima di farci affidamento.

.upsert() in supabase-js è un insert che fa un'altra cosa quando la riga esiste. Il suo codice sorgente lo dice in due righe: "If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows." È l'opzione ignoreDuplicates, e Postgres esegue entrambe le versioni come un insert ... on conflict.

1. onConflict deve nominare la colonna del tuo errore.La documentazione di PostgREST dice: "By default, upsert operates based on the primary key columns". Un upsert di { email } in una tabella con chiave primaria id cerca un conflitto su id, non lo trova perché l'id è nuovo, inserisce e va a sbattere contro la regola su email, con lo stesso 23505. La documentazione di INSERT di Postgres dice che l'azione alternativa scatta "if an arbiter constraint or index specified by conflict_target is violated". Un conflitto su qualsiasi altra colonna rifiuta la riga come prima.

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

2. Quella colonna ha bisogno di una regola unique tutta sua. Senza, Postgres non ha niente con cui confrontare e risponde there is no unique or exclusion constraint matching the ON CONFLICT specification, codice 42P10 (la riga che lo solleva).

3. Dal browser, la Row Level Security legge più regole tue. La Row Level Security è l'insieme delle regole per riga che Supabase controlla a ogni richiesta fatta con la tua chiave pubblica. Per un upsert, la documentazione di Postgres sulle policy dice: "SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies." Quando la riga esiste e l'upsert unisce, la riga esistente viene controllata anche contro le regole di aggiornamento della tabella. Una riga che non passa uno dei due controlli chiude la richiesta con new row violates row-level security policy.

Sulla lista d'attesa, quindi, un upsert dal browser fallisce finché la tabella non ha una regola di lettura che un visitatore rispetta. Una regola di lettura che uno sconosciuto rispetta per qualsiasi indirizzo è una regola che lascia leggere la tua lista agli sconosciuti. Alla porta, riscrivere una riga vuol dire che prima ti hanno mostrato la lista. Per una tabella così, meglio rifiutare il duplicato, o usare la funzione della prossima sezione.

È sicuro mostrare questo errore ai tuoi utenti?

No. Mostra una frase tua e tieni l'errore grezzo nei tuoi log, per due motivi: la riga di dettaglio può restituire il valore, e l'errore conferma che il valore esiste qualunque cosa tu mostri.

La riga di dettaglio ripete il valore. Key (email)=(maria@example.com) è innocua sullo schermo di Maria, e tutt'altra cosa in uno screenshot pubblicato su un forum di assistenza, in uno strumento di tracciamento degli errori o sullo schermo di chi ha scritto l'indirizzo di un'altra persona nel tuo modulo. Postgres la protegge già. La funzione che costruisce la riga (BuildIndexValueDescription) controlla per prima cosa: "if RLS is enabled for the relation. If so, return NULL to avoid leaking data." Una richiesta fatta con la tua chiave pubblica a una tabella con la Row Level Security attiva riceve "details": null.

Letta al contrario, la riga ti dice qualcosa sulla tabella. Se la console del tuo browser mostra Key (...)=(...) per una richiesta che la tua app ha fatto con la chiave pubblica, la Row Level Security è spenta su quella tabella. L'unica eccezione è una richiesta che la tua app manda tramite codice server con la chiave segreta, che salta del tutto le regole. null dimostra meno: Postgres toglie la riga anche quando chi chiama non può leggere ogni colonna della chiave. Attivare la Row Level Security su ogni tabella spiega l'interruttore e la prima regola.

Una tabella con le regole spente risponde a uno sconosciuto con la stessa facilità con cui ha risposto a te. La nostra scansione gratuita legge la tua app online con la stessa chiave pubblica che riceve un visitatore, segnala quali tabelle restituiscono righe e non richiede un account.

L'errore risponde a una domanda a cui forse non vuoi rispondere. Anche senza la riga di dettaglio, un 409 dice a chi ha mandato la riga che il valore c'è già. La pagina di Postgres sulla sicurezza delle righe dice che i vincoli unique "always bypass row security to ensure that data integrity is maintained", e ti chiede di progettare tabelle e regole in modo che questi controlli non diventino un canale nascosto. Sulla lista d'attesa, chiunque può scoprire se un indirizzo è nella tua lista provando ad aggiungerlo. Il buttafuori non legge mai il nome ad alta voce, e quando respinge qualcuno, la fila sa comunque che il nome c'era.

Se per la tua lista conta, dai al browser la stessa risposta in entrambi i casi. Una funzione nel database può aggiungere l'indirizzo, saltarlo se c'è già e non restituire niente in nessuno dei due casi:

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;
$$;

La tua app chiama supabase.rpc('join_waitlist', { new_email: email }). security definer esegue l'insert come proprietario della funzione, quindi i visitatori non hanno bisogno di nessuna regola sulla tabella, e la regola che li lasciava inserire può sparire, altrimenti la vecchia strada resta aperta accanto alla nuova. Quello che "Database error saving new user" dice di security definer spiega che cosa cede, e chiamare una funzione Supabase mostra come far combaciare i nomi degli argomenti.

Prima di cambiare il modo in cui la tua app scrive

Tieni una copia del database di prima di trasformare un insert in upsert o di rimettere a posto un contatore, e vieni a saperlo quando una tabella comincia a rispondere agli sconosciuti.

Reeve Care conserva una copia del tuo database Supabase:

• una copia cifrata secondo un calendario, sullo spazio di archiviazione di Reeve nell'UE, fuori dal tuo account Supabase
• ogni tabella con le sue righe com'erano prima che un'unione le sovrascrivesse
• il contatore degli id di ogni tabella torna con le sue righe al ripristino, così la prossima riga nuova riceve un numero libero
• ogni copia verificata prima di contare, contando le righe di ogni tabella
• anche i tuoi file caricati, appena colleghi una credenziale di Storage
• un ripristino con un clic, con una copia di sicurezza dello stato attuale presa prima
• tutto quello che fa Monitor

Reeve Monitor continua a controllare da fuori:

• se la tua app è raggiungibile, controllato ogni minuto
• il controllo di sicurezza completo ogni ora, su un massimo di tre app
• un messaggio quando un risultato cambia, così una tabella che dopo un deploy comincia a rispondere agli sconosciuti ti raggiunge senza che tu debba andare a guardare
• un report mensile, in linguaggio semplice, di quello che ha visto

Che cosa fare adesso

Comincia dal nome tra virgolette, perché dice quale regola ha rifiutato la riga e quale colonna sorveglia. Prima di lanciare un setval, un upsert o qualsiasi altra cosa che riscriva righe da cui dipendono delle persone, tieni una copia del database di prima della modifica.