A megjelenésükben semmi sem kelt gyanút. Sőt, még úgy is tűnnek, mint akik jól és régóta ismerősek számodra. De ez csak addig van így, amíg meg nem vizsgálod őket. Itt bizonyosodnak be igazi, alattomos természetük, mert úgy viselkednek, ahogy azt nem vártad. Néha olyan dolgokat produkálnak, hogy az ember haja égnek áll – például elveszítik a rájuk bízott titkos adatokat. Amikor szemtől-szembe találkozol velük, azt állítják, hogy nem ismerik egymást, holott a háttérben szorgalmasan dolgoznak egy kalap alatt. Itt az ideje, hogy végre fényt derítsünk rájuk. Tehát nézzük meg ezeket a gyanús alakokat.
A PostgreSQL adatgyűjtés, minden logikájával együtt, valóban meglepő és furcsa meglepetéseket tud okozni. Ebben a cikkben megpróbáljuk tisztázni néhány furcsaságukat, megérteni, mi okozza ezt a szokatlan viselkedést, és rájönni, hogyan kerülhetjük el a problémákat a mindennapi gyakorlatban. Az igazsághoz hozzátartozik, hogy ezt a cikket részben saját magam számára is készítettem, egyfajta kézikönyvként, amit könnyen elérhetek vitás esetekben. Ezért folyamatosan bővülni fog, ahogy új meglepetéseket fedezek fel a gyanús alakoktól. Nos, induljunk el, ti fáradt adatbázis-kutatók!
Az első dosszié: real/double precision/numeric/money
Azt gondolhatnánk, hogy a numerikus típusok a legkevésbé problémásak a meglepetések szempontjából. De nem így van. Ezért kezdjük ezekkel. Szóval...
Elfelejtettek számolni
SELECT 0.1::real = 0.1
?column?
boolean
---------
fMi a helyzet? Az a probléma, hogy a PostgreSQL a típus nélküli konstans 0.1-et double precision típusra alakítja, és megpróbálja összehasonlítani a valódi 0.1-el. Pedig ezek alapvetően különböző értékek! A lényeg a valós számok gépi memóriabeli ábrázolása. Mivel a 0.1-et nem lehet véges bináris törtként ábrázolni (ez 0.0(0011) bináris formában), a különböző pontosságú számok eltérnek egymástól, ez a különbség pedig eredményezi az „nem egyenlő” eredményt. Valójában ez egy külön cikk témája, itt nem fogok részletesebben írni róla.
Honnan ered a hiba?
SELECT double precision(1)
ERROR: szintaktikai hiba az "(" közelében
LINE 1: SELECT double precision(1)
^
********** Hiba **********
ERROR: szintaktikai hiba az "(" közelében
SQL állapot: 42601
Szimbólum: 24Sokan tudják, hogy a PostgreSQL lehetővé teszi a funkcionális típuskonverziót. Tehát nem csak a 1::int írható, hanem az int(1) is, ami egyenértékű. De ez nem igaz több szóból álló típusok esetében! Tehát, ha numerikus értéket szeretnél double precision típusra konvertálni funkcionális formában, használd a float8 típus aliasát, azaz SELECT float8(1).
Mi van az infinítesnél?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tHát úgy van! Kiderül, hogy van valami, ami nagyobb az infinítesnél, és ez a NaN! Eközben a PostgreSQL dokumentáció becsületesen ránk néz, és állítja, hogy a NaN biztosan nagyobb bármely más számnál, tehát nagyobb az infinítesnél. Ugyanez érvényes -NaN esetén is. Helló, matematikai analízis rajongók! De a számítás valós számok kontextusában működik.
A szemek kerekedése
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Még egy váratlan üdvözlet az adatbázistól. Ismét meg kell jegyezni, hogy a double precision és numeric típusok esetében eltérő kerekítések léteznek. A numeric esetében az általános kerekítés alkalmazandó, ahol a 0,5 felfelé kerekedik, míg double precision esetén a 0,5 a legközelebbi páros egész felé kerekedik.
A pénz valami különleges
SELECT '10'::money::float8
ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
^
********** Hibás **********
ERROR: cannot cast type money to double precision
SQL állapot: 42846
Jel: 19A PostgreSQL véleménye szerint a pénz nem valós szám. Néhány egyén véleménye szerint sem. Nekünk azonban meg kell jegyeznünk, hogy a money típus konvertálása csak numeric típusra lehetséges, valamint a money típusra csak numeric típust lehet konvertálni. Ezzel már lehet játszani, ahogy csak szeretnénk. De ezek már nem ugyanazok a pénzek.
Smallint és szekvenciák generálása
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.
********** Hibás **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL állapot: 42725
Útmutató: Could not choose a best candidate function. You might need to add explicit type casts.
Jel: 18A PostgreSQL nem szereti a felesleges részletezést. Milyen fura, hogy a smallint típusra alapozva egy sorozatot kérdezel? Az int, nem kevesebb! Ezért, amikor megpróbálja végrehajtani a fenti lekérdezést, az adatbázis megpróbálja a smallint-t egy másik egész típusra alakítani, és észreveszi, hogy többféle átalakítás is lehetséges. Melyik átalakítást válassza? Ezt nem tudja eldönteni, ezért hibával leáll.
Második dosszié. "char"/char/varchar/text
A karaktertípusoknál is fura dolgok vannak. Ismerkedjünk meg ezekkel is.
Micsoda trükkök ezek?
SELECT 'PÉTER'::"char"
, 'PÉTER'::"char"::bytea
, 'PÉTER'::char
, 'PÉTER'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | P | xd09fMi ez a "char" típus, ki ez a bohóc? Nincs szükségünk ilyesmire… Mert csak sima char-nak adja ki magát, még ha idézőjelek között is van. Különbözik a sima char-tól, amelyik nincs idézőjelek között, attól, hogy csak a karakterlánc reprezentációjának első bájtját adja vissza, míg a normál char az első karaktert adja vissza. A mi esetünkben az első karakter a P betű, amely unicode reprezentációban 2 bájt helyet foglal el, amit a bytea típusra való konvertálás eredménye is mutat. A "char" típus csak ennek a unicode reprezentációnak az első bájtját veszi. Akkor mire van ez a típus? A PostgreSQL dokumentáció azt mondja, hogy ez egy speciális típus, amit különleges igényekre használnak. Tehát valószínűleg nincs szükségünk rá. De nézz a szemébe, és ne tévedj el, amikor találkozol vele és különleges viselkedésével.
Felesleges szóközök. Ki a szem elől, szívből ki.
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Nézd meg a fent bemutatott példát. Szándékosan minden eredményt bytea típusra alakítottam, hogy jól látható legyen, hogy mi van ott. Hol vannak a végső szóközök a varchar(6) típusra való átalakítás után? A dokumentáció tömören azt állítja: „Amikor a karakter típusú értéket egy másik karaktertípusra alakítanak, a kitöltő szóközök eldobásra kerülnek.” Ezt a nem szeretetet meg kell jegyezni. És vedd észre, hogy ha az idézőjelek közötti karakterlánc azonnal varchar(6) típusra van alakítva, a végső szóközök megmaradnak. Ilyen csodák.
Harmadik dosszié. json/jsonb
A JSON egy külön struktúra, amely a saját életét éli. Ezért az entitásai és a PostgreSQL entitásai egy kicsit eltérnek. Íme néhány példa.
Johnson és Johnson. Érezze a különbséget
VÁLASZTO 'null'::jsonb IS NULL
?oszlop?
boolean
---------
fA lényeg az, hogy a JSON-nak van egy null entitása, amely nem azonos a PostgreSQL NULL-jával. Ugyanakkor magának a JSON-objektumnak lehet NULL értéke, ezért a SELECT null::jsonb IS NULL (figyelje meg az egyes idézőjelek hiányát) ezúttal true értéket ad vissza.
Egy betű mindent megváltoztat
VÁLASZTO '{"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]}
---
VÁLASZTO '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb
jsonb
jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}A lényeg az, hogy a json és jsonb teljesen különböző struktúrák. A json objektumot úgy tárolják, ahogy van, míg a jsonb már egy feldolgozott, indexelt struktúraként van tárolva. Ezért a második esetben az 1 kulcs alatt lévő objektum értéke [1, 2, 3] helyett [7, 8, 9] lett, amely utolsóként érkezett a struktúrába ugyanazzal a kulccsal.
A víz nem ivásra való az arcról
VÁLASZTO '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}A PostgreSQL a JSONB megvalósításánál megváltoztatja a valós számok formázását, visszahozva őket a klasszikus formára. A JSON típusnál ez nem történik meg. Ez kicsit furcsa, de ez a jog.
Négyes számú dosszié. dátum/idő/időbélyeg
A dátum/idő típusokkal is vannak furcsa dolgok. Nézzük meg őket. Azonnal megerősítem, hogy a viselkedés néhány sajátossága jobban érthető, ha jól értjük az időzónák kezelésének lényegét. De ez is egy külön cikk témája.
Én nem értem a te dolgaid
VÁLASZTO '98-Jan-08'::date
HIBA: a dátum/idő mező értéke túl széles: "98-Jan-08"
1. SOR: VÁLASZTO '98-Jan-08'::date
^
JELZÉS: Talán más "dátumstílus" beállítást kellene használnia.
********** Hiba **********
HIBA: a dátum/idő mező értéke túl széles: "98-Jan-08"
SQL állapot: 22008
Jelzés: Talán más "dátumstílus" beállítást kellene használnia.
Jelkép: 8Azt gondolnánk, hogy mi itt a problémás? De a rendszer nem érti, hogy mi van az első helyen — az év vagy a nap? És úgy dönt, hogy ez 99 január 2008, ami elborítja az agyát. Általában véve, ha dátumokat szöveges formátumban adunk át, nagyon figyelmesen kell ellenőriznünk, hogy a rendszer mennyire megfelelően ismeri fel őket (különösen az SHOW datestyle parancs alkalmazásával kell elemezni), mivel a kérdésben rejlő képlékenységek nagyon drágák lehetnek.
Honnan kerültél ide?
SELECT '04:05 Európa/Moszkva'::time
ERROR: érvénytelen bemeneti szintaxis az idő típushoz: "04:05 Európa/Moszkva"
LINE 1: SELECT '04:05 Európa/Moszkva'::time
^
********** Hiba **********
ERROR: érvénytelen bemeneti szintaxis az idő típushoz: "04:05 Európa/Moszkva"
SQL állapot: 22007
Jel: 8Miért nem tudja a rendszer kezelni a kifejezetten megadott időt? Mert az időzóna megadásakor nem az rövidítés, hanem a teljes név szerepel, amely csak a dátum kontextusában értelmezhető, mivel figyelembe veszi az időzónák változásának történetét, és ez dátum nélkül nem működik. Ráadásul az időstring megfogalmazása is kérdéseket vet fel — mit is akart a programozó valójában? Így hát, ha megértjük, akkor minden logikus itt.
Miért nem megfelelő neki?
Képzelje el a helyzetet. Van egy mezője a táblájában, amelynek típusa timestamptz. Indexelni szeretné. De megérti, hogy nem mindig indokolt indexet építeni erre a mezőre a magas szelektivitása miatt (majdnem minden ilyen típusú érték egyedi lesz). Ezért úgy dönt, hogy csökkenti az index szelektivitását, ha ezt a típust dátumra alakítja. És meglepetés várja:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
ERROR: az indexkifejezésben szereplő függvényeket IMMUTABLE-ként kell megjelölni
********** Hiba **********
ERROR: az indexkifejezésben szereplő függvényeket IMMUTABLE-ként kell megjelölni
SQL állapot: 42P17Mi a probléma? Az, hogy a timestamptz típust date típussá alakítani a TimeZone rendszerparaméter értékét használja, ami azt jelenti, hogy az típust átalakító függvény a konfigurálható paraméterektől függ, tehát változékony (volatile). Az ilyen függvények nem megengedtek az indexben. Ebben az esetben világosan meg kell adni, hogy melyik időzónában történik a típusváltoztatás.
Amikor a now egyáltalán nem most
Hozzászoktunk, hogy a now() a jelenlegi dátumot/időt adja vissza az időzóna figyelembevételével. De nézze meg a következő lekérdezéseket:
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;A dátum/idő mindig ugyanaz marad, függetlenül attól, hogy mennyi idő telt el az előző kérdés óta! Miért van ez? Azért, mert a now() nem az aktuális idő, hanem a jelen tranzakció kezdőidőpontja. Ezért tranzakción belül nem változik. Bármely kérés, ami tranzakción kívül fut, implicit módon tranzakcióba kerül, ezért nem is észleljük, hogy a SELECT now(); parancs által megadott idő valójában nem a jelenlegi... Ha pontosan aktuális időt szeretne kapni, használja a clock_timestamp() függvényt.
Ötödik dosszié. bit
Kicsit furcsa
SELECT '111'::bit(4)
bit
bit(4)
------
1110Milyen irányból kell a biteket hozzáadni a típus kiterjesztésekor? Úgy tűnik, hogy balról. De a bázisnak ezzel kapcsolatban más véleménye van. Legyen óvatos: ha a számjegyek száma nem egyezik a típuskonverzió során, egészen mást kap, mint amit szeretett volna. Ez vonatkozik akként is, ha biteket ad hozzá jobbról, és a bitek rövidítésére is. Szintén jobbról...
Hatodik dosszié. Tömbök
Még NULL sem lőtt
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Mint normális emberek, akik SQL-en nevelkedtek, azt várnánk, hogy ennek a kifejezésnek az eredménye NULL-t fog adni. De nem így történt. Egy tömböt ad vissza. Miért? Mert ebben az esetben az adatbázis a NULL-t egy egész számú tömbbé konvertálja, és implicit módon meghívja az array_cat függvényt. De még mindig nem világos, hogy miért nem nullázza le ez a "tömbmacska" a tömböt. Ezt a viselkedést is csak meg kell jegyezni.
Összefoglalva. Elég sok furcsaság van. A legtöbb persze nem eléggé kritikus ahhoz, hogy feltűnő, nem megfelelő viselkedésről beszéljünk. Mások a használhatóság vagy a különböző helyzetekben való gyakoriság miatt magyarázhatók. De ugyanakkor sok meglepetés is van. Ezért tudnia kell róluk. Ha bármi furcsát vagy szokatlant tapasztal bármely típussal kapcsolatban, kérem, írja meg a megjegyzésekben, szívesen kiegészítem a meglévő dossziékat.
Forrás: habr.com
