Sumnjivi tipovi

Na njihovom vanjskom izgledu ništa ne pobuđuje sumnju. Štoviše, čak ti se čine dobro i davno poznatim. No, to je sve dok ih ne provjeriš. E tu će se pokazati njihova podla suština, radeći potpuno drugačije nego što si očekivao. Ponekad iznenade na način da ti kosa na glavi stane — na primjer, gube povjerene im tajne podatke. Kada im postaviš direktno pitanje, tvrde da se ne poznaju, iako u sjeni marljivo rade pod jednim krovom. Vrijeme je da ih konačno razotkrijemo. Hajde da se i mi suočimo s ovim sumnjivim tipovima.

Tipizacija podataka u PostgreSQL-u, unatoč svojoj logičnosti, zaista ponekad donosi vrlo čudne iznenađenja. U ovom članku ćemo pokušati razjasniti neke njihove osobitosti, otkriti uzrok njihovog čudnog ponašanja i razumjeti kako izbjeći probleme u svakodnevnoj praksi. Istini za volju, ovo sam sastavio i kao svojevrsni priručnik za sebe, priručnik kojem se lako možeš obratiti u sporovima. Stoga će se dopunjavati dok otkrivam nova iznenađenja od sumnjivih tipova. Idemo, neumorni istraživači baza podataka!

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

Činilo bi se da su numerički tipovi najmanje problematični s obzirom na iznenađenja u ponašanju. No, nije tako. Zato krećemo od njih. Pa…

Zaboravili smo kako se računa

SELECT 0.1::real = 0.1

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

Šta je problem? Problem je u tome što PostgreSQL pretvara netipiziranu konstatu 0.1 u tip double precision i pokušava je usporediti 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 se 0.1 ne može predstaviti kao konačna binarna frakcija (to će biti 0.0(0011) u binarnom obliku), brojevi s različitim razredima se razlikuju, odatle i rezultat da nisu jednaki. Uopšteno govoreći, to je tema za poseban članak, ovdje neću detaljno pisati.

Odakle greška?

SELECT double precision(1)

GREŠKA:  sintaktička greška u blizini "("
LINE 1: SELECT double precision(1)
                               ^
********** Greška **********
GREŠKA: sintaktička greška u blizini "("
SQL-stanje: 42601
Simbol: 24

Mnogi znaju da PostgreSQL dopušta funkcionalno predstavljanje tipova. To znači da možete napisati ne samo 1::int, već i int(1), što je ekvivalentno. Ali ne za tipove čija se imena sastoje od više reči! Zato, ako želite da konvertujete numeričku vrednost u tip double precision funkcionalno, koristite alias ovog tipa float8, dakle SELECT float8(1).

Šta je više od beskonačnosti?

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

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

Eto kako! Ispostavlja se da postoji nešto veće od beskonačnosti, a to je NaN! Pri tome, dokumentacija PostgreSQL se pošteno gleda u oči i tvrdi da je NaN poznato veće od bilo kog drugog broja, a samim tim i od beskonačnosti. Oba su ista i za -NaN. Pozdrav ljubiteljima matematičke analize! Ali treba imati u vidu da sve ovo važi u kontekstu realnih brojeva.

Zaokruživanje očiju

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

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

Još jedan neočekivan pozdrav iz baze. I ponovo treba zapamtiti da za tipove double precision i numeric važe različita pravila zaokruživanja. Za numeric važi uobičajeno pravilo gde se 0,5 zaokružuje na veću stranu, dok se za double precision 0,5 zaokružuje na najbliži paran broj.

Novac je nešto posebno

SELECT '10'::money::float8

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

Prema mišljenju PostgreSQL-a, novac nije realan broj. Prema mišljenju nekih pojedinaca, takođe. Moramo imati na umu da je konverzija tipa money moguća samo u tip numeric, a tip money se može konvertovati samo u tip numeric. Ali tada se njime može manipulisati kako god želite. No to više neće biti pravi novac.

Smallint i generisanje sekvenci

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.
********** Greška **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL stanje: 42725
Sugestija: Could not choose a best candidate function. You might need to add explicit type casts.
Simbol: 18

PostgreSQL ne voli sitničarenje. Kakve to sekvence na osnovu smallint? int, bar! Zato prilikom pokušaja izvršenja prethodnog upita, baza pokušava da konvertuje smallint u neki drugi celobrojni tip i vidi da može biti više takvih konverzija. Koju konverziju izabrati? To ne može da odluči, i zato pada sa greškom.

Dosije broj dva. «char»/char/varchar/text

Nekih čudnovatih stvari ima i kod tipova karaktera. Hajde da se upoznamo s njima.

Šta je ovo za trikove?

SELECT 'PETYA'::"char"
     , 'PETYA'::"char"::bytea
     , 'PETYA'::char
     , 'PETYA'::char::bytea

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

Šta je to za tip «char», ko je taj clown? Takvi nam ne trebaju… Zato što on se pretvara u običan char, samo što je u navodnicima. A razlikuje se od običног char, koji je bez navodnika, po tome što ispisuje samo prvi bajt string prikaza, dok normalan char ispisuje prvi znak. U našem slučaju, prvi znak je slovo P, koje u unicode prikazu zauzima 2 bajta, o čemu svedoči konverzija rezultata u tip bytea. A tip «char» uzima samo prvi bajt tog unicode prikaza. Pa zašto je ovaj tip potreban? PostgreSQL dokumentacija kaže da je to poseban tip, korišćen za posebne potrebe. Tako da nam verovatno neće biti potreban. Ali pogledajte ga u oči i ne napravite grešku kada ga sretnete s njegovim posebnim ponašanjem.

Višak razmaka. Daleko od očiju, daleko od srca

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

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

Pogledajte dati primer. Namerno sam sve rezultate konvertovao u tip bytea, kako bi bilo jasno šta se nalazi unutra. Gde su viškovi razmaka nakon konverzije u tip varchar(6)? Dokumentacija kratko tvrdi: «Prilikom konverzije vrednosti karaktera u drugi simoblični tip, dodatni prostori se odbacuju». Ovo neprijateljstvo treba zapamtiti. I primetite da ukoliko je string konstanta u navodnicima odmah konvertovana u tip varchar(6), krajnji prostori se čuvaju. Takva čuda.

Dosije broj tri. json/jsonb

JSON — posebna struktura koja živi svojim životom. Zato se njene entitete i entiteti PostgreSQL-a malo razlikuju. Evo primera.

Johnson & Johnson. Osjetite razliku

SELECT 'null'::jsonb IS NULL

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

Cijela stvar je u tome što JSON ima svoju null suštinu koja nije ekvivalent NULL-u u PostgreSQL-u. Istovremeno, sam JSON objekat može imati null vrijednost, pa će izraz SELECT null::jsonb IS NULL (obratite pažnju na nedostatak jednostrukih navodnika) ovoga puta vratiti true.

Jedno slovo mijenja sve

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]}

Cijela stvar je u tome što su json i jsonb potpuno različite strukture. U json-u objekat se čuva onako kako jeste, dok se u jsonb-u čuva već u obliku analizirane indeksirane strukture. Zbog toga je u drugom slučaju vrijednost objekta pod ključem 1 zamijenjena iz [1, 2, 3] u [7, 8, 9], koja je došla u strukturu na kraju s istim ključem.

S lica 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 u implementaciji JSONB mijenja formatiranje decimalnih brojeva, dovodeći ih u klasični izgled. Za tip JSON to se ne događa. Pomalo je to čudno, ali je to njegovo pravo.

Dosije broj četiri. datum/vreme/timestamp

Sa tipovima datuma/vremena također postoje određene čudnosti. Pogledajmo ih. Odmah da napomenem, neke od osobina ponašanja postaju jasnije ako dobro razumijete suštinu rada sa vremenskim zonama. No to je također tema za poseban članak.

Moja tvoja ne razumjeti

SELECT '08-Jan-99'::date

ERROR:  date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Možda vam treba drugačija "datastyle" postavka.
********** Greška **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL stanje: 22008
Sugestija: Možda vam treba drugačija "datastyle" postavka.
Simbol: 8

Činilo bi se, što tu nije jasno? Ali baza ne shvata da smo ovdje stavili na prvo mesto — godinu ili dan? I smatra da je to 99. januar 2008. godine, što joj izaziva probleme. U svakom slučaju, prilikom prijenosa datuma u tekstualnom formatu treba vrlo pažljivo provjeriti koliko je ispravno baza prepoznala (posebno analizirati parametar datestyle komandama SHOW datestyle), jer nejasnoće u ovom pitanju mogu skupo koštati.

Odakle si se ti uzeo?

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

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

Zašto baza ne može razumjeti jasno navedeno vrijeme? Zato što je za vremensku zonu navedena ne abrevijacija, već puno ime, koje ima smisla samo u kontekstu datuma, jer uzima u obzir istoriju promjena vremenskih zona, a ona ne funkcioniše bez datuma. I sama formulacija vremenske niza postavlja pitanja — šta je zapravo mislila programer? Stoga tu sve ima logiku, ako se malo zagledate.

Šta mu nije u redu?

Zamislite situaciju. Imate polje u tabeli tipa timestamptz. Želite ga indeksirati. Ali shvatate da graditi indeks po ovom polju nije uvijek opravdano zbog njegove visoke selektivnosti (gotovo sve vrijednosti ovog tipa će biti jedinstvene). Zato odlučujete smanjiti selektivnost indeksa, povodeći se za ovim tipom do datuma. 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 konverziju tipa timestamptz u tip date koristi vrijednost sistemskog parametra TimeZone, što čini funkciju konverzije tipa zavisnom od konfigurabilnog parametra, tj. promjenljivom (volatile). Takve funkcije nisu dopuštene u indeksu. U ovom slučaju treba jasno naznačiti u kojoj vremenskoj zoni se vrši konverzija tipa.

Kada now nije baš now

Navikli smo da now() vraća trenutni datum/vrijeme uzimajući u obzir vremensku zonu. Ali pogledajte sljedeć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 vremena je prošlo od prethodnog zahteva! Šta je problem? Problem je 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 izvan okvira transakcije implicitno se obavija u transakciju, zbog čega ne primećujemo da vreme koje daje običan zahtev SELECT now(); zapravo nije trenutno... Ako želite da dobijete tačno trenutno vreme, trebate koristiti funkciju clock_timestamp().

Dosije broj pet. bit

Pomalo čudno

SELECT '111'::bit(4)

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

S koje strane bi trebalo dodavati bite u slučaju proširenja tipa? Čini se da je s leva. Ali samo baza ima drugačije mišljenje o tome. Budite oprezni: kad se broj bitova ne poklapa prilikom prevođenja tipa, dobićete nešto sasvim drugo od onoga što ste želeli. To se odnosi i na dodavanje bitova s desna, kao i na skraćivanje bitova. Takođe s desna...

Dosije broj šest. Aritmijske operacije

Čak ni NULL nije uspeo

SELECT ARRAY[1, 2] || NULL

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

Kao normalni ljudi, odgajeni na SQL-u, očekujemo da će rezultat ovog izraza biti NULL. Ali nije baš tako. Vraća se niz. Zašto? Zato što baza u ovom slučaju pretvara NULL u celobrojni niz i implicitno poziva funkciju array_cat. Ali se i dalje postavlja pitanje zašto taj "nizovni mačak" ne postavlja niz na nulu. Takvo ponašanje treba jednostavno zapamtiti.

Zaključimo. Ima mnogo čudnosti. Većina njih, naravno, nije toliko kritična da bismo govorili o očigledno neadekvatnom ponašanju. A druge se objašnjavaju praktičnošću ili učestalošću njihove primene u određenim situacijama. Međutim, istovremeno ima mnogo iznenađenja. Zato treba o njima znati. Ako nađete još nešto čudno ili neuobičajeno u ponašanju nekih tipova, pišite u komentarima, rado ću dopuniti postojeće dosije.

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster