Kahtlased tüübid

Nende välimuses ei kahtle sa milleski. Veelgi enam, nad näivad sulle hästi ja ammu tuttavad. Kuid see on alles seni, kuni sa neid ei kontrolli. Siin nad näitavadki oma petlikku olemust, käitudes sootuks teistmoodi, kui sa ootasid. Mõnikord juhtub midagi sellist, et juuksed tõusevad püsti — näiteks kaotavad nad usaldatud saladusandmed. Kui sa teed neile otsetee, väidavad nad, et nad ei tunne üksteist, kuigi varjus teevad nad usinalt tööd üheskoos. On aeg nad lõpuks päevavalgele tuua. Läheme siis koos nende kahtlaste tüüpide kallale.

Andmete tüpiseerimine PostgreSQL-is, hoolimata oma loogilisusest, toob tõepoolest aeg-ajalt välja väga kummalisi üllatusi. Selles artiklis püüame selgitada mõningaid nende veidrustest, mõista nende kummalist käitumist ja leida viise, kuidas igapäevaelus probleemide vältimiseks. Tõsi, koostasin selle artikli ka teatud mõttes enese jaoks, kuna see toimib juhendina, millele oleks kerge keerulistes olukordades viidata. Seetõttu täiendame seda artiklit igal ajal, kui avastame uusi üllatusi kahtlastelt tüüpide poolt. Nii et teele, väsimatuid andmebaaside uurijaid!

Dossier number üks. real/double precision/numeric/money

Näiliselt on arvutüübid probleemide poolest vähem üllatavad. Kuid mitte sugugi. Seetõttu alustamegi neist. Nii et…

Oleme unustanud arvutada

SELECT 0.1::real = 0.1

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

Mis toimub? Asi on selles, et PostgreSQL tõlgib tüübita konstanti 0.1 tüüpi double precision ja üritab seda võrrelda 0.1 tüüpi real-iga. Need on täiesti erinevad väärtused! Probleem on tegelikult reaalarvude esitamises masinamuistis. Kuna 0.1 ei saa esitada lõpliku binaardüraadina (see on 0.0(0011) binaarsete näidistega), siis erineva täpsusega numbrid erinevad omavahel, seega ka tulemus, et nad pole võrdsed. Üldiselt on see teema eraldi artikli vääriline, rohkem ei kirjuta siin.

Kust tuleb viga?

SELECT double precision(1)

ERROR: süntaksiviga lähimast "("
LINE 1: SELECT double precision(1)
                               ^
********** Viga **********
ERROR: süntaksiviga lähimast "("
SQL-i olek: 42601
Sümbol: 24

Paljud teavad, et PostgreSQL lubab funktsionaalset tüübi konverteerimist. See tähendab, et saab kirjutada mitte ainult 1::int, vaid ka int(1), mis on ekvivalentne. Kuid see ei kehti tüüpide puhul, mille nimed koosnevad mitmest sõnast! Seetõttu, kui soovite pöörata numbrilise väärtuse tüübiks double precision funktsionaalses vormis, kasutage selle tüübi aliast float8, see tähendab SELECT float8(1).

Mis on suurem kui lõpmatus?

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

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

Nii et see on nii! Selgub, et on midagi, mis on suurem kui lõpmatus, ja see on NaN! Sellega nõustub PostgreSQL dokumentatsioon ausate silmadega, väites, et NaN on põhimõtteliselt suurem igast teisest numbrist ning seega ka lõpmatusest. Vastupidine kehtib ka -NaN-i puhul. Tere, matemaatika sõbrad! Kuid tuleb meeles pidada, et kõik see kehtib reaalnumberite kontekstis.

Silmade ümardamine

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

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

Veel üks ootamatu tere andmebaasilt. Ja taas tuleb meeles pidada, et tüüpide double precision ja numeric jaoks kehtivad erinevad ümardamisreeglid. Numeric puhul kehtib tavaline, kus 0,5 ümardatakse ülespoole, samas kui double precision puhul toimub 0,5 ümardamine lähima paarisarvuni.

Raha on midagi erilist

SELECT '10'::money::float8

ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Viga **********
ERROR: cannot cast type money to double precision
SQL-state: 42846
Symbol: 19

PostgreSQL-i arvates ei ole raha reaalne number. Mõned inimesed arvavad samuti. Peame siiski meeles pidama, et raha tüüp on võimalik pöörata ainult tüübiks numeric, samamoodi on raha tüübiks võimalik pöörata ainult numeric tüüpi. Ja sellega saab juba mängida, nagu hing ihaldab. Kuid need ei ole enam need rahad.

Smallint ja sekveneerimise genereerimine

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.
********** Viga **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-state: 42725
Näpunäide: Could not choose a best candidate function. You might need to add explicit type casts.
Symbol: 18

PostgreSQL ei armasta pisiasjadega tegeleda. Millised järjestused põhinevad smallint'il? int, vähemalt! Seetõttu, kui proovite täita ülaltoodud päringut, püüab andmebaas väikse arvu tüüpida muuks täisarvutüübi, ja näeb, et selliseid tüpplahtteid võib olla mitu. Millist tüpplahtet valida? Andmebaas ei oska seda otsustada ja seetõttu kukub see veaga kokku.

Fail number kaks. «char»/char/varchar/text

Iseloomulikke kummalisusi leidub ka sümbolitüüpides. Alustame nende tutvustamist.

Mis asja see on?

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

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

Mis asi on «char» tüüp, kes see kloun on? Me ei vaja selliseid... Sest ta teeseldab tavalisest char'ist, kuigi on jutumärkides. Erinevus tavalisest char'ist, mis ei ole jutumärkides, on see, et see väljastab ainult esimese baitide stringi esindusest, samas kui tavaline char väljastab esimese sümboli. Meie juhul on esimene sümbol täht П, mille unicode'i esindus võtab 2 baitide, nagu näitab tulemuse konverteerimine tüübiks bytea. «char» tüüp haarab ainult selle unicode'i esinduse esimese byte'i. Miks see tüüp vajalik on? PostgreSQL'i dokumentatsioon ütleb, et see on eriline tüüp, mida kasutatakse erilistel vajadustel. Nii et me tõenäoliselt ei vaja seda. Kuid vaata talle silma ja ärge eksige, kui kohtate teda tema erilise käitumisega.

Liigne tühik. Silmast välja, südamest kaugemale.

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

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

Vaadake antud näidet. Ma tõin kõik tulemused teadlikult tüübiks bytea, et oleks selgelt näha, mis seal on. Kus on lõpus tühikud pärast üleminekut tüübile varchar(6)? Dokumentatsioon väidab napilt: «Kui väärtus muudetakse sümbolitüübist teiseks, siis lisatud tühikud kukuvad ära». Seda mitte-armastust tuleb meelde jätta. Ja märkige, et kui stringi konstant jutumärkides muudetakse kohe tüübiks varchar(6), siis lõpp-tühikud säilitavad.

Fail number kolm. json/jsonb

JSON on eraldi struktuur, mis elab oma elu. Seetõttu erinevad tema üksused ja PostgreSQL'i üksused veidi. Siin on mõned näited.

Johnson ja Johnson. Tunne erinevus

SELECT 'null'::jsonb IS NULL

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

Asi on selles, et JSON-il on oma null-käsk, mis ei ole PostgreSQL-i NULL-i analoog. Samas võib JSON-objekt ise omada väärtust NULL, seega väljund SELECT null::jsonb IS NULL (pange tähele, et ühtegi ühtegi tühikute märki ei ole) tagastab seekord true.

Üks täht muudab kogu

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

Asi on selles, et json ja jsonb on täiesti erinevad struktuurid. json-is säilitatakse objekt nii nagu ta on, samas kui jsonb-s salvestatakse see juba indekseeritud struktuurina. Just seetõttu, teises variandis, muudeti objekti väärtus võtmega 1 väärtuseks [1, 2, 3] [7, 8, 9], mis tuli strukturi lõpus sama võtmega.

Vee näolt ei joo

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

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

PostgreSQL JSONB rakenduses muudab realiseeritud reaalsete arvude vorming, viies need klassikalise kujuga. JSON-tüübi puhul seda ei juhtu. Veidi kummaline, kuid see on nii.

Toimiku number neli. kuupäev/aeg/aeg

Kuupäeva/aegade tüüpidega on samuti teatud kummalisi asju. Vaatame neid. Juba ette öeldes, et mõned käitumise eripärad saavad selgemaks, kui mõistetakse hästi, kuidas ajavöönditega töötamine toimib. Kuid see on ka teema eraldi artikli jaoks.

Minu su sinupooli ei mõista

SELECT '08-Jan-99'::date

ERROR:  kuupäev/aeg väli väärtus välja vahemik: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Võibolla on teil vaja erinevat "datestyle" seadistust.
********** Viga **********
ERROR: kuupäev/aeg väli väärtus välja vahemik: "08-Jan-99"
SQL-seisund: 22008
Viide: Võibolla on teil vaja erinevat "datestyle" seadistust.
Sümbol: 8

Tundub, et mis siin segadust tekitab? Kuid siiski ei mõista andmebaas, mida me siia esiteks seadsime — aasta või päev? Ja otsustab, et see on 99. jaanuar 2008, mis paneb selle aju plahvatama. Üldiselt tuleb kuupäevi tekstiformaadis edastamisel väga ettevaatlik olla, kui õigesti andmebaas neid tuvastab (eriti analüüsida parameetrit datestyle käsuga SHOW datestyle), kuna selles küsimuses võivad ebaselgused olla väga kallid.

Kust sa selline tulid?

VALI '04:05 Euroopa/Moskva'::aeg

VIGA:  vale sisendi süntaks tüübi jaoks aeg: "04:05 Euroopa/Moskva"
REALI 1: VALI '04:05 Euroopa/Moskva'::aeg
               ^
********** Viga **********
VIGA: vale sisendi süntaks tüübi jaoks aeg: "04:05 Euroopa/Moskva"
SQL-olek: 22007
Sümbol: 8

Miks andmebaas ei saa arusaadavalt määratud aega mõista? Sest ajavööndile on antud mitte lühend, vaid täielik nimetus, millel on tähendus ainult kuupäeva kontekstis, kuna see arvestab ajavööndite ajaloo muutusi, ja see ei toimi ilma kuupäevata. Ja ka aegade vormul tekitab küsimusi - mida programmeerija tegelikult silmas pidas? Seega on siin kõik loogiline, kui süveneda.

Mis tal viga on?

Kujutage ette olukorda. Teie tabelis on väli tüübiga timestamptz. Soovite seda indekseerida. Kuid mõistate, et selle välja järgi indeksi loomine ei ole alati õigustatud, arvestades selle kõrget valikulisust (peaaegu kõik selle tüüpi väärtused on unikaalsed). Seega otsustate vähendada indeksi valikulisust, muutes selle tüübi kuupäevaks. Ja saate üllatuse:

LOO INDEKS "iIdent-DateLastUpdate"
  PUBLIC."Ident" PEAL kasutades btree
  (("DTLastUpdate"::kuupäev));

VIGA:  funktsioonid indeksivalemis peavad olema märgistatud IMMUTABLE
********** Viga **********
VIGA: funktsioonid indeksivalemis peavad olema märgistatud IMMUTABLE
SQL-olek: 42P17

Mis toimub? Asi on selles, et tüübi timestamptz muutmiseks tüübiks kuupäev kasutatakse süsteemi parameetri TimeZone väärtust, mis muudab tüübimuundamise funktsiooni sõltuvaks konfigureeritavast parameetrist, st muutlikust (volatile). Sellised funktsioonid indeksis ei ole lubatud. Sellisel juhul tuleb selgelt määrata, millises ajavööndis tüübimuundamine toimub.

Kui now ei ole sugugi now

Oleme harjunud, et now() tagastab praeguse kuupäeva/aega, arvestades ajavööndit. Kuid vaadake järgmisi päringuid:

ALUSTA TRANSAKTSIOONI;
VALI now();

            now
  timestamp koos ajavööndiga
-----------------------------
2019-11-26 13:13:04.271419+03

...

VALI now();

            now
  timestamp koos ajavööndiga
-----------------------------
2019-11-26 13:13:04.271419+03

...

VALI now();

            now
  timestamp koos ajavööndiga
-----------------------------
2019-11-26 13:13:04.271419+03

KOMMITTEERI;

Kuupäev/aeg tagastatakse alati sama, olenemata sellest, kui kaua on möödunud eelmisest päringust! Miks? Sest now() ei ole praegune aeg, vaid jooksva tehingu algusaeg. Seetõttu ei muutu see tehingu raames. Iga päring, mis käivitatakse tehingu raames, ümbritsetakse vaikimisi tehinguga, mistõttu me ei pane tähele, et lihtne päring SELECT now(); tagastab tegelikult mitte praeguse aja... Kui soovite saada tõelist praegust aega, peate kasutama funktsiooni clock_timestamp().

Dossier number viis. bit

Veidi kummaline

SELECT '111'::bit(4)

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

Mis küljest tuleks bitti lisada tüübi suurendamisel? Tundub, et vasakult. Kuid ainult andmebaasil on selle kohta teine arvamus. Olge ettevaatlik: kui tüübi konverteerimisel on bittide arvud vale, saate midagi muud, kui soovisite. See kehtib nii bittide lisamise kui ka bittide kärpimise kohta. Samuti paremal...

Dossier number kuus. Massiivid

I isegi NULL ei tulnud välja

SELECT ARRAY[1, 2] || NULL

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

Nagu normaalsed inimesed, kellel on SQL-alused, ootame, et selle avaldise tulemuseks on NULL. Kuid ei. Tagastatakse massiiv. Miks? Sest antud juhul teisendab andmebaas NULL'i täisarvude massiiviks ja kutsub vaikimisi üles foonktsiooni array_cat. Kuid jääb ebaselgeks, miks see "massiivne kass" massiivi ei nulli. Sellise käitumise mäletamine on oluline.

Kokkuvõttes. Kummalisi asju on palju. Enamiku nende seas ei ole need piisavalt kriitilised, et rääkida šokeerivast mitteadekvaatsest käitumisest. Teised on selgitatavad mugavuse kasutamise või nende rakendamise sagedusega erinevates olukordades. Kuid samas on palju üllatusi. Seetõttu tuleb neist teada. Kui leiate veel mingeid kummalisi või ebatavalisi käitumisi mingite tüüpide seas, kirjutage kommentaaridesse, täiendan hea meelega olemasolevaid dossiersid.

Allikas: habr.com