PostgreSQL Antipatterns: העברת סטים ובחירות ל-SQL

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

בוא נלך "מההפך" ונראה איך לא עושים את זה, למה ואיך אפשר לעשות את זה טוב יותר.

"הכנסה" ישירה של ערכים בגוף הבקשה

בדרך כלל זה נראה בערך כך:

query = "SELECT * FROM tbl WHERE id = " + value

... או ככה:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

על שיטה זו נאמר, כתוב ו אפילו מצויר מספיק:

כמעט תמיד זה כך נתיב ישיר להזרקת SQL ועומס נוסף על ההיגיון העסקי, שנאלץ "להדביק" את מחרוזת השאילתות שלך.

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

$n טיעונים

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

מספר משתנה של ארגומנטים

בעיות יחכו לנו כאשר נרצה להעביר מספר לא ידוע של טיעונים מראש:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

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

זה מספיק כדי להעביר רק פרמטר אחד המכיל ייצוג סדרתי של מערך:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

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

העברה לדוגמה (מטריקס)

בדרך כלל אלו כל מיני אפשרויות להעברת מערכי נתונים להוספה למסד הנתונים "בבקשה אחת":

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

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

בוא נשכתב את השאילתה, נחיל כבר סדרה "דו-מפלסית".:

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

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

אי-שקט, אי-שקט,…

מעת לעת יש אופציות למעבר במקום "מערך מערכים" כמה "מערכי עמודות" שציינתי במאמר האחרון:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

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

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

החל מגרסה 9.3, ל-PostgreSQL יש פונקציות מלאות לעבודה עם סוג json. לכן, אם פרמטרי הקלט שלך מוגדרים בדפדפן, אתה יכול ממש שם וליצור אובייקט json עבור שאילתת SQL:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

עבור גרסאות קודמות, ניתן להשתמש באותה שיטה עבור כל (hstore), אבל "קיפול" נכון עם אובייקטים מורכבים שנמלטים ב-hstore עלול לגרום לבעיות.

json_populate_recordset

אם אתה יודע מראש שהנתונים ממערך "input" json יעברו למילוי טבלה כלשהי, תוכל לחסוך הרבה בשדות "dereferencing" והעברה לסוגים הרצויים באמצעות הפונקציה json_populate_recordset:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

ופונקציה זו פשוט "תרחיב" את מערך האובייקטים המועבר לבחירה, מבלי להסתמך על פורמט הטבלה:

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

שולחן זמני

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

במקרה זה, הפתרון הטוב ביותר יהיה להשתמש שולחנות זמניים:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

השיטה טובה לשידור נדיר של נפחים גדולים נתונים.
מנקודת המבט של תיאור מבנה הנתונים שלה, טבלה זמנית שונה מטבלה "רגילה" בתכונה אחת בלבד. בטבלת מערכת pg_class, בעוד ב pg_type, pg_depend, pg_attribute, pg_attrdef, ... - וכלום בכלל.

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

משתני הפעלה

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

אנחנו גם לא יכולים להשתמש ב-$n-פרמטרים כדי לעבור לבלוק אנונימי. משתני הפגישה והפונקציה יעזרו לנו לצאת מהמצב. ההגדרה הנוכחית.

לפני גרסה 9.2, היה עליך להגדיר מראש מרחב שמות מיוחד מחלקות_משתנים_מותאמת אישית עבור משתני הפגישה "שלהם". בגרסאות הנוכחיות, אתה יכול לכתוב משהו כזה:

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

ישנם פתרונות אחרים זמינים בשפות פרוצדורליות נתמכות אחרות.

מכיר דרכים נוספות? שתפו בתגובות!

מקור: www.habr.com