ProHoster > Блог > администрация > Подозрителни типове

Подозрителни типове

Няма нищо подозрително във външния им вид. Нещо повече, те дори ви изглеждат добре познати и отдавна. Но това е само докато не ги проверите. Тук те показват своята коварна същност, работейки съвсем различно от очакваното. А понякога правят нещо, от което ви настръхват косите – например губят поверени им секретни данни. Когато се сблъскате с тях, те твърдят, че не се познават, въпреки че в сенките работят усилено под една и съща качулка. Време е най-накрая да ги изведем на чиста вода. Нека се справим и с тези подозрителни типове.

Въвеждането на данни в PostgreSQL, въпреки цялата си логика, понякога поднася много странни изненади. В тази статия ще се опитаме да изясним някои от техните странности, да разберем причината за странното им поведение и да разберем как да не се натъкваме на проблеми в ежедневната практика. Честно казано, съставих тази статия и като един вид справочник за себе си, справочник, който лесно може да бъде използван при спорни случаи. Следователно, той ще бъде попълван, когато бъдат открити нови изненади от подозрителни видове. И така, да тръгваме, о, неуморни тракери на бази данни!

Досие номер едно. реална/двойна точност/цифрови/пари

Изглежда, че числовите типове са най-малко проблематични по отношение на изненадите в поведението. Но без значение как е. Така че нека започнем с тях. Така…

Забравих как да броя

SELECT 0.1::real = 0.1

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

Какъв е проблема? Проблемът е, че PostgreSQL преобразува нетипизираната константа 0.1 в двойна точност и се опитва да я сравни с 0.1 от реален тип. И това са съвсем различни значения! Идеята е да се представят реални числа в паметта на машината. Тъй като 0.1 не може да бъде представено като крайна двоична дроб (в двоичен формат би било 0.0(0011), числата с различни битови дълбочини ще бъдат различни, оттук и резултатът, че не са равни. Най-общо казано, това е тема за отделна статия, няма да пиша по-подробно тук.

Откъде идва грешката?

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

Много хора знаят, че PostgreSQL позволява функционална нотация за преобразуване на типове. Тоест можете да напишете не само 1::int, но и int(1), което ще бъде еквивалентно. Но не и за типове, чиито имена се състоят от няколко думи! Следователно, ако искате да прехвърлите числова стойност към тип с двойна точност във функционална форма, използвайте псевдонима на този тип float8, тоест SELECT float8(1).

Какво е по-голямо от безкрайността?

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

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

Вижте какво е! Оказва се, че има нещо по-голямо от безкрайността и то е NaN! В същото време документацията на PostgreSQL ни гледа с честни очи и твърди, че NaN очевидно е по-голямо от всяко друго число и, следователно, безкрайност. Обратното важи и за -NaN. Здравейте, любители на математиката! Но трябва да помним, че всичко това работи в контекста на реални числа.

Закръгляване на очите

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

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

Още един неочакван поздрав от базата. Отново не забравяйте, че двойната точност и числовите типове имат различни ефекти на закръгляване. За числова - обичайната, когато 0,5 се закръгля нагоре, а за двойна точност - 0,5 се закръгля към най-близкото четно цяло число.

Парите са нещо специално

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

Според PostgreSQL парите не са реално число. Според някои личности също. Трябва да помним, че прехвърлянето на типа пари е възможно само към числовия тип, точно както само числовият тип може да бъде преобразуван към типа пари. Но сега можете да играете с него, както сърцето ви желае. Но няма да са същите пари.

Smallint и генериране на последователност

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 не обича да си губи времето за дреболии. Какви са тези последователности, базирани на smallint? int, не по-малко! Следователно, когато се опитва да изпълни горната заявка, базата данни се опитва да преобразува smallint към някакъв друг целочислен тип и вижда, че може да има няколко такива прехвърляния. Кой актьорски състав да избера? Тя не може да реши това и следователно се срива с грешка.

Файл номер две. "char"/char/varchar/текст

Редица странности също присъстват в типовете герои. Да се ​​запознаем и с тях.

Що за трикове са това?

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

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

Що за "чар" е това, що за клоун е това? Не ни трябват такива... Защото се преструва на обикновен символ, въпреки че е в кавички. И се различава от обикновения char, който е без кавички, по това, че извежда само първия байт от представянето на низа, докато нормалният char извежда първия знак. В нашия случай първият символ е буквата P, която в представянето на unicode заема 2 байта, както се вижда от преобразуването на резултата в типа bytea. И типът "char" взема само първия байт от това представяне на unicode. Тогава защо е необходим този тип? В документацията на PostgreSQL се казва, че това е специален тип, използван за специални нужди. Така че едва ли ще ни трябва. Но погледнете в очите му и няма да сбъркате, когато го срещнете с неговото особено поведение.

Допълнителни пространства. Далеч от очите, далеч от ума

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

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

Разгледайте дадения пример. Специално преобразувах всички резултати в типа bytea, така че да се вижда ясно какво има там. Къде са крайните интервали след кастинг към varchar(6)? В документацията се посочва накратко: „Когато преобразувате стойността на знак към друг тип символи, празното пространство в края се отхвърля.“ Тази неприязън трябва да се помни. И имайте предвид, че ако низова константа в кавички се преобразува директно към тип varchar(6), интервалите в края се запазват. Такива са чудесата.

Файл номер три. json/jsonb

JSON е отделна структура, която живее свой собствен живот. Следователно неговите обекти и тези на PostgreSQL са малко по-различни. Ето примери.

Джонсън и Джонсън. Почувствай разликата

SELECT 'null'::jsonb IS NULL

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

Работата е там, че JSON има свой собствен нулев обект, който не е аналогът на NULL в PostgreSQL. В същото време самият JSON обект може да има стойност NULL, така че изразът SELECT null::jsonb IS NULL (обърнете внимание на липсата на единични кавички) този път ще върне true.

Една буква променя всичко

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

Работата е там, че json и jsonb са напълно различни структури. В json обектът се съхранява такъв, какъвто е, а в jsonb той вече се съхранява под формата на анализирана, индексирана структура. Ето защо във втория случай стойността на обекта по ключ 1 беше заменена от [1, 2, 3] на [7, 8, 9], която влезе в структурата в самия край със същия ключ.

Не пийте вода от лицето си

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

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

PostgreSQL в своята JSONB реализация променя форматирането на реалните числа, привеждайки ги в класическа форма. Това не се случва за типа JSON. Малко странно, но е прав.

Файл номер четири. дата/час/клеймо за час

Има и някои странности с типовете дата/час. Нека да ги разгледаме. Нека веднага направя резервация, че някои от поведенческите характеристики стават ясни, ако разбирате добре същността на работата с часови зони. Но това също е тема за отделна статия.

Моите твои не разбират

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

Изглежда, какво е неразбираемото тук? Но базата данни все още не разбира какво поставяме на първо място тук - годината или деня? И тя решава, че е януари 99, 2008 г., което я поразява. Най-общо казано, когато предавате дати в текстов формат, трябва много внимателно да проверите колко правилно ги е разпознала базата данни (по-специално, анализирайте параметъра datestyle с командата SHOW datestyle), тъй като неяснотите по този въпрос могат да бъдат много скъпи.

Откъде взехте това?

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

Защо базата данни не може да разбере изрично указаното време? Тъй като часовата зона няма съкращение, а пълно име, което има смисъл само в контекста на дата, тъй като отчита историята на промените в часовата зона и не работи без дата. И самата формулировка на времевата линия повдига въпроси - какво всъщност е имал предвид програмистът? Следователно тук всичко е логично, ако го погледнете.

Какво му става?

Представете си ситуацията. Имате поле във вашата таблица с тип timestamptz. Искате да го индексирате. Но разбирате, че изграждането на индекс в това поле не винаги е оправдано поради високата му селективност (почти всички стойности от този тип ще бъдат уникални). Така че решавате да намалите селективността на индекса, като прехвърлите типа към дата. И ще получите изненада:

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

Какъв е проблема? Факт е, че за прехвърляне на тип timestamptz към тип дата се използва стойността на системния параметър TimeZone, което прави функцията за преобразуване на типа зависима от персонализиран параметър, т.е. летлив. Такива функции не са разрешени в индекса. В този случай трябва изрично да посочите в коя часова зона се извършва преобразуването на типа.

Когато сега изобщо не е дори сега

Свикнали сме сега() да връща текущата дата/час, като взема предвид часовата зона. Но вижте следните запитвания:

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;

Датата/часът се връщат същите, независимо колко време е минало от предишната заявка! Какъв е проблема? Факт е, че now() не е текущият час, а началният час на текущата транзакция. Следователно той не се променя в рамките на транзакцията. Всяка заявка, стартирана извън обхвата на транзакция, е обвита имплицитно в транзакция, поради което не забелязваме, че времето, върнато от обикновен SELECT now(); всъщност не текущото... Ако искате да получите честно текущо време, трябва да използвате функцията clock_timestamp().

Файл номер пет. малко

Странно малко

SELECT '111'::bit(4)

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

От коя страна трябва да се добавят битовете в случай на разширение на типа? Изглежда, че е отляво. Но само базата има различно мнение по този въпрос. Бъдете внимателни: ако броят на цифрите не съвпада при кастинг на тип, няма да получите това, което искате. Това се отнася както за добавяне на битове отдясно, така и за изрязване на битове. Също така отдясно...

Файл номер шест. Масиви

Дори NULL не се задейства

SELECT ARRAY[1, 2] || NULL

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

Като нормални хора, израснали на SQL, очакваме резултатът от този израз да бъде NULL. Но го нямаше. Връща се масив. Защо? Тъй като в този случай базата прехвърля NULL към целочислен масив и имплицитно извиква функцията array_cat. Но все още остава неясно защо тази „масивна котка“ не нулира масива. Това поведение също трябва да се помни.

Обобщете. Има много странни неща. Повечето от тях, разбира се, не са толкова критични, че да говорят за откровено неадекватно поведение. А други се обясняват с лекотата на използване или честотата на тяхната приложимост в определени ситуации. Но в същото време има много изненади. Следователно, трябва да знаете за тях. Ако намерите нещо друго странно или необичайно в поведението на който и да е тип, пишете в коментарите, ще се радвам да добавя към наличните досиета за тях.

Източник: www.habr.com

Добавяне на нов коментар