PostgreSQL "integer out of range": kad int primārajai atslēgai beidzas vērtības

PostgreSQL "integer out of range" noslogotā tabulā parasti nozīmē vienu: integer primārā atslēga ir sasniegusi 2 147 483 647, un brīvu numuru vairs nav. Ierakstīšana apstājas, lietotne sāk mest kļūdas, un acīmredzamais risinājums, ALTER COLUMN id TYPE bigint, bloķē tabulu uz visu tās pārrakstīšanas laiku. Tabulā ar diviem miljardiem rindu tas var ilgt stundas.

Tas ir lēns, paredzams incidents. Skaitītājs katru dienu pieaug par zināmu apjomu, tāpēc to var redzēt mēnešiem iepriekš. Tālāk: kā kļūda izpaužas, kā atrast riska tabulas, ko darīt, ja ID jau ir beigušies, un kā pāriet uz bigint tiešsaistē.

Divi kļūdas paziņojumi, viens cēlonis

ERROR:  nextval: reached maximum value of sequence "orders_id_seq" (2147483647)

ERROR:  integer out of range

integer tips aizņem 4 baitus un glabā vērtības no -2147483648 līdz +2147483647. bigint aizņem 8 baitus un sniedzas līdz aptuveni 9,2 kvintiljoniem. Kuru paziņojumu redzat, atkarīgs no sekvences aiz kolonnas:

• nextval: reached maximum value of sequence nozīmē, ka robeža ir pati sekvence. Kopš PostgreSQL 10 serial izveido sekvenci AS integer, tāpēc sekvence apstājas pie 2147483647 kopā ar kolonnu.
• integer out of range INSERT laikā parasti nozīmē, ka sekvence ir bigint, bet kolonna nav. Pirms PostgreSQL 10 izveidotās sekvences ģenerēja 64 bitu vērtības, tāpēc tabulas, kas izgājušas cauri jauninājumiem, bieži izskatās tieši tā. Sekvence mierīgi atgriež 2147483648, un pārveidošana uz integer kolonnu neizdodas.

Tas pats paziņojums parādās arī parastā aritmētikā, piemēram, ja sareizina divas integer vērtības un rezultāts neietilpst. Ja neizdodas atskaite, nevis INSERT, vispirms skatieties izteiksmes, nevis atslēgas.

Atrodiet tabulas, kas tuvojas robežai

Zemāk esošais vaicājums parāda katru integer kolonnu, aiz kuras ir sekvence (serial vai identity), un cik liela daļa int diapazona jau izlietota. Tas izmanto pg_get_serial_sequence un skatu pg_sequences.

SELECT c.oid::regclass AS table_name, a.attname AS column_name,
       s.data_type AS seq_type, s.last_value,
       round(100.0 * s.last_value / 2147483647, 1) AS pct_of_int_max
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid AND c.relkind IN ('r', 'p')
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq
) g
JOIN pg_sequences s
  ON format('%I.%I', s.schemaname, s.sequencename)::regclass = g.seq::regclass
WHERE a.atttypid = 'integer'::regtype
  AND a.attnum > 0 AND NOT a.attisdropped
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pct_of_int_max DESC NULLS LAST;

Divas lietas, ko paturēt prātā. last_value ir pēdējā uz diska ierakstītā vērtība, tāpēc kešošanas dēļ tā var būt nedaudz priekšā faktiski izsniegtajām. Un tā ir NULL, ja sekvence vēl nav izmantota vai jūsu lomai trūkst privilēģiju, tāpēc palaidiet pārbaudi ar lomu, kas redz visu.

Mēs brīdinām pie 50% un 75% uzskatām par projektu, kas jāieplāno tūlīt. Tas ir praktisks noteikums, nevis standarts. Patiesībā svarīgs ir temps: nolasiet vērtību divreiz ar nedēļas starpību un izdaliet atlikušos numurus ar dienas pieaugumu. "Atlikuši astoņi mēneši" un "atlikušas trīs nedēļas" prasa ļoti atšķirīgus plānus.

ID jau beigušies: pirmā stunda

Ja ierakstīšana neizdodas tieši tagad, pilna pārrakstīšana lielai tabulai nav risinājums. Divas lietas dod laiku:

1. Izmantojiet negatīvo diapazonu. integer glabā arī aptuveni 2,1 miljardu negatīvu vērtību. Sekvences restartēšana tur ar ALTER SEQUENCE atjauno ierakstīšanu dažās sekundēs:

   ALTER SEQUENCE orders_id_seq MINVALUE -2147483648 RESTART WITH -2147483648;

   Tas darbojas tikai tad, ja nekas lietotnē nepieņem, ka ID ir pozitīvi vai ka lielāks ID nozīmē jaunāku rindu. Pirms tam pārbaudiet kārtošanu, lapošanu pēc ID un jebkuru kodu, kas negatīvus skaitļus uztver kā īpašus. Tas ir tilts līdz migrācijai, nevis labojums.
2. Atrodiet, kas tērē ID. nextval vērtības netiek izmantotas atkārtoti, ja transakcija tiek atcelta, un INSERT ... ON CONFLICT paņem savu numuru, pirms konstatē konfliktu. Atkārtošanas cikls vai darbs ar daudziem upsert var iztērēt diapazonu daudz ātrāk, nekā liecina rindu skaits. Ja to palēnināt, tilts kalpo ilgāk.

Tad, kamēr tilts tur, plānojiet īsto labojumu. Ņemiet vērā, ka ALTER SEQUENCE izpildes laikā bloķē paralēlus nextval izsaukumus, tāpēc noslogotā tabulā gaidiet īsu pauzi.

Vienkāršais labojums un kāpēc tas sāp lielās tabulās

BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE orders ALTER COLUMN id TYPE bigint;   -- rewrites table and indexes
ALTER SEQUENCE orders_id_seq AS bigint;           -- otherwise it still stops at 2147483647
COMMIT;

ALTER TABLE dokumentācija par cenu runā skaidri: kolonnas tipa maiņa "parasti izraisa visas tabulas un tās indeksu pārrakstīšanu". integer uz bigint nav binārā saderība, tāpēc pārrakstīšana notiek. Komanda visu laiku tur ACCESS EXCLUSIVE bloķējumu, tāpēc gaida pat lasīšana, un pārrakstīšanai uz laiku var vajadzēt līdz pat divkāršam diska apjomam.

Otrā rinda arī ir svarīga. Sekvence, kas izveidota AS integer, saglabā veco maksimumu, līdz to nomainīsiet. Saskaņā ar dokumentāciju tipa maiņa automātiski pārvieto minimumu un maksimumu tikai tad, ja tie bija vecā tipa noklusējuma vērtības. Ja kāds iestatījis konkrētu MAXVALUE, nomainiet arī to.

Mazām tabulām apkopes logā tas ir labi. Lielām pārejiet tiešsaistē.

Tiešsaistes pāreja no int uz bigint soli pa solim

Šī ir standarta "paplašini un sašaurini" pieeja, ko aprakstām mūsu PostgreSQL migrāciju bez dīkstāves ceļvedī. Piemērā izmantota tabula orders ar serial primāro atslēgu id.

1. Pievienojiet jauno kolonnu un uzturiet to sinhronu. Kolonnas pievienošana bez noklusējuma vērtības maina tikai metadatus, tāpēc tā ir ātra pat milzīgās tabulās. Tai tomēr vajadzīgs īss ekskluzīvs bloķējums, tāpēc iestatiet lock_timeout un atkārtojiet, ja iestājas taimauts.

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN id_new bigint;      -- no default: metadata only

CREATE FUNCTION orders_id_sync() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.id_new := NEW.id;
  RETURN NEW;
END $$;

CREATE TRIGGER orders_id_sync BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_id_sync();

2. Aizpildiet pa partijām. Mazas transakcijas, pauze starp tām un acs uz replikācijas aizturi. Katra atjauninātā rinda rada jaunu rindas versiju, tāpēc autovacuum būs darbs.

-- repeat with the next range until max(id) is covered
UPDATE orders SET id_new = id
WHERE id >= 1 AND id < 50001 AND id_new IS NULL;

3. Izveidojiet indeksu un pierādiet, ka NULL nav, nebloķējot rakstīšanu. CREATE UNIQUE INDEX CONCURRENTLY izveido nākamās primārās atslēgas indeksu, kamēr tabulā var rakstīt. CHECK ... NOT VALID ierobežojums ar sekojošu VALIDATE ļauj vēlākajam SET NOT NULL izlaist pilnu tabulas skenēšanu, ko dokumentācija apraksta tieši.

CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_idx ON orders (id_new);

ALTER TABLE orders ADD CONSTRAINT orders_id_new_nn
  CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_new_nn;

4. Samainiet vienā īsā transakcijā. Katrs šeit esošais priekšraksts ir ātrs: bez tabulas skenēšanas un bez pārrakstīšanas. ADD PRIMARY KEY USING INDEX pievieno esošo indeksu, nevis būvē jaunu.

BEGIN;
SET LOCAL lock_timeout = '5s';
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
ALTER SEQUENCE orders_id_seq AS bigint;
ALTER TABLE orders ALTER COLUMN id_new SET NOT NULL;   -- skenēšana izlaista CHECK dēļ
ALTER TABLE orders DROP CONSTRAINT orders_pkey;        -- vispirms dzēsiet atsaucošās FK
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_idx;
DROP TRIGGER orders_id_sync ON orders;
ALTER TABLE orders ALTER COLUMN id_new SET DEFAULT nextval('orders_id_seq');
ALTER SEQUENCE orders_id_seq OWNED BY orders.id_new;   -- pirms vecā kolonna tiek dzēsta
ALTER TABLE orders DROP COLUMN id;
ALTER TABLE orders RENAME COLUMN id_new TO id;
ALTER TABLE orders DROP CONSTRAINT orders_id_new_nn;
COMMIT;

Pievērsiet uzmanību secībai. Sekvence ir OWNED BY vecajai kolonnai, un šīs kolonnas dzēšana dzēstu arī sekvenci, tāpēc vispirms pārceliet piederību. Trigeris atsaucas uz veco kolonnas nosaukumu, tāpēc tas tiek dzēsts pirms pārdēvēšanas. DROP COLUMN ir ātrs, jo fiziski datus nedzēš; vieta atgriežas laika gaitā, rindām tiekot pārrakstītām.

5. Atjaunojiet ārējās atslēgas bez ilga bloķējuma. Ja citas tabulas atsaucas uz orders.id, šīs ārējās atslēgas jādzēš pirms 4. soļa, jo DROP CONSTRAINT orders_pkey atteiksies, kamēr tās no tās ir atkarīgas. Pievienojiet tās atpakaļ kā NOT VALID un validējiet atsevišķi. Validācija ņem tikai SHARE UPDATE EXCLUSIVE bloķējumu bērnu tabulai un ROW SHARE bloķējumu tabulai, uz kuru atsaucas, tāpēc parasta rakstīšana turpinās:

ALTER TABLE order_items ADD CONSTRAINT order_items_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_id_fkey;

Identity kolonnām vajadzīga nedaudz atšķirīga samaiņa (DROP IDENTITY vecajai kolonnai, ADD GENERATED ... AS IDENTITY ar pareizu sākuma vērtību jaunajai). Uzrakstiet šo skriptu un izmēģiniet to uz pilna izmēra kopijas, pirms palaižat produkcijā.

PostgreSQL 18 versijā arī pašu not-null ierobežojumu var pievienot kā NOT VALID un validēt vēlāk, saskaņā ar laidiena piezīmēm. Augstāk aprakstītā CHECK pieeja darbojas visās atbalstītajās versijās.

Kas mainās vairāku terabaitu tabulā

• Garākā daļa ir aizpildīšana. Divu miljardu rindu atjaunināšana rada daudz WAL un atstāj mirušu versiju katrai rindai, līdz vacuum to satīra. Izvēlieties partiju izmēru pēc WAL tempa un repliku aiztures, nevis pēc sajūtas. Plānojiet dienas, nevis stundas, un padariet darbu atsākamu no pēdējā apstrādātā ID.
• Diska lietojums vispirms pieaug un tikai tad samazinās. Katra aizpildītā rinda iegūst jaunu versiju, un jaunā kolonna un indekss aizņem vietu. Pēc DROP COLUMN vecie dati paliek uz diska, līdz rindas tiek pārrakstītas. Pirms sākat, pārbaudiet brīvo vietu primārajā serverī un katrā replikā.
• Bloķējumu rindas ir īstais dīkstāves risks. Samaiņas transakcija ir ātra, bet tai vajag ACCESS EXCLUSIVE. Ja garš vaicājums tur bloķējumu uz tabulas, samaiņa gaida, un katrs jauns vaicājums nostājas rindā aiz tās. Īss lock_timeout, atkārtošanas cikls un garo transakciju pārbaude tieši pirms samaiņas ir obligāti.
• Loģiskā replikācija jums neseko. DDL netiek replicēts uz abonentiem. Vispirms nomainiet abonenta kolonnu uz bigint, lai ienākošās vērtības virs 2147483647 joprojām ietilptu, un tikai tad publicētāju. Arī sekvenču vērtības netiek replicētas, un tas ir svarīgi, ja abonents kādreiz var kļūt par primāro.
• Sadalītām tabulām vajag rūpību katrai partīcijai. ADD ... USING INDEX saskaņā ar dokumentāciju sadalītām tabulām netiek atbalstīts, tāpēc tur samaiņa jāplāno citādi. Daudzas partīcijas nozīmē arī daudzus būvējamus indeksus.
• Sekojiet otram 2 miljardu skaitītājam. Katra rinda, ko aizpildīšana pārraksta, saņem jaunu transakcijas ID, un vacuum vēlāk visas šīs versijas būs jāiesaldē. Gaidiet, ka nākamais pret-wraparound vacuum šai tabulai būs smagāks nekā parasti, un sekojiet XID vecumam; kāpēc tas ir svarīgi, skaidrojam mūsu rakstā par transakciju ID wraparound.