🥇Verdachte types

Er is niets verdachts aan hun uiterlijk. Bovendien komen ze je zelfs al lang bekend voor. Maar dat is alleen totdat je ze controleert. Dit is waar ze hun verraderlijke aard laten zien en compleet anders werken dan je had verwacht. En soms doen ze iets waar je de haren van overeind gaat staan – ze verliezen bijvoorbeeld geheime gegevens die aan hen zijn toevertrouwd. Als je ze confronteert, beweren ze dat ze elkaar niet kennen, ook al werken ze in de schaduw hard onder dezelfde motorkap. Het is tijd om ze eindelijk naar schoon water te brengen. Laten we ook deze verdachte types aanpakken.

Het typen van gegevens in PostgreSQL brengt, ondanks al zijn logica, soms zeer vreemde verrassingen met zich mee. In dit artikel zullen we proberen enkele van hun eigenaardigheden te verduidelijken, de reden voor hun vreemde gedrag te begrijpen en te begrijpen hoe we in de dagelijkse praktijk geen problemen kunnen tegenkomen. Eerlijk gezegd heb ik dit artikel ook samengesteld als een soort naslagwerk voor mezelf, een naslagwerk waarnaar in controversiële gevallen gemakkelijk kan worden verwezen. Daarom zal het worden aangevuld als er nieuwe verrassingen van verdachte typen worden ontdekt. Dus laten we gaan, oh onvermoeibare databasetrackers!

Dossier nummer één. echt/dubbele precisie/numeriek/geld

Het lijkt erop dat numerieke typen het minst problematisch zijn als het gaat om verrassingen in gedrag. Maar hoe het ook is. Laten we dus met hen beginnen. Dus…

Vergat hoe te tellen

SELECT 0.1::real = 0.1

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

Wat is er aan de hand? Het probleem is dat PostgreSQL de ongetypeerde constante 0.1 omzet naar dubbele precisie en deze probeert te vergelijken met 0.1 van het echte type. En dit zijn totaal verschillende betekenissen! Het idee is om reële getallen in het machinegeheugen weer te geven. Omdat 0.1 niet kan worden weergegeven als een eindige binaire breuk (het zou 0.0(0011) zijn in binair getal), zullen getallen met verschillende bitdieptes verschillend zijn, vandaar dat ze niet gelijk zijn. Over het algemeen is dit een onderwerp voor een apart artikel; ik zal hier niet in meer detail schrijven.

Waar komt de fout vandaan?

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

Veel mensen weten dat PostgreSQL functionele notatie voor typecasting mogelijk maakt. Dat wil zeggen, je kunt niet alleen 1::int schrijven, maar ook int(1), wat gelijkwaardig zal zijn. Maar niet voor typen waarvan de naam uit meerdere woorden bestaat! Als u daarom een numerieke waarde wilt casten naar een type met dubbele precisie in functionele vorm, gebruikt u de alias van dit type float8, dat wil zeggen SELECT float8(1).

Wat is groter dan oneindig?

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

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

Kijk hoe het is! Het blijkt dat er iets groters is dan oneindig, en dat is NaN! Tegelijkertijd kijkt de documentatie van PostgreSQL ons met eerlijke ogen aan en beweert dat NaN duidelijk groter is dan enig ander getal, en daarom oneindig. Het tegenovergestelde geldt ook voor -NaN. Hallo wiskundeliefhebbers! Maar we moeten niet vergeten dat dit alles plaatsvindt in de context van reële getallen.

Ronding van de ogen

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

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

Nog een onverwachte groet van de basis. Onthoud nogmaals dat dubbele precisie en numerieke typen verschillende afrondingseffecten hebben. Voor numeriek - de gebruikelijke, wanneer 0,5 naar boven wordt afgerond, en voor dubbele precisie - wordt 0,5 afgerond naar het dichtstbijzijnde even gehele getal.

Geld is iets bijzonders

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

Volgens PostgreSQL is geld geen reëel getal. Volgens sommige individuen ook. We moeten niet vergeten dat het geldtype alleen naar het numerieke type kan worden gecast, net zoals alleen het numerieke type naar het geldtype kan worden gecast. Maar nu kun je ermee spelen zoals je hartje begeert. Maar het zal niet hetzelfde geld zijn.

Smallint en sequentiegeneratie

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 houdt er niet van om tijd te verspillen aan kleinigheden. Wat zijn deze reeksen gebaseerd op smallint? int, niet minder! Daarom probeert de database, wanneer de bovenstaande query wordt uitgevoerd, smallint naar een ander geheel getaltype te casten, en ziet dat er meerdere van dergelijke casts kunnen zijn. Welke cast kiezen? Ze kan dit niet beslissen en crasht daarom met een fout.

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

Er zijn ook een aantal eigenaardigheden aanwezig in karaktertypen. Laten we ze ook leren kennen.

Wat voor trucjes zijn dit?

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

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

Wat voor soort "char" is dit, wat voor soort clown is dit? Die hebben we niet nodig... Omdat het zich voordoet als een gewoon teken, ook al staat het tussen aanhalingstekens. En het verschilt van een gewone char, die zonder aanhalingstekens staat, doordat het alleen de eerste byte van de stringrepresentatie uitvoert, terwijl een normale char het eerste teken uitvoert. In ons geval is het eerste teken de letter P, die in de Unicode-weergave 2 bytes in beslag neemt, zoals blijkt uit het converteren van het resultaat naar het bytea-type. En het type “char” neemt alleen de eerste byte van deze Unicode-representatie in beslag. Waarom is dit type dan nodig? De PostgreSQL-documentatie zegt dat dit een speciaal type is dat wordt gebruikt voor speciale behoeften. Het is dus onwaarschijnlijk dat we het nodig zullen hebben. Maar kijk hem in de ogen en je zult je niet vergissen als je hem tegenkomt met zijn bijzondere gedrag.

Extra 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 eens naar het gegeven voorbeeld. Alle resultaten heb ik speciaal omgezet naar het type bytea, zodat duidelijk zichtbaar was wat er stond. Waar zijn de volgspaties na het casten naar varchar(6)? De documentatie stelt kort en bondig: "Wanneer de waarde van een teken naar een ander tekentype wordt gecast, wordt de achterliggende witruimte weggegooid." Deze afkeer moet onthouden worden. En merk op dat als een stringconstante tussen aanhalingstekens rechtstreeks naar het type varchar(6) wordt gegoten, de volgspaties behouden blijven. Dat zijn de wonderen.

Dossier nummer drie. json/jsonb

JSON is een aparte structuur die zijn eigen leven leidt. Daarom zijn de entiteiten en die van PostgreSQL enigszins verschillend. Hier zijn voorbeelden.

Johnson en Johnson. voel het verschil

SELECT 'null'::jsonb IS NULL

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

Het punt is dat JSON zijn eigen nulentiteit heeft, die niet analoog is aan NULL in PostgreSQL. Tegelijkertijd kan het JSON-object zelf de waarde NULL hebben, dus de expressie SELECT null::jsonb IS NULL (let op de afwezigheid van enkele aanhalingstekens) zal deze keer true retourneren.

Eén brief 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 totaal verschillende structuren zijn. In json wordt het object opgeslagen zoals het is, en in jsonb is het al opgeslagen in de vorm van een ontlede, geïndexeerde structuur. Dat is de reden waarom in het tweede geval de waarde van het object door sleutel 1 werd vervangen van [1, 2, 3] naar [7, 8, 9], die helemaal aan het einde in de structuur kwam met dezelfde sleutel.

Drink geen water uit je gezicht

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

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

PostgreSQL verandert in zijn JSONB-implementatie de opmaak van reële getallen, waardoor ze de klassieke vorm krijgen. Dit gebeurt niet voor het JSON-type. Een beetje vreemd, maar hij heeft gelijk.

Dossier nummer vier. datum/tijd/tijdstempel

Er zijn ook enkele eigenaardigheden met datum-/tijdtypen. Laten we ze eens bekijken. Laat ik meteen voorop stellen dat sommige gedragskenmerken duidelijk worden als je de essentie van het werken met tijdzones goed begrijpt. Maar dit is ook een onderwerp voor een apart artikel.

Mijn jouwe begrijpt het niet

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

Het lijkt erop dat wat hier onbegrijpelijk is? Maar de database begrijpt nog steeds niet wat we hier op de eerste plaats zetten: het jaar of de dag? En ze besluit dat het 99 januari 2008 is, wat haar versteld doet staan. Over het algemeen moet u bij het verzenden van datums in tekstformaat zeer zorgvuldig controleren hoe correct de database ze heeft herkend (analyseer in het bijzonder de datestyle-parameter met het SHOW datestyle-commando), aangezien onduidelijkheden in deze kwestie erg duur kunnen zijn.

Waar kom je vandaan?

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

Waarom kan de database de expliciet gespecificeerde tijd niet begrijpen? Omdat de tijdzone geen afkorting heeft, maar een volledige naam, die alleen zinvol is in de context van een datum, omdat deze rekening houdt met de geschiedenis van tijdzoneveranderingen, en zonder datum niet werkt. En de bewoording van de tijdlijn roept vragen op: wat bedoelde de programmeur werkelijk? Daarom is alles hier logisch, als je ernaar kijkt.

Wat is er mis met hem?

Stel je de situatie voor. U hebt een veld in uw tabel met het type timestamptz. Je wilt het indexeren. Maar u begrijpt dat het bouwen van een index op dit veld niet altijd gerechtvaardigd is vanwege de hoge selectiviteit (bijna alle waarden van dit type zullen uniek zijn). U besluit dus de selectiviteit van de index te verminderen door het type naar een datum te casten. En je krijgt een verrassing:

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

Wat is er aan de hand? Het feit is dat om een timestamptz-type naar een datumtype te casten, de waarde van de TimeZone-systeemparameter wordt gebruikt, waardoor de typeconversiefunctie afhankelijk wordt van een aangepaste parameter, d.w.z. vluchtig. Dergelijke functies zijn niet toegestaan in de index. In dit geval moet u expliciet aangeven in welke tijdzone de typecast wordt uitgevoerd.

Wanneer nu is helemaal niet eens nu

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

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;

De datum/tijd wordt hetzelfde geretourneerd, ongeacht hoeveel tijd er is verstreken sinds het vorige verzoek! Wat is er aan de hand? Feit is dat now() niet de huidige tijd is, maar de starttijd van de huidige transactie. Daarom verandert dit niet binnen de transactie. Elke zoekopdracht die buiten het bereik van een transactie wordt gelanceerd, wordt impliciet in een transactie verpakt. Daarom merken we niet dat de tijd wordt geretourneerd door een simpele SELECT now(); in feite niet de huidige... Als je een eerlijke huidige tijd wilt krijgen, moet je de functie clock_timestamp() gebruiken.

Dossier nummer vijf. beetje

Een beetje vreemd

SELECT '111'::bit(4)

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

Aan welke kant moeten de bits worden toegevoegd bij type-extensie? Het lijkt aan de linkerkant te zijn. Maar alleen de basis heeft hierover een andere mening. Let op: als het aantal cijfers niet overeenkomt bij het casten van een type, krijg je niet wat je wilde. Dit geldt zowel voor het toevoegen van bits aan de rechterkant als voor het trimmen van bits. Ook rechts...

Dossier nummer zes. Arrays

Zelfs NULL schoot niet

SELECT ARRAY[1, 2] || NULL

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

Omdat normale mensen met SQL zijn opgegroeid, verwachten we dat het resultaat van deze expressie NULL is. Maar het was er niet. Er wordt een array geretourneerd. Waarom? Omdat in dit geval de base NULL naar een integer-array cast en impliciet de array_cat-functie aanroept. Maar het blijft nog steeds onduidelijk waarom deze “array cat” de array niet reset. Dit gedrag moet ook gewoon onthouden worden.

Samenvatten. Er zijn genoeg vreemde dingen. De meesten van hen zijn uiteraard niet zo kritisch dat ze over flagrant ongepast gedrag praten. En andere worden verklaard door gebruiksgemak of de frequentie van hun toepasbaarheid in bepaalde situaties. Maar tegelijkertijd zijn er veel verrassingen. Daarom moet je er meer over weten. Als u iets anders vreemds of ongewoons vindt in het gedrag van welk type dan ook, schrijf dan in de opmerkingen. Ik zal graag iets toevoegen aan de dossiers die daarover beschikbaar zijn.

Bron: www.habr.com

Verdachte types