טיפוסים חשודים

אין שום דבר חשוד במראה שלהם. יתרה מכך, הם אפילו נראים לך מוכרים היטב ולאורך זמן. אבל זה רק עד שתבדוק אותם. זה המקום שבו הם מראים את טבעם הערמומי, עובדים אחרת לגמרי ממה שציפיתם. ולפעמים הם עושים משהו שגורם לשיער שלך להזדקף - למשל, הם מאבדים נתונים סודיים שהופקדו עליהם. כשמתעמתים איתם הם טוענים שהם לא מכירים אחד את השני, למרות שבצללים הם עובדים קשה תחת אותו מכסה מנוע. הגיע הזמן להביא אותם סוף סוף למים נקיים. הבה נעסוק גם בטיפוסים החשודים הללו.

הקלדת נתונים ב-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 לא אוהב לבזבז זמן על זוטות. מה הם הרצפים האלה המבוססים על סמולינט? int, לא פחות! לכן, כאשר מנסים לבצע את השאילתה הנ"ל, מסד הנתונים מנסה להטיל אינט קטן לסוג מספר שלם אחר, ורואה שייתכן שיהיו כמה השלמות כאלה. איזה קאסט לבחור? היא לא יכולה להחליט על כך, ולכן קורסת עם שגיאה.

תיק מספר שתיים. "char"/char/varchar/text

מספר מוזרויות קיימות גם בסוגי תווים. בואו להכיר גם אותם.

איזה סוג של טריקים אלה?

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

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

איזה סוג של "צ'אר" זה, איזה סוג של ליצן זה? אנחנו לא צריכים את אלה... כי זה מתיימר להיות char רגיל, למרות שזה במרכאות. והוא נבדל מ-char רגיל, שהוא ללא מרכאות, בכך שהוא מוציא רק את הביט הראשון של ייצוג המחרוזת, בעוד ש-char רגיל מוציא את התו הראשון. במקרה שלנו, התו הראשון הוא האות P, אשר בייצוג unicode תופסת 2 בתים, כפי שמעידים על ידי המרת התוצאה לסוג bytea. והסוג "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

תסתכל על הדוגמה שניתנה. המרתי במיוחד את כל התוצאות לסוג bytea, כך שיהיה ברור מה יש שם. איפה הרווחים הנגררים לאחר הליהוק ל-varchar(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

מה הבעיה? העובדה היא שכדי להטיל סוג חותמת זמן לסוג תאריך, נעשה שימוש בערך של פרמטר מערכת 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;

התאריך/שעה מוחזרים זהים לא משנה כמה זמן עבר מאז הבקשה הקודמת! מה הבעיה? העובדה היא שעכשיו() אינו הזמן הנוכחי, אלא שעת ההתחלה של העסקה הנוכחית. לכן, זה לא משתנה בתוך העסקה. כל שאילתה המופעלת מחוץ להיקף של עסקה עטופה בטרנזקציה באופן מרומז, וזו הסיבה שאנחנו לא שמים לב שהזמן מוחזר על ידי 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