Incidentu reaģēšana

PostgreSQL "duplicate key value violates unique constraint": cēloņi un risinājumi

Kļūda izskatās pēc datu problēmas, taču primārajai atslēgai tā parasti nozīmē sekvenci, kas atpalikusi pēc atjaunošanas, migrācijas vai pārslēgšanās. Kā saprast, kurš gadījums ir jums, droši salabot sekvenci un novērst sacensību apstākļus un dublikātus lielās tabulās.

· 14 min lasīšanai · AG Data komanda

"duplicate key value violates unique constraint" nozīmē, ka PostgreSQL atsakās izpildīt INSERT vai UPDATE, kas izveidotu otru rindu ar tādu pašu vērtību primārajā atslēgā vai unikālā kolonnā. Pati kļūda ir vienkārša. Svarīgi ir tas, no kurienes radās dublētā vērtība, un primārajai atslēgai atbilde parasti nav lietotne, bet sekvence, kas atpaliek no datiem.

Kā lasīt kļūdu

ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (id)=(1042) already exists.

Katru unikālo ierobežojumu un primāro atslēgu nodrošina unikāls indekss, ko PostgreSQL izveido automātiski (unikālie indeksi). Tāpēc jautājums vienmēr ir viens: kura vērtība sadūrās un no kurienes tā radās?

1. gadījums: sekvence atpaliek no datiem

Klasika. Tabulā ir rindas līdz id = 50000, bet sekvence nākamo izsniegs 1042. Katrs ieraksts, kas paļaujas uz noklusējuma vērtību, sadurās ar esošu rindu, līdz sekvence pārsniegs esošās vērtības. Tipiski ceļi, kā tur nonākt:

Pārbaude aizņem desmit sekundes:

-- which sequence feeds the column (works for serial and identity)
SELECT pg_get_serial_sequence('public.orders', 'id');

-- where the sequence is vs. where the data is
SELECT last_value, is_called FROM public.orders_id_seq;
SELECT max(id) FROM public.orders;

pg_get_serial_sequence atgriež kolonnas sekvenci gan serial/bigserial, gan identitātes kolonnām (sistēmas informācijas funkcijas), tāpēc tās nosaukums nav jāmin. Ja last_value ir mazāks par max(id), problēma ir atrasta.

Sekvences labošana

-- next nextval() returns max(id)+1 (or 1 on an empty table)
SELECT setval(pg_get_serial_sequence('public.orders', 'id'),
              COALESCE(max(id), 0) + 1, false)
FROM public.orders;

Ar trešo argumentu false nākamais nextval atgriež tieši iestatīto vērtību; divu argumentu formā sekvence vispirms tiek palielināta (sekvenču funkcijas). Produkcijā svarīgas divas setval īpašības: izmaiņa citām sesijām redzama uzreiz, un tā netiek atsaukta, ja jūsu transakcija tiek atritināta. Variants "pamēģināt transakcijā un atritināt" nepastāv.

Pēc atjaunošanas vai migrācijas pieņemiet, ka skartas vairākas tabulas. Šis psql fragments ģenerē un izpilda labojumu katrai serial un identitātes kolonnai:

-- psql: generate and run setval for every serial/identity column
SELECT format('SELECT setval(%L, COALESCE((SELECT max(%I) FROM %I.%I), 0) + 1, false);',
              pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name),
              column_name, table_schema, table_name)
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) IS NOT NULL
\gexec

Palaidiet to, kamēr lietotne neraksta, vai rēķinieties ar dažām kļūdām izpildes laikā: rindas, kas ievietotas starp max() un setval, joprojām var sadurties. Ja viena sekvence apkalpo vairākas tabulas, mūsu pieredzē drošāk ir to iestatīt ar roku no lielākās vērtības visās tabulās.

2. gadījums: īsts dublikāts no lietotnes

Ja sekvence ir kārtībā, dublikāts radās no datiem: atkārtots pieprasījums, dubultklikšķis, divi procesi apstrādā vienu ziņojumu, vai shēma "pārbaudi, vai eksistē, tad ievieto". Šī shēma ir sacensību apstākļi: abas sesijas redz "rindas nav", abas ievieto, un otrā saņem kļūdu. Unikālais ierobežojums te dara savu darbu. Labojums ir rakstīšanas loģikā, nevis datubāzē:

INSERT INTO users (email, name)
VALUES ('a@example.com', 'Anna')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- or simply skip rows that already exist
INSERT INTO events (event_id, payload)
VALUES (...)
ON CONFLICT (event_id) DO NOTHING;

Idempotentai ziņojumu apstrādei parasti tīrākais risinājums ir unikāla atslēga uz notikuma id kopā ar DO NOTHING.

Robi ir normāli, sadursmes nav. Ar nextval iegūta vērtība netiek izmantota atkārtoti, ja transakcija tiek pārtraukta (dokumentācija), tāpēc neveiksmīgi ieraksti un upsert atstāj robus. Nekad "nelabojiet" robus, pārbīdot sekvenci atpakaļ: tieši tā rodas 1. gadījums.

3. gadījums: NULL, izteiksmes un partīcijas

Unikāla ierobežojuma pievienošana lielai tabulai

Dažreiz kļūda ir mērķis: jūs gribat, lai datubāze noraida dublikātus, ko lietotne veido jau gadiem. Lielā tabulā to dariet divos soļos:

-- find duplicates before adding a unique constraint
SELECT email, count(*)
FROM users
GROUP BY email
HAVING count(*) > 1
ORDER BY count(*) DESC
LIMIT 50;

-- then build the index without blocking writes
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);

CREATE INDEX CONCURRENTLY nebloķē rakstīšanu, bet to nevar palaist transakcijas blokā. Ja tas uzduras dublikātam, tas beidzas ar kļūdu un atstāj INVALID indeksu. Dokumentācija brīdina, ka šāds indekss joprojām rada atjaunināšanas izmaksas un unikāla indeksa gadījumā turpina nodrošināt unikalitāti (CREATE INDEX). Pēc tīrīšanas izdzēsiet to un mēģiniet vēlreiz vai pārbūvējiet ar REINDEX INDEX CONCURRENTLY, skat. REINDEX CONCURRENTLY. Kad indekss ir derīgs, ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX pārvērš to par ierobežojumu; UNIQUE gadījumā tā ir ātra operācija, taču partīcijās dalītām tabulām tā netiek atbalstīta (ALTER TABLE).

Kas mainās lielā mērogā

Testa datubāze ar 10 000 rindām slēpj lielāko daļu īsto problēmu:

Galvenās atziņas

PostgreSQL Sekvences Kļūdas Upsert
Sadarbība ar mums

Ieraksti produkcijā krīt dublētu atslēgu dēļ?

Noskaidrosim, kāpēc sekvences un dati izgāja no sinhrona, salabosim tos bez dīkstāves un pārbaudīsim, vai migrācija vai pārslēgšanās nav atstājusi tādā pašā stāvoklī arī citas tabulas.

Sazināties Vairāk no emuāra