Op 1 juli verloopt de ondersteuningstijd van Mercurial-repositories op het samenwerkingsplatform Bitbucket.

In hun uiterlijke verschijning wekt niets wantrouwen. Sterker nog, ze lijken je zelfs goed en vertrouwd. Maar dat is alleen totdat je ze controleert. Dan zullen ze hun geslepen aard tonen, en werken ze helemaal niet zoals je had verwacht. Soms doen ze zelfs dingen die je haren rechtop doen staan – bijvoorbeeld, ze verliezen vertrouwelijke gegevens die aan hen zijn toevertrouwd. Wanneer je ze confronteert, beweren ze dat ze elkaar niet kennen, terwijl ze in de schaduw hard werken onder één dak. Het is tijd om ze op hun plaats te zetten. Laten we ook eens kijken naar deze verdachte types.

Gegevenstyping in PostgreSQL, voor zover logisch, kan op sommige momenten echt vreemde verrassingen opleveren. In dit artikel zullen we proberen enkele van hun eigenaardigheden te verhelderen, de reden van hun vreemde gedrag te achterhalen en te begrijpen hoe je problemen in de dagelijkse praktijk kunt voorkomen. Om de waarheid te zeggen, heb ik dit artikel ook samengesteld als een soort naslagwerk voor mezelf, een naslagwerk waar ik gemakkelijk op kan terugvallen in betwiste gevallen. Daarom zal het worden aangevuld naarmate ik nieuwe verrassingen van verdachte types tegenkom. Dus, laten we op pad gaan, onvermoeibare speurneuzen van databases!

Dossier nummer één. real/double precision/numeric/money

Het lijkt erop dat numerieke typen de minste problemen opleveren wat betreft verrassingen in gedrag. Maar niets is minder waar. Daarom beginnen we hiermee. Dus...

Vergeten te tellen

SELECT 0.1::real = 0.1

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

Wat is het probleem? PostgreSQL converteert de niet-getypeerde constante 0.1 naar het type double precision en probeert deze te vergelijken met 0.1 van het type real. En dat zijn absoluut verschillende waarden! Het gaat om de weergave van reële cijfers in het geheugen van de machine. Aangezien 0.1 niet kan worden weergegeven als een eindige binaire breuk (dit zou 0.0(0011) in binaire vorm zijn), zullen getallen met verschillende precisie verschillen, vandaar het resultaat dat ze ongelijk zijn. Dit onderwerp verdient eigenlijk een apart artikel, daar ga ik hier niet verder op in.

Waar komt de fout vandaan?

SELECT double precision(1)

ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Fout **********
ERROR: syntax error at or near "("
SQL-status: 42601
Symbool: 24

Veel mensen weten dat PostgreSQL functionele typecasting toelaat. Dat wil zeggen, je kunt niet alleen 1::int schrijven, maar ook int(1), wat gelijkwaardig is. Maar dit geldt niet voor types met meerdere woorden in hun naam! Dus, als je een numerieke waarde naar het type double precision wilt casten op functionele wijze, gebruik de alias van dit type float8, dat wil zeggen SELECT float8(1).

Wat is groter dan oneindigheid?

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

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

Kijk eens aan! Blijkbaar is er iets dat groter is dan oneindigheid, en dat is NaN! De documentatie van PostgreSQL kijkt ons met eerlijke ogen aan en beweert dat NaN per definitie groter is dan elk ander getal, en dus ook dan oneindigheid. Het omgekeerde geldt ook voor -NaN. Hallo, wiskundige analyse liefhebbers! Maar het is belangrijk te onthouden dat dit allemaal in de context van reële getallen gebeurt.

Ogen feilloos maken

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

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

Weer een verrassende begroeting van de database. En weer moeten we onthouden dat er verschillende afrondingen gelden voor de types double precision en numeric. Voor numeric is het normaal, waarbij 0,5 naar boven wordt afgerond, en voor double precision is de afronding van 0,5 naar het dichtstbijzijnde even gehele getal.

Geld is iets bijzonders

SELECT '10'::money::float8

ERROR: kan type money niet naar double precision casten
LINE 1: SELECT '10'::money::float8
                          ^
********** Fout **********
ERROR: kan type money niet naar double precision casten
SQL-status: 42846
Symbool: 19

Volgens PostgreSQL zijn geldbedragen geen reële getallen. Ook volgens sommige individuen. We moeten onthouden dat casting van het type money alleen mogelijk is naar het type numeric, net zoals money alleen naar numeric kan worden gecast. Maar met numeric kun je dan doen wat je wilt. Maar dat zijn dan al niet meer die geldbedragen.

Smallint en sequentie generatie

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

ERROR: functie generate_series(smallint, smallint, smallint) is niet uniek
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
HINT: Kon geen beste kandidaatfunctie kiezen. Mogelijk moet je expliciete typecasts toevoegen.
********** Fout **********
ERROR: functie generate_series(smallint, smallint, smallint) is niet uniek
SQL-status: 42725
Hint: Kon geen beste kandidaatfunctie kiezen. Mogelijk moet je expliciete typecasts toevoegen.
Symbool: 18

PostgreSQL houdt niet van halfslachtig werk. Wat voor reeksen op basis van smallint? int, minstens! Daarom, wanneer je probeert de bovenstaande query uit te voeren, probeert de database smallint naar een andere gehele getaltype om te zetten, en ziet dat er meerdere conversies mogelijk zijn. Welke conversie moet gekozen worden? Dat kan ze niet beslissen, en daarom faalt het met een foutmelding.

Dossier nummer twee. "char"/char/varchar/text

Er zijn ook vreemde dingen met de stringtypes. Laten we ook daar kennis mee maken.

Wat voor trucjes zijn dit?

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

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

Wat is dit voor type "char", wat voor clown is dat? Die hebben we niet nodig... Omdat het zich voordoet als een gewone char, ondanks dat het tussen aanhalingstekens staat. Het verschilt van de gewone char, die geen aanhalingstekens heeft, omdat het alleen de eerste byte van de stringweergave weergeeft, terwijl de normale char het eerste teken weergeeft. In ons geval is het eerste teken de letter П, die in unicode-representatie 2 bytes kost, wat zichtbaar is in de conversie van het resultaat naar het type bytea. En het type "char" neemt alleen de eerste byte van die unicode-representatie. Waar is dit type dan voor nodig? De PostgreSQL-documentatie zegt dat dit een speciaal type is dat voor bijzondere behoeften wordt gebruikt. Dus waarschijnlijk hebben we het niet nodig. Maar kijk het in de ogen en vergis je niet wanneer je het tegenkomt met zijn bijzondere gedrag.

Overbodige spaties. Uit het oog, uit het hart

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

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

Kijk naar het gegeven voorbeeld. Ik heb opzettelijk alle resultaten naar het type bytea geconverteerd, zodat het duidelijk zichtbaar is wat er aanwezig is. Waar zijn de achterste spaties na conversie naar het type varchar(6)? De documentatie stelt beknopt: "Bij conversie van een characterwaarde naar een ander stringtype worden aanvullende spaties weggeworpen". Deze afkeer moet je onthouden. En merk op dat als een stringconstante tussen aanhalingstekens direct wordt geconverteerd naar het type varchar(6), de eindspaties behouden blijven. Dat zijn de wonderen.

Dossier nummer drie. json/jsonb

JSON is een aparte structuur die zijn eigen leven leidt. Daarom verschillen de entiteiten ervan iets van die van PostgreSQL. Hier zijn enkele voorbeelden.

Johnson en Johnson. Voel het verschil

SELECT 'null'::jsonb IS NULL

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

Het punt is dat JSON zijn eigen null-entiteit heeft, die niet gelijkwaardig is aan NULL in PostgreSQL. Tegelijkertijd kan het JSON-object zelf wel de waarde NULL hebben, zodat de expressie SELECT null::jsonb IS NULL (let op het ontbreken van enkele aanhalingstekens) dit keer true zal retourneren.

Één letter verandert alles

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

Het punt is dat json en jsonb volkomen verschillende structuren zijn. In json wordt het object opgeslagen zoals het is, terwijl in jsonb het al wordt opgeslagen als een geparsed geïndexeerde structuur. Daarom werd in het tweede geval de waarde van het object onder sleutel 1 vervangen van [1, 2, 3] naar [7, 8, 9], die in de structuur op het laatst met dezelfde sleutel kwam.

Je moet van water niet drinken

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

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

PostgreSQL verandert de formattering van reële getallen in de JSONB-implementatie en brengt ze naar de klassieke vorm. Voor het JSON-type gebeurt dit niet. Een beetje vreemd, maar het is hun recht.

Dossier nummer vier. datum/tijd/timestamp

Er zijn ook enkele eigenaardigheden met de datum/tijd types. Laten we ernaar kijken. Ik moet meteen zeggen dat sommige van de gedragskenmerken begrijpelijker worden als je goed begrijpt hoe je met tijdzones werkt. Maar dat is ook een onderwerp voor een apart artikel.

Mijn jou niet begrijpen

SELECT '08-Jan-99'::date

ERROR:  date/tijd veldwaarde buiten bereik: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Misschien heb je een andere "datestyle" instelling nodig.
********** Fout **********
ERROR: date/tijd veldwaarde buiten bereik: "08-Jan-99"
SQL-status: 22008
Hint: Misschien heb je een andere "datestyle" instelling nodig.
Symbool: 8

Wat is daar nu onduidelijk aan? Maar de database begrijpt niet of we hier het jaar of de dag als eerste hebben gezet. En ze besluit dat dit 99 januari 2008 is, wat haar hoofd explodeert. Over het algemeen, wanneer je datums in tekstformaat doorgeeft, moet je heel voorzichtig controleren hoe goed de database ze herkent (in het bijzonder het datestyle parameter analyseren met de SHOW datestyle command), omdat ambiguïteiten hierin heel duur kunnen zijn.

Waar kom jij vandaan?

SELECT '04:05 Europa/Moskou'::time

ERROR: ongeldige invoersyntaxis voor type time: "04:05 Europa/Moskou"
LINE 1: SELECT '04:05 Europa/Moskou'::time
               ^
********** Fout **********
ERROR: ongeldige invoersyntaxis voor type time: "04:05 Europa/Moskou"
SQL-status: 22007
Symbool: 8

Waarom kan de database het expliciet opgegeven tijdstip niet begrijpen? Omdat voor de tijdzone niet de afkorting is opgegeven, maar de volledige naam, die alleen zinvol is in de context van een datum, aangezien deze rekening houdt met de geschiedenis van tijdzonewijzigingen, en dat werkt niet zonder datum. En ook de formulering van de tijdstring roept vragen op — wat bedoelde de programmeur eigenlijk? Dus, als je het goed bekijkt, is alles logisch.

Wat is er met hem aan de hand?

Stel je de situatie voor. Je hebt een kolom in je tabel met het type timestamptz. Je wilt deze indexeren. Maar je beseft dat het niet altijd zinvol is om op dit veld een index te bouwen vanwege de hoge selectiviteit (bijna alle waarden van dit type zullen uniek zijn). Daarom besluit je de selectiviteit van de index te verlagen door dit type naar een datum om te zetten. En je krijgt een verrassing:

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

ERROR: functies in de indexexpressie moeten als IMMUTABLE zijn gemarkeerd
********** Fout **********
ERROR: functies in de indexexpressie moeten als IMMUTABLE zijn gemarkeerd
SQL-status: 42P17

Wat is er aan de hand? Dat is omdat voor het omzetten van het type timestamptz naar het type date de waarde van de systeemeigenschap TimeZone wordt gebruikt, waardoor de type-conversiefunctie afhankelijk is van een configureerbare parameter, d.w.z. veranderlijk (volatile). Dergelijke functies zijn niet toegestaan in een index. In dit geval moet je expliciet aangeven in welke tijdzone de type-conversie plaatsvindt.

Wanneer now helemaal geen now is

We zijn gewend dat now() de huidige datum/tijd retourneert rekening houdend met de tijdzone. Maar kijk eens naar de volgende query's:

START TRANSACTION;
SELECT now();

            now
  timestamp met tijdzone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp met tijdzone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp met tijdzone
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

De datum/tijd blijft hetzelfde, ongeacht hoe lang het is sinds de vorige aanvraag! Wat is er aan de hand? Dat now() geen huidige tijd is, maar de tijd van het begin van de huidige transactie. Daarom verandert het niet binnen de transactie. Elke aanvraag die buiten de transactie wordt uitgevoerd, wordt impliciet in een transactie gewikkeld, daarom merken we niet dat de tijd die wordt gegeven door een eenvoudige SELECT now(); eigenlijk niet de huidige is… Als je de echte huidige tijd wilt krijgen, moet je de functie clock_timestamp() gebruiken.

Dossier nummer vijf. bit

Een beetje vreemd

SELECT '111'::bit(4)

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

Van welke kant moeten de bits worden toegevoegd bij het uitbreiden van het type? Het lijkt erop dat van links. Maar de database heeft hier een andere mening over. Wees voorzichtig: als het aantal bits niet overeenkomt bij type-conversie, krijg je absoluut niet wat je wilde. Dit geldt zowel voor het toevoegen van bits aan de rechterkant als voor het inkorten van bits. Ook aan de rechterkant...

Dossier nummer zes. Arrays

Zelfs NULL schoot niet omhoog

SELECT ARRAY[1, 2] || NULL

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

Als normale mensen, opgevoed met SQL, verwachten we dat het resultaat van deze expressie NULL zal zijn. Maar dat is niet het geval. Er komt een array terug. Waarom? Omdat de database in dit geval NULL naar een integer-array converteert en impliciet de functie array_cat aanroept. Maar het blijft onduidelijk waarom deze "array kat" de array niet op nul zet. Dit gedrag moet gewoon worden onthouden.

Laten we samenvatten. Er zijn genoeg vreemde dingen. De meeste zijn natuurlijk niet zo kritisch dat we kunnen spreken van schandalig ongepast gedrag. Andere worden verklaard door gebruiksgemak of de frequentie van toepassing in bepaalde situaties. Maar aan de andere kant zijn er veel verrassingen. Daarom moet je hiervan op de hoogte zijn. Als je nog iets vreemds of ongewonens vindt in het gedrag van bepaalde types, laat het me dan weten in de comments, ik voeg graag meer toe aan de bestaande dossiers.

Bron: habr.com