ProHoster > Blog > Administrácia > Podozrivé typy

Podozrivé typy

Na ich vzhľade nie je nič podozrivé. Navyše sa vám dokonca zdajú dobre a dlho známe. Ale to je len dovtedy, kým ich neskontrolujete. Tu ukazujú svoju zákernú povahu, fungujúcu úplne inak, ako ste čakali. A občas urobia niečo, z čoho sa vám zježia vlasy dupkom – napríklad prídu o tajné údaje, ktoré im boli zverené. Keď ich konfrontujete, tvrdia, že sa nepoznajú, hoci v tieni usilovne pracujú pod jednou kapotou. Je čas ich konečne priviesť k čistej vode. Poďme sa zaoberať aj týmito podozrivými typmi.

Zadávanie údajov v PostgreSQL pri všetkej svojej logike niekedy predstavuje veľmi zvláštne prekvapenia. V tomto článku sa pokúsime objasniť niektoré z ich vrtochov, pochopiť dôvod ich podivného správania a pochopiť, ako sa nestretnúť s problémami v každodennej praxi. Pravdupovediac, tento článok som zostavil aj ako akúsi príručku pre seba, príručku, na ktorú by sa dalo ľahko odkazovať v kontroverzných prípadoch. Preto sa bude dopĺňať, keď sa objavia nové prekvapenia od podozrivých typov. Takže, poďme, oh neúnavní sledovači databáz!

Dokument číslo jedna. reálna/dvojitá presnosť/numerická/peniaze

Zdalo by sa, že numerické typy sú z hľadiska prekvapení v správaní najmenej problematické. Ale bez ohľadu na to, ako to je. Začnime teda nimi. Takže…

Zabudol som počítať

SELECT 0.1::real = 0.1

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

Čo sa deje? Problém je v tom, že PostgreSQL konvertuje netypizovanú konštantu 0.1 na dvojnásobnú presnosť a pokúsi sa ju porovnať s 0.1 skutočného typu. A to sú úplne iné významy! Cieľom je reprezentovať reálne čísla v pamäti stroja. Keďže 0.1 nemožno reprezentovať ako konečný binárny zlomok (v dvojkovej sústave by to bolo 0.0(0011), čísla s rôznymi číslicami sa budú líšiť, výsledkom čoho je, že nie sú rovnaké. Vo všeobecnosti je to téma na samostatný článok, nebudem tu písať podrobnejšie.

Odkiaľ pochádza chyba?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24

Mnoho ľudí vie, že PostgreSQL umožňuje funkčnú notáciu pre typové pretypovanie. To znamená, že môžete napísať nielen 1::int, ale aj int(1), ktoré budú ekvivalentné. Ale nie pre typy, ktorých mená pozostávajú z niekoľkých slov! Preto, ak chcete pretypovať číselnú hodnotu na typ s dvojnásobnou presnosťou vo funkčnej forme, použite alias tohto typu float8, teda SELECT float8(1).

Čo je väčšie ako nekonečno?

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

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

Pozrite sa, aké to je! Ukázalo sa, že existuje niečo väčšie ako nekonečno a je to NaN! Dokumentácia PostgreSQL sa na nás zároveň pozerá úprimnými očami a tvrdí, že NaN je zjavne väčšie ako akékoľvek iné číslo, a teda nekonečno. Opak platí aj pre -NaN. Dobrý deň, milovníci matematiky! Musíme si však uvedomiť, že toto všetko funguje v kontexte reálnych čísel.

Zaoblenie očí

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

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

Ďalší nečakaný pozdrav zo základne. Opäť nezabudnite, že dvojitá presnosť a číselné typy majú rôzne efekty zaokrúhľovania. Pre numerické - obvyklé, keď sa 0,5 zaokrúhľuje nahor, a pre dvojnásobnú presnosť - 0,5 sa zaokrúhľuje smerom k najbližšiemu párnemu celému číslu.

Peniaze sú niečo výnimočné

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19

Podľa PostgreSQL peniaze nie sú skutočné číslo. Aj podľa niektorých jednotlivcov. Musíme si uvedomiť, že typ peňazí je možné prehodiť iba na číselný typ, rovnako ako iba číselný typ možno prehodiť na typ peňazí. Teraz sa s ním však môžete hrať tak, ako si vaše srdce želá. Ale nebudú to tie isté peniaze.

Smallint a sekvenčné generovanie

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.
********** Ошибка **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-состояние: 42725
Подсказка: Could not choose a best candidate function. You might need to add explicit type casts.
Символ: 18

PostgreSQL nerád stráca čas maličkosťami. Aké sú tieto sekvencie založené na smallintu? int, nič menej! Preto pri pokuse o vykonanie vyššie uvedeného dotazu sa databáza pokúsi pretypovať smallint na nejaký iný celočíselný typ a vidí, že takýchto pretypovaní môže byť niekoľko. Aké obsadenie zvoliť? Nemôže to rozhodnúť, a preto havaruje s chybou.

Súbor číslo dva. "char"/char/varchar/text

V typoch postáv je prítomných aj množstvo zvláštností. Poďme sa s nimi zoznámiť aj my.

Čo sú to za triky?

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

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

Čo je to za "char" a čo je to za klauna? Tie nepotrebujeme... Pretože sa tvári, že je to obyčajný znak, aj keď je v úvodzovkách. A líši sa od bežného znaku, ktorý je bez úvodzoviek, v tom, že na výstup je len prvý bajt reťazcovej reprezentácie, zatiaľ čo normálny znak na výstupe prvý znak. V našom prípade je prvým znakom písmeno P, ktoré v unicode reprezentácii zaberá 2 bajty, o čom svedčí aj prevod výsledku na typ bytea. A typ „char“ zaberá iba prvý bajt tejto reprezentácie Unicode. Prečo je potom tento typ potrebný? Dokumentácia PostgreSQL hovorí, že ide o špeciálny typ používaný pre špeciálne potreby. Je teda nepravdepodobné, že to budeme potrebovať. Ale pozrite sa mu do očí a nepomýlite sa, keď ho stretnete s jeho zvláštnym správaním.

Priestory navyše. Zmizne z očí, zíde z mysle

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

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

Pozrite sa na uvedený príklad. Všetky výsledky som špeciálne previedol na typ bytea, aby bolo jasne vidieť, čo tam je. Kde sú medzery na konci po odovzdaní do varchar(6)? V dokumentácii sa stručne uvádza: "Pri prenášaní hodnoty znaku na iný typ znaku sa zahodia medzery na konci." Túto nechuť si treba pamätať. A všimnite si, že ak je reťazcová konštanta v úvodzovkách pretypovaná priamo na typ varchar(6), koncové medzery sa zachovajú. Také sú zázraky.

Spis číslo tri. json/jsonb

JSON je samostatná štruktúra, ktorá si žije vlastným životom. Preto sa jeho entity a entity PostgreSQL mierne líšia. Tu sú príklady.

Johnson a Johnson. cítiť rozdiel

SELECT 'null'::jsonb IS NULL

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

Ide o to, že JSON má svoju vlastnú nulovú entitu, ktorá nie je analógom NULL v PostgreSQL. Zároveň môže mať samotný objekt JSON hodnotu NULL, takže výraz SELECT null::jsonb IS NULL (všimnite si absenciu jednoduchých úvodzoviek) tentoraz vráti hodnotu true.

Jedno písmeno zmení všetko

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

Ide o to, že json a jsonb sú úplne odlišné štruktúry. V json je objekt uložený tak, ako je, a v jsonb je už uložený vo forme analyzovanej, indexovanej štruktúry. Preto v druhom prípade bola hodnota objektu kľúčom 1 nahradená z [1, 2, 3] na [7, 8, 9], ktorý prišiel do štruktúry úplne na konci s rovnakým kľúčom.

Nepite vodu z tváre

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

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

PostgreSQL vo svojej implementácii JSONB mení formátovanie reálnych čísel a dostáva ich do klasickej podoby. Pre typ JSON sa to nestane. Trochu zvláštne, ale má pravdu.

Spis číslo štyri. dátum/čas/časová pečiatka

Existujú aj niektoré zvláštnosti s typmi dátumu a času. Pozrime sa na ne. Dovoľte mi hneď urobiť výhradu, že niektoré funkcie správania budú jasné, ak dobre rozumiete podstate práce s časovými pásmami. Ale to je tiež téma na samostatný článok.

Moji tvoji nechápu

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.
********** Ошибка **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-состояние: 22008
Подсказка: Perhaps you need a different "datestyle" setting.
Символ: 8

Zdá sa, že čo je tu nepochopiteľné? Ale databáza stále nerozumie tomu, čo sme tu umiestnili na prvé miesto – rok alebo deň? A rozhodne sa, že je 99. január 2008, čo jej vybuchlo z hlavy. Všeobecne povedané, pri prenose dátumov v textovom formáte musíte veľmi starostlivo skontrolovať, ako správne ich databáza rozpoznala (najmä analyzovať parameter datestyle pomocou príkazu SHOW datestyle), pretože nejasnosti v tejto veci môžu byť veľmi drahé.

Odkiaľ to máš?

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

ERROR:  invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** Ошибка **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL-состояние: 22007
Символ: 8

Prečo databáza nerozumie explicitne špecifikovanému času? Časové pásmo totiž nemá skratku, ale celé meno, čo dáva zmysel len v kontexte dátumu, keďže zohľadňuje históriu zmien časových pásiem a bez dátumu to nefunguje. A už samotné znenie časovej línie vyvoláva otázky – čo tým vlastne programátor myslel? Preto je tu všetko logické, ak sa na to pozriete.

Čo s ním je?

Predstavte si situáciu. V tabuľke máte pole s typom timestamptz. Chcete ho indexovať. Chápete však, že vytvorenie indexu na tomto poli nie je vždy opodstatnené z dôvodu jeho vysokej selektivity (takmer všetky hodnoty tohto typu budú jedinečné). Rozhodnete sa teda znížiť selektivitu indexu pretypovaním typu na dátum. A čaká vás prekvapenie:

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

ERROR:  functions in index expression must be marked IMMUTABLE
********** Ошибка **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL-состояние: 42P17

Čo sa deje? Faktom je, že na pretypovanie typu časovej pečiatky na typ dátumu sa používa hodnota systémového parametra TimeZone, čím je funkcia konverzie typu závislá od vlastného parametra, t.j. nestály. Takéto funkcie nie sú v indexe povolené. V tomto prípade musíte explicitne uviesť, v ktorom časovom pásme sa typové pretypovanie vykonáva.

Keď teraz nie je ani teraz

Sme zvyknutí, že now() vracia aktuálny dátum/čas s prihliadnutím na časové pásmo. Ale pozrite sa na nasledujúce otázky:

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;

Dátum/čas sa vráti rovnaký bez ohľadu na to, koľko času uplynulo od predchádzajúcej žiadosti! Čo sa deje? Faktom je, že now() nie je aktuálny čas, ale čas začiatku aktuálnej transakcie. V rámci transakcie sa teda nemení. Akýkoľvek dotaz spustený mimo rámec transakcie je implicitne zabalený do transakcie, a preto si nevšimneme, že čas vrátený jednoduchým SELECT now(); v skutočnosti nie aktuálny... Ak chcete získať poctivý aktuálny čas, musíte použiť funkciu clock_timestamp().

Spis číslo päť. trocha

Trochu zvláštne

SELECT '111'::bit(4)

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

Na ktorú stranu treba pridať bity v prípade rozšírenia typu? Zdá sa, že je vľavo. Ale len základňa má na túto vec iný názor. Buďte opatrní: ak sa počet číslic pri hádzaní typu nezhoduje, nedostanete to, čo ste chceli. Týka sa to pridávania bitov doprava aj orezávania bitov. Tiež vpravo...

Spis číslo šesť. Polia

Dokonca ani NULL nevystrelil

SELECT ARRAY[1, 2] || NULL

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

Ako normálni ľudia vychovaní na SQL očakávame, že výsledok tohto výrazu bude NULL. Ale nebolo to tam. Vráti sa pole. prečo? Pretože v tomto prípade základ pretypuje NULL na celočíselné pole a implicitne zavolá funkciu array_cat. Stále však zostáva nejasné, prečo táto „mačka poľa“ neresetuje pole. Toto správanie si tiež treba pamätať.

Zhrnúť. Je veľa zvláštnych vecí. Väčšina z nich, samozrejme, nie je taká kritická, aby hovorila o očividne nevhodnom správaní. A iné sú vysvetlené jednoduchosťou použitia alebo frekvenciou ich použiteľnosti v určitých situáciách. Ale zároveň je tu veľa prekvapení. Preto o nich musíte vedieť. Ak sa vám zdá niečo zvláštne alebo nezvyčajné v správaní akéhokoľvek typu, napíšte do komentárov, rád doplním dokumentáciu, ktorá je na nich k dispozícii.

Zdroj: hab.com

Pridať komentár