"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):
nskaita rakstzīmes, nevis baitus.ā,žvai emocijzīme katra ir viena rakstzīme, lai gan UTF-8 tās aizņem vairākus baitus.- Garāka virkne izraisa kļūdu, ja vien liekās rakstzīmes nav tikai atstarpes. Tad vērtība klusi tiek apgriezta līdz limitam. To prasa SQL standarts.
- Tiešs cast uz
varchar(n)apgriež vērtību bez jebkādas kļūdas. To arī prasa standarts. varcharbez garuma pieņem jebkura garuma virknes. Patsnvar būt ne vairāk kā 10 485 760, un viena vērtība ir ierobežota ar aptuveni 1 GB.- Starp
text,varcharunvarchar(n)nav veiktspējas atšķirības, izņemot dažus CPU ciklus garuma pārbaudei.
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:
- Esošo datu mērīšana ir pilna skenēšana.
max(char_length(col))pār miljardu rindu nolasa visu tabulu. Palaidiet to uz replikas vai vispirms novērtējiet arTABLESAMPLE. - Īstais risks ir bloķēšana, nevis pārrakstīšana. Pat ALTER, kas maina tikai metadatus, gaida aiz garām transakcijām un bloķē visu, kas sarindojies aiz tā. Izmantojiet
lock_timeout, atkārtojumus un mierīgu laika logu. - Sadalītas (partitioned) tabulas. Tipa maiņa uz vecāktabulas attiecas uz visām partīcijām. PostgreSQL neļauj mainīt kolonnas tipu tikai vecāktabulai. Plānojiet bloķēšanu uz katras partīcijas.
- Loģiskā replikācija. DDL netiek replicēts. Vispirms paplašiniet kolonnu abonentā, pēc tam publicētājā. Citādi, mūsu pieredzē, pirmā garā vērtība aptur apply procesu.
- Daudz klientu vai šardu. Tā pati kolonna ir simtiem shēmu vai datubāzu. Izvērsiet izmaiņu ar skriptu, kas pieraksta, kur tā izdevās, un pārbaudiet, ka tā izpildīta visur.
- Indeksi uz garām vērtībām. Noņemot limitu, B-tree indeksā var nonākt daudz garākas virknes, bet B-tree ierakstam ir maksimālais izmērs, aptuveni trešdaļa lapas pēc saspiešanas (B-tree indeksi). Ja kolonna ir indeksēta, atstājiet saprātīgu CHECK limitu vai indeksējiet vērtības hešu.
-- 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
- SQLSTATE 22001: virkne pārsniedz varchar(n) limitu. n skaita rakstzīmes, nevis baitus.
- Ziņojumā nav kolonnas: atlasiet kolonnas ar šo limitu vai izmantojiet COPY konteksta rindu.
- varchar paplašināšana vai pāreja uz text kopš 9.2 nepārraksta tabulu, bet joprojām ņem ACCESS EXCLUSIVE: izmantojiet lock_timeout.
- Limita samazināšana pārraksta tabulu. Labāk text ar CHECK ierobežojumu, kas pievienots ar NOT VALID.
- Tiešs ::varchar(n) cast klusi apgriež datus. Meklējiet to savā ETL kodā.
- Masveida ielādei PostgreSQL 17+ COPY ON_ERROR ignore izlaiž sliktās rindas; pierakstiet un labojiet tās avotā.