"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.
- Ierobežojuma nosaukums parāda, kurš indekss nostrādāja.
*_pkeyir primārā atslēga,*_keyparasti ir arUNIQUEizveidots unikāls ierobežojums, viss pārējais ir kāda ar roku nosaukts unikāls indekss. - Rinda
DETAILparāda precīzu atslēgu, kas jau eksistē. - SQLSTATE ir
23505,unique_violation(PostgreSQL kļūdu kodi). Lietotnē ķeriet šo kodu, nevis ziņojuma tekstu, ko var tulkot.
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:
- Dati ielādēti ar konkrētiem id (
COPY, ETL rīks, ar roku rakstīts migrācijas skripts), un neviens pēc tam sekvenci nepārbīdīja. - Rindas nokopētas no citas datubāzes, piemēram, pārejot no MySQL uz PostgreSQL, bet sekvence palika sākuma vērtībā.
- Identitātes kolonna aizpildīta ar
OVERRIDING SYSTEM VALUE. Dokumentācija skaidri saka, ka tad norādītās vērtības aizstāj sekvences vērtības (INSERT); pati sekvence nepārvietojas. - Pārslēgšanās uz loģiskās replikācijas abonentu. PostgreSQL 18 un vecākās versijās sekvenču dati netiek replicēti vispār: rindas atnāk, bet abonenta sekvence joprojām rāda sākuma vērtību (loģiskās replikācijas ierobežojumi). Par to rakstām rakstā PostgreSQL loģiskā replikācija.
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;
ON CONFLICT DO UPDATEgarantē atomisku ievietošanas vai atjaunināšanas iznākumu pat pie lielas vienlaicības (INSERT ... ON CONFLICT).DO UPDATEvajag konflikta mērķi;DO NOTHINGto var izlaist un tad apstrādā konfliktus ar visiem izmantojamajiem unikālajiem ierobežojumiem.- Par arbitriem var būt tikai
NOT DEFERRABLEierobežojumi un unikālie indeksi, un izslēgšanas ierobežojumi kā arbitriDO UPDATEnetiek atbalstīti. - Vairāku rindu upsert, kas divreiz skar vienu un to pašu esošo rindu, beidzas ar kardinalitātes kļūdu. Pirms sūtīšanas atbrīvojiet paketi no dublikātiem.
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
- Pēc noklusējuma NULL vērtības netiek uzskatītas par vienādām, tāpēc unikāla kolonna pieņem daudz NULL, un vairāku kolonnu unikāls indekss noraida tikai rindas, kurās visas kolonnas ir vienādas.
NULLS NOT DISTINCTto maina (unikālie indeksi). Komandas, kas pāriet no citām datubāzēm, bieži pārsteidz abos virzienos. - Partīcijās dalītā tabulā unikālam vai primārās atslēgas ierobežojumam jāietver visas partīcijas atslēgas kolonnas, jo katras partīcijas indekss nodrošina unikalitāti tikai savā partīcijā (partīciju ierobežojumi). "Globāli unikāls" id, kas nav atslēgas daļa, starp partīcijām netiek nodrošināts.
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:
- Dublikātu meklēšana ir pilna skenēšana un kārtošana. Tabulā ar simtiem miljonu rindu vaicājums
GROUP BY ... HAVINGvar ilgt ilgi un izmantot disku. Palaidiet to replikā vai pa id diapazoniem un rēķinieties, ka pati tīrīšana būs pakešu darbs, nevis viensDELETE. - Dublikāti turpina nākt, kamēr jūs tīrāt. Mūsu pieredzē uzticama secība ir: vispirms salabot rakstīšanas ceļu (upsert vai unikāla atslēga uz biznesa id), tad iztīrīt vēsturi, tad būvēt indeksu. Citādi vienlaicīgā būve krīt uz rindām, kas ievietotas tīrīšanas laikā.
- Indeksa būve prasa laiku un disku. Vienlaicīga būve skenē tabulu divreiz un gaida vecākas transakcijas. Ļoti lielām tabulām plānojiet stundas un papildu diska vietu jaunajam indeksam.
max(id)ir lēts, ģenerētājvaicājums ne vienmēr. Indeksētai primārajai atslēgaimax()ir ātrs. Kolonnām bez indeksa vai simtiem tabulu skripts "salabot visas sekvences" skenē; palaidiet to apkopes logā.- Pārslēgšanās uz loģiskajām replikām. Sākot ar PostgreSQL 19, loģiskā replikācija var sinhronizēt sekvences, un
ALTER SUBSCRIPTION ... REFRESH SEQUENCEStās sinhronizē no jauna, bet tikai tad, ja arī publicētājs darbojas ar 19. vai jaunāku versiju (ALTER SUBSCRIPTION, 19+ versijas dokumentācija). Ar vecākiem publicētājiem sekvenču iestatīšana no datiem ir daļa no pārslēgšanās plāna. Atstājiet drošu rezervi virsmax(id), ja ieraksti vēl var būt ceļā. - Kļūdu apjoms. Lietotne, kas pie
23505mēģina atkārtoti, var radīt tūkstošiem kļūdu sekundē un pārpludināt žurnālu. Pārliecinieties, ka atkārtošanas loģika atšķir sekvences problēmu (krīt katrs ieraksts) no īsta dublikāta (krīt viens pieprasījums).
Galvenās atziņas
- Primārajai atslēgai vispirms salīdziniet sekvenci ar
max(id); atpalikusi sekvence ir biežākais cēlonis. - Salabojiet ar
setval(pg_get_serial_sequence(...), max + 1, false). Tas stājas spēkā uzreiz un nav atsaucams. - Pēc atjaunošanas, migrācijām un loģiskās replikācijas pārslēgšanās pārbaudiet visas serial un identitātes kolonnas, ne tikai to, kas krita.
- Īstiem dublikātiem lietojiet
ON CONFLICT, nevis "pārbaudi, tad ievieto". - Lielās tabulās vispirms apturiet jaunus dublikātus, tīriet paketēs, tad būvējiet unikālo indeksu
CONCURRENTLYun izdzēsiet atlikušosINVALIDindeksus.