Sumljive vrste

Na njihovom vanjskom izgledu ništa ne izaziva sumnju. Štoviše, čak ti se čine dobro poznatima. No, to važi sve dok ih ne provjeriš. Tu se može pokazati njihova zla suština, djelujući potpuno drugačije nego što si očekivao. Ponekad ispadnu stvari od kojih ti se kosa digne na glavi — na primjer, izgube povjerene tajne podatke. Kada ih suočiš, tvrde da se ne poznaju, iako u sjeni marljivo rade pod jednim krovom. Vrijeme je da ih konačno izvučemo na čistac. Hajde da se i mi pozabavimo tim sumnjivim tipovima.

Tipizacija podataka u PostgreSQL-u, uprkos svojoj logici, ponekad zaista donosi vrlo čudna iznenađenja. U ovom članku ćemo pokušati razjasniti neke od njihovih posebnosti, istražiti uzroke njihovog čudnog ponašanja i razumjeti kako izbjeći probleme u svakodnevnoj praksi. Da budem iskren, napisao sam ovaj članak i kao svojevrsni priručnik za sebe, priručnik kojem bih mogao lako pristupiti u spornim slučajevima. Stoga će se on dopunjavati kako budem otkrivao nova iznenađenja od sumnjivih tipova. Pa, krenimo, neumorni tragači za podacima!

Dosje broj jedan. real/double precision/numeric/money

Naizgled, numerički tipovi su najmanje problematični kada je u pitanju iznenađenja u ponašanju. No, ne možeš tako razmišljati. Zbog toga ćemo s njima i započeti. Dakle...

Zaboravili smo kako da brojimo

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

U čemu je problem? U tome što PostgreSQL konvertuje netipiziranu konstantu 0.1 u tip double precision i pokušava je uporediti s 0.1 tipa real. A to su apsolutno različite vrijednosti! Suština je u predstavljanju realnih brojeva u mašinskoj memoriji. Budući da 0.1 nije moguće predstaviti kao konačnu binarnu frakciju (to će biti 0.0(0011) u binarnom obliku), brojevi različitih preciznosti će se razlikovati, otuda i rezultat da nisu jednaki. Općenito, ovo je tema za poseban članak, neću se detaljnije baviti ovim ovdje.

Odakle greška?

SELECT double precision(1)

ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Greška **********
ERROR: syntax error at or near "("
SQL stanje: 42601
Simbol: 24

Mnogi vedo, da PostgreSQL omogoča funkcijsko zapisovanje pretvorb tipov. To pomeni, da lahko zapišete ne le 1::int, temveč tudi int(1), kar bo enakovredno. Vendar to ne velja za tipe, katerih ime je sestavljeno iz več besed! Zato, če želite pretvoriti numerično vrednost v tip double precision na funkcijski način, uporabite alias tega tipa float8, torej SELECT float8(1).

Kaj je več od neskončnosti?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

Tako torej! Izkazalo se je, da obstaja nekaj, kar je večje od neskončnosti, in to je NaN! Pri tem dokumentacija PostgreSQL z iskrenimi očmi trdi, da je NaN vnaprej večji od katere koli druge številke in torej neskončnosti. Enako velja za -NaN. Pozdravljeni, ljubitelji matematične analize! Vendar je treba pomniti, da vse to velja v okviru realnih števil.

Okrugljivost oči

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

Še en nepričakovan pozdrav iz baze. In znova je treba zapomniti, da za tipe double precision in numeric veljajo različnaokroglična pravila. Za numeric velja običajno okroglo pravilo, kjer se 0,5 okrogli navzgor, medtem ko se pri double precision 0,5 okrogli proti najbližjemu sodemu celemu.

Denar je nekaj posebnega

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Napaka **********
ERROR: cannot cast type money to double precision
SQL stanje: 42846
Simbol: 19

Po mnenju PostgreSQL denar ni realno število. Po mnenju nekaterih posameznikov pa tudi. Mi pa se moramo spomniti, da je pretvorba tipa money mogoča le v tip numeric, prav tako pa lahko tip money pretvorite le v tip numeric. Ampak s tem se lahko že poigravamo, kot si želimo. Toda to ne bo več tisti denar.

Smallint in generiranje zaporedij

SELECT *
  FROM generate_series(1::smallint, 5::smallint, 1::smallint)

ERROR:  function generate_series(smallint, smallint, smallint) is not unique
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Napaka **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL stanje: 42725
Namig: Could not choose a best candidate function. You might need to add explicit type casts.
Simbol: 18

PostgreSQL не любит мелочиться. Какие последовательности могут быть основаны на smallint? int, не меньше! Поэтому при попытке выполнения вышеприведенного запроса база пытается привести smallint к другому целочисленному типу и видит, что таких приведений может быть несколько. Какое приведение выбрать? Это она решить не может, и поэтому возникает ошибка.

Досье номер два. «char»/char/varchar/text

Символьные типы тоже имеют свои странности. Давайте познакомимся с ними.

Что это за фокусы?

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

Что это за тип «char», что это за клоун? Нам такие не нужны… Он прикидывается обычным char, хоть и в кавычках. Он отличается от обычного char тем, что выводит только первый байт строкового представления, тогда как нормальный char выводит первый символ. В нашем случае первый символ – буква П, которая в юникод-представлении занимает 2 байта, о чем свидетельствует конвертация результата в тип bytea. А тип «char» берет только первый байт этого юникод-представления. Тогда зачем он нужен? Документация PostgreSQL говорит, что это специальный тип для особых нужд. Так что вряд ли мы его потребуем. Но посмотрите ему в глаза и не ошибитесь, когда встретите его с его особенным поведением.

Лишние пробелы. С глаз долой, из сердца вон

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

Взгляните на приведенный пример. Я специально все результаты привел к типу bytea, чтобы наглядно показать, что там находится. Где хвостовые пробелы после приведения к типу varchar(6)? Документация лаконично утверждает: «При приведении значения character к другому символьному типу дополняющие пробелы отбрасываются». Эту нелюбовь надо запомнить. И обратите внимание, что если строковая константа в кавычках сразу приводится к типу varchar(6), концевые пробелы сохраняются. Вот такие чудеса.

Досье номер три. json/jsonb

JSON — это отдельная структура, которая живет своей жизнью. Поэтому ее сущности несколько отличаются от сущностей PostgreSQL. Вот примеры.

Johnson & Johnson. Odkrijte razliko

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

Vse je v tem, da ima JSON svojo pojavnost null, ki ni enaka NULL v PostgreSQL. Hkrati pa sam JSON-objekt lahko ima vrednost NULL, zato bo izraz SELECT null::jsonb IS NULL (opazite odsotnost enojnih narekovajev) tokrat vrnil true.

Eno črka spremeni vse

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json

                     json
                     json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}

---

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb

             jsonb
             jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}

Vse je v tem, da sta json in jsonb popolnoma različni strukturi. V json je objekt shranjen takšen, kot je, medtem ko je v jsonb shranjen že v obliki razčlenjene indeksirane strukture. Prav zato je v drugem primeru vrednost objekta pod ključem 1 bila zamenjana z [1, 2, 3] na [7, 8, 9], ki je prišla v strukturo na samem koncu z istim ključem.

Z obraza vode ne piti

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL pri implementaciji JSONB spreminja formatiranje decimalnih števil, jih privede do klasične oblike. Za tip JSON se to ne zgodi. Malo čudno, a njegova pravica.

Dosje številka štiri. datum/čas/timestamp

Pri tipih datuma/časa obstajajo tudi nekatere čudnosti. Poglejmo jih. Takoj moram povedati, da nekatere značilnosti obnašanja postanejo razumljive, če dobro razumete bistvo dela s časovnimi pasovi. A to je tudi tema za posebej članek.

Moj ti ne razumeš

SELECT '08-Jan-99'::date

ERROR:  date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.
********** Napaka **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-stanje: 22008
Nasvet: Perhaps you need a different "datestyle" setting.
Znak: 8

Zdi se, da tu ni nič nejasnega? A vseeno baza ne razume, ali smo na prvo mesto postavili leto ali dan? In se odloča, da je to 99. januar 2008, kar ji eksplozivno dela. Na splošno, pri prenosu datumov v besedilni obliki je treba zelo previdno preveriti, kako pravilno jih je baza prepoznala (zlasti analizirati parameter datestyle z ukazom SHOW datestyle), saj lahko nejasnosti v tem vprašanju prinesejo zelo visoke stroške.

Od kod si prišel takšen?

SELECT '04:05 Europe/Moscow'::time

ERROR:  nevalidna sintaksa ulaza za tip vremena: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** Greška **********
ERROR: nevalidna sintaksa ulaza za tip vremena: "04:05 Europe/Moscow"
SQL stanje: 22007
Simbol: 8

Zašto baza ne može razumeti jasno navedeno vreme? Zato što je za vremensku zonu navedena puna oznaka, a ne skraćenica, koja ima smisla samo u kontekstu datuma, jer uzima u obzir istoriju promena vremenskih zona, a ona bez datuma ne funkcioniše. I sama formulacija vremenskog stringa postavlja pitanja — šta je programer zapravo imao na umu? Zato je sve ovde logično, ako se malo zagledamo.

Šta mu nije u redu?

Zamislite situaciju. Imate polje u tabeli tipa timestamptz. Želite ga indeksirati. Ali shvatate da nije uvek opravdano graditi indeks po ovom polju zbog njegove visoke selektivnosti (gotovo sve vrednosti ovog tipa će biti jedinstvene). Dakle, odlučujete da smanjite selektivnost indeksa prebacivanjem ovog tipa u datum. I dobijate iznenađenje:

CREATE INDEX "iIdent-DateLastUpdate"
  ON public."Ident" USING btree
  (("DTLastUpdate"::date));

ERROR:  funkcije u izrazu indeksa moraju biti označene kao IMMUTABLE
********** Greška **********
ERROR: funkcije u izrazu indeksa moraju biti označene kao IMMUTABLE
SQL stanje: 42P17

U čemu je problem? U tome što se za prebacivanje tipa timestamptz u tip date koristi vrednost sistemskog parametra TimeZone, što čini funkciju prebacivanja tipa zavisnom od konfigurisanog parametra, tj. promenljivom (volatile). Takve funkcije nisu dozvoljene u indeksu. U ovom slučaju, treba jasno navesti u kojoj vremenskoj zoni se vrši prebacivanje tipa.

Kada now nije baš now

Navikli smo da now() vraća trenutni datum/vreme uzimajući u obzir vremensku zonu. Ali pogledajte sledeće upite:

START TRANSACTION;
SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

Datum/vreme se vraća isto bez obzira na to koliko je vremena prošlo od prethodnog zahteva! U čemu je stvar? U tome što now() nije trenutno vreme, već vreme početka trenutne transakcije. Stoga se unutar transakcije ne menja. Svaki zahtev koji se pokreće van okvira transakcije implicitno se obavija u transakciju, pa ne primećujemo da vreme koje se dobija jednostavnim zahtevom SELECT now(); zapravo nije trenutno… Ako želite da dobijete tačno trenutno vreme, treba da koristite funkciju clock_timestamp().

Dosije broj pet. bit

Malo je čudno

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

S koje strane treba dodavati bite u slučaju proširivanja tipa? Čini se da sleva. Ali baza ima drugačije mišljenje. Budite oprezni: ako se broj bitova ne poklapa prilikom konverzije tipa, dobićete nešto sasvim drugo nego što ste želeli. Ovo se odnosi i na dodavanje bitova s desne strane, kao i na skraćivanje bitova. Takođe s desne strane…

Dosije broj šest. Mase

Čak ni NULL nije uspeo

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

Kao normalni ljudi, vaspitani na SQL-u, očekujemo da će rezultat ovog izraza biti NULL. Ali nije to. Vraća se niz. Zašto? Zato što u ovom slučaju baza pretvara NULL u celobrojni niz i implicitno poziva funkciju array_cat. Ipak ostaje nejasno zašto ovaj 'nizovni mačak' ne nullifikuje niz. Ovo ponašanje treba samo zapamtiti.

Da rezimiramo. Čudnosti ima u izobilju. Većina od njih, naravno, nije toliko kritična da bi se govorilo o očigledno neadekvatnom ponašanju. A neka druga se objašnjavaju praktičnošću ili učestalošću njihove primene u određenim situacijama. Ali u isto vreme, ima mnogo iznenađenja. Zbog toga treba da budete svesni njih. Ako pronađete još nešto čudno ili neobično u ponašanju nekih tipova, pišite u komentarima, rado ću dopuniti postojeće dosijee.

Izvor: habr.com