Supabase "duplicate key value violates unique constraint" expliqué

Quelqu'un remplit le formulaire de votre liste d'attente, attend, et appuie de nouveau sur le bouton. Le premier clic était déjà passé. Le second revient en erreur, et la console du navigateur affiche tout :

{
  "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" signifie que Postgres a refusé d'ajouter la ligne parce qu'une autre ligne contient déjà la même valeur dans une colonne qui n'accepte chaque valeur qu'une fois. Ici la colonne est email, et Maria est déjà sur votre liste.

Le conseil qui circule pour cette erreur est de passer l'insert en upsert. C'est parfois juste. Sur une table où vos visiteurs peuvent seulement ajouter, pourtant, un upsert depuis le navigateur a besoin de règles qui les laissent lire la table, et la ligne qui contient l'adresse de Maria ne devrait jamais s'afficher devant un visiteur.

Que signifie "duplicate key value violates unique constraint" ?

La valeur que vous vouliez enregistrer se trouve déjà dans une colonne qui n'accepte chaque valeur qu'une fois, alors Postgres a refusé toute la ligne. Rien n'a été écrit, et la ligne arrivée la première n'a pas bougé.

Imaginez la table comme la liste des invités à une porte, une ligne par nom. Une contrainte unique, c'est la règle du videur : un nom n'entre sur la liste qu'une fois. Votre application s'est présentée avec un nom déjà inscrit, et il l'a renvoyée.

Chaque partie de l'erreur dit une chose :

La formulation vient de Postgres lui-même. Le code qui lève l'erreur n'ajoute la ligne Key ... already exists que s'il a une description de la clé à donner, et une section plus bas montre quand il n'en a pas.

Quelle colonne a provoqué l'erreur ?

Le nom entre guillemets vous le dit, car Postgres le construit à partir de la table et des colonnes, sauf si quelqu'un en a choisi un autre. Son code de nommage donne trois formes :

Le message dit "unique constraint" pour les trois. Pour voir exactement ce que couvre un nom, exécutez ceci dans le SQL Editor avec le nom de votre message :

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

indexdef revient sous une forme comme CREATE UNIQUE INDEX waitlist_email_key ON public.waitlist USING btree (email), et les colonnes entre parenthèses sont celles qui ne peuvent pas se répéter. Une règle sur deux colonnes, comme (team_id, email), laisse la même adresse rejoindre deux équipes et la refuse une deuxième fois dans la même.

"Key (id)=(1) already exists" alors que vous n'avez jamais envoyé d'id

Celle-ci signifie que le compteur d'id de la table est en retard sur les lignes qu'elle contient déjà, et qu'il a donc distribué un numéro pris. Votre application n'a envoyé aucun id. Postgres a pris le numéro suivant au compteur, et une ligne portant ce numéro existait déjà.

Sur la liste des invités, le videur distribue aussi des bracelets numérotés tirés d'un rouleau. Quelqu'un a recopié à la main les deux cents premiers invités, numéros de bracelet compris, et personne n'a fait avancer le rouleau. Il repart toujours de 1.

Une colonne qui se numérote toute seule prend chaque numéro dans une séquence, le nom que Postgres donne à ce compteur. La page de Postgres sur les colonnes d'identité dit qu'une telle colonne "does not guarantee uniqueness", et donne ce cas parmi les raisons : des valeurs "could be inserted manually into the identity column". Des lignes arrivent avec leur numéro déjà rempli depuis un import CSV qui contient la colonne id, une copie venue d'un autre projet ou un script de seed. Le compteur n'entend parler d'aucune. La page de dépannage de Supabase sur les séquences d'id traite exactement ce message sur un _pkey, et sa réparation est aussi un setval.

La réparation tient en une instruction. Mettez votre table et votre colonne à la place de orders et id ; la table est la partie du nom avant _pkey :

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

pg_get_serial_sequence trouve le compteur derrière la colonne, et la documentation de Postgres dit qu'elle marche pour les deux sortes : "If the column is an identity column, the associated sequence is the sequence internally created for that column." Ensuite setval avec deux arguments règle le compteur de sorte que "the next nextval will advance the sequence before returning a value". La prochaine nouvelle ligne reçoit l'id le plus haut plus un. Si Postgres répond que la relation n'existe pas, la table s'écrit autrement, porte une majuscule ou se trouve dans un autre schéma, et cette erreur a son propre article.

Une restauration depuis la sortie de pg_dump ne vous amène pas ici. Sa documentation énumère ce que contient un dump de données : "Table data, large objects, and sequence values are dumped." Chaque compteur revient avec ses lignes.

Les trois façons de traiter un doublon

Décidez ce qui doit se passer quand la valeur est déjà là : refuser la nouvelle ligne et le dire, la laisser remplacer l'ancienne, ou ne plus l'envoyer du tout.

Refusez-la, avec vos mots. Cela convient quand une deuxième ligne serait une erreur : une adresse déjà inscrite, un nom d'utilisateur déjà pris. L'insert échoue, votre application lit le code, et la personne voit une phrase que vous avez écrite :

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

if (error?.code === '23505') {
  setMessage("Ce nom d'utilisateur est pris. Essayez-en un autre.")
} else if (error) {
  setMessage('Un problème est survenu. Veuillez réessayer.')
}

Laissez-la remplacer l'ancienne. Cela convient quand la version la plus récente doit l'emporter : une ligne de réglages par utilisateur, un panier par session. C'est un upsert, et la section suivante dit quand il tourne mal.

Ne l'envoyez plus. Parfois la deuxième ligne n'aurait jamais dû exister, et elle vient de l'une de ces sources :

• un bouton qui renvoie le formulaire quand on appuie une deuxième fois. Désactivez-le jusqu'à ce que la première requête réponde.
• une nouvelle tentative après une réponse lente, alors que la première était passée
• deux auteurs pour une même ligne, comme un trigger qui crée le profil de chaque nouvel utilisateur et du code applicatif qui insère ce même profil après l'inscription

Le cas du trigger vaut d'être reconnu au premier coup d'œil. Un trigger qui copie les nouveaux utilisateurs dans une table à vous échoue avec cette erreur quand la valeur copiée y est déjà, et Supabase signale toute l'inscription par "Database error saving new user". Le 23505 lui-même se trouve dans les logs Postgres, et cet article montre où.

Faut-il utiliser upsert ?

Utilisez-le quand la nouvelle ligne doit remplacer l'ancienne, et vérifiez trois choses avant de compter dessus.

.upsert() dans supabase-js est un insert qui fait autre chose quand la ligne existe. Son code source le dit en deux lignes : "If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows." C'est l'option ignoreDuplicates, et Postgres exécute l'une et l'autre version comme un insert ... on conflict.

1. onConflict doit nommer la colonne de votre erreur.La documentation de PostgREST dit : "By default, upsert operates based on the primary key columns". Un upsert de { email } dans une table dont la clé primaire est id cherche un conflit sur id, n'en trouve pas puisque l'id est neuf, insère, et se heurte à la règle sur email avec le même 23505. La référence d'INSERT de Postgres dit que l'action de rechange s'applique "if an arbiter constraint or index specified by conflict_target is violated". Un conflit sur toute autre colonne refuse la ligne comme avant.

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

2. Cette colonne a besoin de sa propre règle unique. Sans elle, Postgres n'a rien à faire correspondre et répond there is no unique or exclusion constraint matching the ON CONFLICT specification, code 42P10 (la ligne qui la lève).

3. Depuis le navigateur, la Row Level Security lit davantage de vos règles. La Row Level Security est l'ensemble des règles par ligne que Supabase vérifie à chaque requête faite avec votre clé publique. Pour un upsert, la référence de Postgres sur les policies dit : "SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies." Quand la ligne existe et que l'upsert fusionne, la ligne existante est aussi vérifiée contre les règles de mise à jour de la table. Une ligne qui échoue à l'une de ces vérifications met fin à la requête avec new row violates row-level security policy.

Sur la liste d'attente, un upsert depuis le navigateur échoue donc tant que la table n'a pas de règle de lecture qu'un visiteur respecte. Une règle de lecture qu'un inconnu respecte pour n'importe quelle adresse est une règle qui laisse des inconnus lire votre liste. À la porte, réécrire une ligne veut dire qu'on vous a d'abord montré la liste. Pour une table comme celle-là, refusez plutôt le doublon, ou servez-vous de la fonction de la section suivante.

Peut-on montrer cette erreur à vos utilisateurs sans risque ?

Non. Montrez une phrase à vous et gardez l'erreur brute dans vos logs, pour deux raisons : la ligne de détail peut renvoyer la valeur, et l'erreur confirme que la valeur existe, quoi que vous affichiez.

La ligne de détail répète la valeur. Key (email)=(maria@example.com) est sans conséquence sur l'écran de Maria, et tout autre chose dans une capture d'écran postée sur un forum d'assistance, dans un outil de suivi d'erreurs, ou sur l'écran de quelqu'un qui a tapé l'adresse d'une autre personne dans votre formulaire. Postgres la protège déjà. La fonction qui construit la ligne (BuildIndexValueDescription) vérifie d'abord : "if RLS is enabled for the relation. If so, return NULL to avoid leaking data." Une requête faite avec votre clé publique vers une table où la Row Level Security est activée reçoit "details": null.

Lue dans l'autre sens, la ligne vous renseigne sur la table. Si la console de votre navigateur affiche Key (...)=(...) pour une requête que votre application a faite avec la clé publique, la Row Level Security est désactivée sur cette table. La seule exception est une requête que votre application envoie par du code serveur muni de la clé secrète, qui passe outre toutes les règles. null prouve moins de choses : Postgres retire aussi la ligne quand l'appelant ne peut pas lire chaque colonne de la clé. Activer la Row Level Security sur chaque table couvre l'interrupteur et la première règle.

Une table dont les règles sont désactivées répond à un inconnu aussi volontiers qu'à vous. Notre scan gratuit lit votre application en ligne avec la même clé publique qu'un visiteur, indique quelles tables rendent des lignes, et ne demande aucun compte.

L'erreur répond à une question que vous ne voulez peut-être pas voir résolue. Même sans la ligne de détail, un 409 apprend à l'expéditeur que la valeur est déjà là. La page de Postgres sur la sécurité des lignes dit que les contraintes uniques "always bypass row security to ensure that data integrity is maintained", et vous demande de concevoir tables et règles pour que ces vérifications ne deviennent pas un canal caché. Sur la liste d'attente, n'importe qui peut savoir si une adresse y figure en essayant de l'ajouter. Le videur ne lit jamais le nom à voix haute, et quand il renvoie quelqu'un, la file sait quand même que le nom était là.

Si cela compte pour votre liste, donnez au navigateur la même réponse dans les deux cas. Une fonction dans la base peut ajouter l'adresse, l'ignorer si elle est déjà là, et ne rien renvoyer dans les deux cas :

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

Votre application appelle supabase.rpc('join_waitlist', { new_email: email }). security definer exécute l'insert en tant que propriétaire de la fonction, les visiteurs n'ont donc besoin d'aucune règle sur la table, et la règle qui les laissait insérer peut partir, sinon l'ancien chemin reste ouvert à côté du nouveau. Ce que "Database error saving new user" dit de security definer explique ce qu'il cède, et appeler une fonction Supabase montre comment faire correspondre les noms d'arguments.

Avant de changer la façon dont votre application écrit

Gardez une copie de la base d'avant le moment où vous passez un insert en upsert ou remettez un compteur à niveau, et soyez prévenu quand une table se met à répondre à des inconnus.

Reeve Care garde une copie de votre base de données Supabase :

• une copie chiffrée selon un calendrier, sur le stockage propre de Reeve dans l'UE, en dehors de votre compte Supabase
• chaque table avec ses lignes telles qu'elles étaient avant qu'une fusion les écrase
• le compteur d'id de chaque table revient avec ses lignes lors d'une restauration, pour que la ligne suivante reçoive un numéro libre
• chaque copie vérifiée avant de compter, en comptant les lignes de chaque table
• vos fichiers envoyés aussi, dès que vous connectez un identifiant Storage
• une restauration en un clic, avec une copie de sécurité de l'état actuel prise d'abord
• tout ce que fait Monitor

Reeve Monitor continue de vérifier de l'extérieur :

• si votre application répond, vérifié chaque minute
• la vérification de sécurité complète toutes les heures, sur trois applications au plus
• un message quand un résultat change, pour qu'une table qui se met à répondre à des inconnus après un déploiement vous parvienne sans que vous alliez voir
• un rapport mensuel, en langage clair, de ce qu'il a vu

Que faire maintenant

Commencez par le nom entre guillemets : il dit quelle règle a refusé la ligne et quelle colonne elle garde. Avant de lancer un setval, un upsert ou toute autre chose qui réécrit des lignes dont des gens dépendent, gardez une copie de la base d'avant le changement.