საეჭვო ტიპები

მათ გარეგნობაში საეჭვო არაფერია. უფრო მეტიც, ისინი კარგად და დიდი ხნის განმავლობაში ნაცნობიც კი გეჩვენებათ. მაგრამ ეს მხოლოდ მანამ, სანამ არ შეამოწმებთ მათ. აქ ისინი აჩვენებენ თავიანთ მზაკვრულ ბუნებას, მუშაობენ სრულიად განსხვავებულად, ვიდრე თქვენ მოელოდით. და ხანდახან ისეთ რაღაცას აკეთებენ, რითაც თმას გიწევს – მაგალითად, კარგავენ მათთვის მინდობილ საიდუმლო მონაცემებს. როცა მათ უპირისპირდებით, ისინი აცხადებენ, რომ ერთმანეთს არ იცნობენ, თუმცა ჩრდილში ერთსა და იმავე კაპოტის ქვეშ ბევრს მუშაობენ. დროა საბოლოოდ მიიყვანოთ ისინი სუფთა წყალში. მოდით გავუმკლავდეთ ამ საეჭვო ტიპებსაც.

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-ის მიხედვით, ფული არ არის რეალური რიცხვი. ზოგიერთი პიროვნების აზრითაც. უნდა გვახსოვდეს, რომ ფულის ტიპის ჩამორთმევა შესაძლებელია მხოლოდ ციფრულ ტიპზე, ისევე როგორც მხოლოდ რიცხვითი ტიპის გადაცემა შესაძლებელია ფულის ტიპზე. მაგრამ ახლა თქვენ შეგიძლიათ ითამაშოთ როგორც თქვენი გული. მაგრამ ეს არ იქნება იგივე ფული.

მცირე და თანმიმდევრობით თაობა

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-ს არ უყვარს დროის დაკარგვა წვრილმანებზე. რა არის ეს თანმიმდევრობები დაფუძნებული წვრილმანებზე? int, არანაკლებ! ამიტომ, ზემოაღნიშნული მოთხოვნის შესრულების მცდელობისას, მონაცემთა ბაზა ცდილობს smallint-ის გადატანას სხვა მთელი რიცხვის ტიპზე და ხედავს, რომ შეიძლება იყოს რამდენიმე ასეთი cast. რომელი მსახიობი აირჩიოს? მას არ შეუძლია ამის გადაწყვეტა და, შესაბამისად, შეცდომით იშლება.

ფაილი ნომერი ორი. "char"/char/varchar/ტექსტი

რიგი უცნაურობები ასევე გვხვდება პერსონაჟების ტიპებში. მოდით გავეცნოთ მათაც.

რა სახის ხრიკებია ეს?

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

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

ეს რა ტიპის "შარია", რა კლოუნია ეს? ჩვენ არ გვჭირდება ეს... იმიტომ, რომ ის თავს იჩენს როგორც ჩვეულებრივი ქარხანა, მიუხედავად იმისა, რომ ბრჭყალებშია. და ის განსხვავდება ჩვეულებრივი char-ისგან, რომელიც ბრჭყალების გარეშეა, იმით, რომ გამოაქვს სტრიქონის წარმოდგენის მხოლოდ პირველი ბაიტი, ხოლო ჩვეულებრივი char გამოაქვს პირველი სიმბოლო. ჩვენს შემთხვევაში, პირველი სიმბოლოა ასო P, რომელიც უნიკოდის წარმოდგენაში იკავებს 2 ბაიტს, რაც დასტურდება შედეგის ბაიტის ტიპად გადაქცევით. და "char" ტიპი იღებს ამ უნიკოდის წარმოდგენის მხოლოდ პირველ ბაიტს. მაშინ რატომ არის საჭირო ეს ტიპი? 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

შეხედეთ მოცემულ მაგალითს. მე სპეციალურად გადავაქციე ყველა შედეგი ბაიტის ტიპზე, ისე, რომ ნათლად ჩანდა, რაც იყო. სად არის მიმავალი ადგილები ვარჩარ(6) ჩამოსხმის შემდეგ? დოკუმენტაციაში ლაკონურად ნათქვამია: „გმირის მნიშვნელობის სხვა ტიპზე გადაცემისას, უკანა სივრცე უგულვებელყოფილია“. ეს ზიზღი უნდა ახსოვდეს. და გაითვალისწინეთ, რომ თუ ციტირებული სტრიქონის მუდმივი გადატანილია პირდაპირ ტიპზე varchar(6), უკანა სივრცეები შენარჩუნებულია. ასეთია სასწაულები.

ფაილი ნომერი სამი. json/jsonb

JSON არის ცალკე სტრუქტურა, რომელიც ცხოვრობს საკუთარი ცხოვრებით. ამიტომ, მისი და PostgreSQL-ის ერთეულები ოდნავ განსხვავებულია. აი მაგალითები.

ჯონსონი და ჯონსონი. იგრძენი განსხვავება

SELECT 'null'::jsonb IS NULL

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

საქმე იმაშია, რომ JSON-ს აქვს საკუთარი null ერთეული, რომელიც არ არის 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 სისტემის პარამეტრის მნიშვნელობა, რაც ტიპების კონვერტაციის ფუნქციას დამოკიდებულს ხდის მორგებულ პარამეტრზე, ე.ი. არასტაბილური. ასეთი ფუნქციები დაუშვებელია ინდექსში. ამ შემთხვევაში, თქვენ მკაფიოდ უნდა მიუთითოთ, თუ რომელ დროის ზონაში ხორციელდება გადაცემის ტიპი.

როცა ახლა საერთოდ არ არის

ჩვენ მიჩვეულები ვართ now() დროის ზონის გათვალისწინებით მიმდინარე თარიღის/დროის დაბრუნებას. მაგრამ შეხედეთ შემდეგ კითხვებს:

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