Incidentu reaģēšana

PostgreSQL "value too long for type character varying": kā to novērst

Kļūda nepasaka, kura kolonna nav derējusi; kolonnas paplašināšana ir lētāka, nekā vairums domā; bet tiešs cast var klusi apgriezt datus. Lūk, kā atrast kolonnu, droši to salabot un neļaut kļūdai atgriezties.

· 15 min lasīšanai · AG Data komanda

"value too long for type character varying(n)" nozīmē, ka mēģinājāt saglabāt virkni, kas ir garāka par varchar(n) kolonnai noteikto limitu. PostgreSQL noraida visu komandu ar SQLSTATE 22001, string_data_right_truncation (kļūdu kodi). Labojums parasti ir vienkāršs. Laiku patērē divas lietas: atrast kolonnu, kas izraisīja kļūdu, un nomainīt limitu lielā, noslogotā tabulā.

CREATE TABLE customers (id bigint, email varchar(50), city varchar(50));

INSERT INTO customers VALUES (1, 'a-very-long-address-from-a-marketing-import@example.com', 'Riga');
-- ERROR:  value too long for type character varying(50)

Ko PostgreSQL patiesībā pārbauda

Noteikumi ir īsi, un tos vērts zināt precīzi (rakstzīmju tipi):

Pēdējais punkts ir svarīgs labojumam. PostgreSQL varchar(255) nav ātrāks vai mazāks par text. Limits ir biznesa noteikums, nekas vairāk.

1. solis: atrodiet kolonnu

Ziņojumā ir tips un limits, bet nav kolonnas. To var redzēt pirmkodā: kļūdu izraisa varchar ievades un garuma pārveides funkcijas, kuras nezina, kuru kolonnu tās aizpilda (varchar.c). Tabulā ar piecām varchar(50) kolonnām no ziņojuma vien nevar saprast, kura no tām izraisīja kļūdu.

-- which columns of this table have exactly this limit?
SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'customers'
  AND character_maximum_length = 50;

Pēc tam salīdziniet aplikācijas sūtītās vērtības ar šo īso sarakstu. Draivera vai ORM žurnālā parasti ir piesaistītie parametri. Ja ieraksts nāk no funkcijas vai trigera, kolonna ar šo limitu var būt citā tabulā, nevis tajā, kurā rakstījāt. Skatiet kļūdas CONTEXT rindu.

COPY palīdz vairāk. Tā kļūdas kontekstā ir rindas numurs un kolonnas nosaukums:

COPY customers FROM '/tmp/customers.csv' WITH (FORMAT csv);
-- ERROR:  value too long for type character varying(50)
-- CONTEXT:  COPY customers, line 18234, column email: "..."

2. solis: izlemiet, kam limits domāts

Pirms kaut ko maināt, pajautājiet, vai limitam ir jēga. Valsts kods varchar(2) kolonnā ir īsts noteikums, un 30 rakstzīmju vērtība tur ir slikti dati, kas jānoraida jau iepriekš. E-pasta kolonna varchar(50) ir kāda pirms gadiem izdarīts minējums. Īstās adreses ir garākas, un kļūda tagad bloķē likumīgus klientus.

Mūsu pieredzē lielākā daļa šo incidentu ir otrā veida: ORM vai nokopētas shēmas noklusējuma garums, no kura reālie dati beidzot ir izauguši.

A risinājums: paplašiniet kolonnu

SET lock_timeout = '3s';
ALTER TABLE customers ALTER COLUMN email TYPE varchar(320);
-- or remove the limit entirely:
ALTER TABLE customers ALTER COLUMN email TYPE text;

varchar limita palielināšanai vai noņemšanai kopš PostgreSQL 9.2 tabula nav jāpārraksta (9.2 izlaiduma piezīmes). Plānotājs uzskata garuma maiņu par tukšu darbību, ja jaunais limits ir vienāds vai lielāks. Pāreja no varchar uz text arī nepārraksta tabulu. ALTER TABLE dokumentācijā teikts, ka text ↔ varchar gadījumā indeksi netiek pārbūvēti, jo abi tipi kārtojas vienādi (ALTER TABLE).

Āķis ir bloķēšanā. ALTER COLUMN TYPE paņem ACCESS EXCLUSIVE bloķēšanu pat tad, ja nekas netiek pārrakstīts. Ja gara transakcija tur jebkādu bloķēšanu uz tabulas, jūsu ALTER gaida, un katrs vaicājums aiz tā gaida arī. Tāpēc piemērā ir iestatīts lock_timeout: labāk ātri neizdoties un mēģināt vēlreiz, nekā iesaldēt tabulu. Pilns paņēmiens ir rakstā PostgreSQL migrācijas bez dīkstāves.

Ja kolonnu izmanto skats, ALTER neizdodas ar cannot alter type of a column used by a view or rule. Skats jādzēš un jāizveido no jauna tajā pašā transakcijā, kurā ir ALTER.

Limita samazināšana ir cita operācija. Saīsinājums bez pārrakstīšanas darbojas tikai tad, ja jaunais limits ir lielāks vai vienāds ar veco. Maiņa no varchar(255) uz varchar(100) pārraksta tabulu un tās indeksus zem ACCESS EXCLUSIVE. Lielā tabulā tā ir dīkstāve.

B risinājums: text un CHECK ierobežojums

Ja vajag limitu, ko vēlāk var mainīt, neaiztiekot tipu, glabājiet text un garumu nodrošiniet ar ierobežojumu:

-- business limit as a constraint instead of a type modifier
ALTER TABLE customers ALTER COLUMN city TYPE text;   -- no rewrite, no index rebuild
ALTER TABLE customers
  ADD CONSTRAINT customers_city_len CHECK (char_length(city) <= 100) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT customers_city_len;  -- SHARE UPDATE EXCLUSIVE

Ar NOT VALID jaunās un mainītās rindas tiek pārbaudītas uzreiz, bet esošās rindas netiek skenētas. VALIDATE CONSTRAINT pēc tam tās skenē zem SHARE UPDATE EXCLUSIVE bloķēšanas, kas nebloķē parastu lasīšanu un rakstīšanu (ALTER TABLE). Lai vēlāk mainītu limitu, pievienojiet jaunu ierobežojumu un dzēsiet veco. Tips paliek tas pats, tāpēc pārrakstīšana nenotiek.

C risinājums: apgrieziet apzināti, nevis nejauši

Dažreiz vērtības apgriešana ir pareizā atbilde: brīva teksta piezīme vai user agent virkne, ko glabājat tikai atkļūdošanai. Tad apgrieziet tieši un redzami, ar left(value, 200) vaicājumā vai aplikācijas kodā, un pierakstiet to žurnālā.

Taču uzmanieties ar cast:

SELECT 'abcdefgh'::varchar(5);       -- 'abcde', no error, no warning
SELECT 'abc     '::varchar(5);       -- 'abc  ', trailing spaces are clipped silently
INSERT INTO t (code) VALUES ('abcdefgh');  -- varchar(5): ERROR

ETL darbs, kas raksta value::varchar(50), "lai tipi sakristu", nekad neizgāžas. Tas vienkārši apgriež datus, un neviens to nepamana, līdz klienta adresei pietrūkst pēdējās rindas. Pārskatot ielādes kodu, mēs šī iemesla dēļ meklējam cast uz varchar(n) un char(n).

Masveida ielāde: neļaujiet vienai rindai nogalināt darbu

Pēc noklusējuma viena slikta rinda pārtrauc visu COPY. Kopš PostgreSQL 17 ON_ERROR ignore izlaiž rindas, kuru vērtības neizdodas pārveidot ievadē, un pārāk gara varchar vērtība ir viena no šādām kļūdām (PostgreSQL 17 izlaiduma piezīmes). Ar LOG_VERBOSITY verbose PostgreSQL par katru izlaisto rindu izdod paziņojumu ar rindu un kolonnu (COPY).

-- PostgreSQL 17+: skip rows that fail input conversion and log them
COPY customers FROM '/tmp/customers.csv'
  WITH (FORMAT csv, ON_ERROR ignore, LOG_VERBOSITY verbose);

Jaunākās versijās ir arī REJECT_LIMIT, lai darbs neizdotos pēc pārāk daudzām sliktām rindām, un set_null, lai rindu saglabātu un lauku aizstātu ar null. Pirms paļauties uz kādu no tiem, pārbaudiet COPY lapu savai versijai. Jebkurā gadījumā savāciet noraidītās rindas un labojiet tās avotā. Klusi izlaist klientu datus ir kļūda, nevis funkcija.

Ja nākat no MySQL

Šī kļūda bieži parādās uzreiz pēc migrācijas. Ja strict SQL režīms ir izslēgts, MySQL pārāk garu VARCHAR vērtību apgriež un izdod tikai brīdinājumu (MySQL CHAR un VARCHAR). Aplikācijas, kas strādāja ar šādu serveri, gadiem varēja zaudēt datus, to nepamanot. PostgreSQL tas pats ieraksts neizdodas, tāpēc migrācija atklāj problēmu, kas bija vienmēr. Pirms pārslēgšanās pārbaudiet maksimālos garumus avota datos un to virkņu izmērus, ko sūta jūsu aplikācija. Vairāk par to mūsu MySQL uz PostgreSQL migrācijas ceļvedī.

Lielās tabulās un daudzās datubāzēs

Viss iepriekš minētais ir viegli testa datubāzē ar tūkstoš rindām. Produkcijā dažas lietas mainās:

-- how close is existing data to the limit? (full scan: run on a replica)
SELECT max(char_length(email)) AS max_chars,
       max(octet_length(email)) AS max_bytes
FROM customers;

-- quick estimate on a huge table: read ~1% of pages
SELECT max(char_length(email)) FROM customers TABLESAMPLE SYSTEM (1);

Galvenās atziņas

PostgreSQL Kļūdas Datu tipi Migrācijas
Sadarbība ar mums

Datu ielāde krīt uz kolonnu limitiem?

Mēs salabojam neizdevušos importu un migrācijas, mainām kolonnu tipus lielās produkcijas tabulās bez ilgām bloķēšanām un pārskatām shēmas, pirms tās izraisa incidentus.

Sazināties Vairāk no emuāra