PostgreSQL एंटीपैटर्न: 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),...

अनुरोध के "री-ग्लूइंग" के साथ ऊपर वर्णित समस्याओं के अलावा, यह हमें इस ओर भी ले जा सकता है स्मृति से बाहर और सर्वर क्रैश हो गया। कारण सरल है - पीजी तर्कों के लिए अतिरिक्त मेमोरी सुरक्षित रखता है, और सेट में रिकॉर्ड की संख्या केवल व्यावसायिक तर्क एप्लिकेशन विशलिस्ट द्वारा सीमित है। विशेष रूप से नैदानिक ​​मामलों में यह देखना आवश्यक था $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 प्रकार के साथ काम करने के लिए पूर्ण सुविधाएँ हैं। इसलिए, यदि आपके इनपुट पैरामीटर ब्राउज़र में परिभाषित हैं, तो आप वहीं फॉर्म बना सकते हैं SQL क्वेरी के लिए json ऑब्जेक्ट:

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

पिछले संस्करणों के लिए, उसी विधि का उपयोग किया जा सकता है प्रत्येक(hstore), लेकिन hstore में जटिल वस्तुओं से बचने के साथ सही "फोल्डिंग" समस्याएँ पैदा कर सकता है।

json_populator_recordset

यदि आप पहले से जानते हैं कि "इनपुट" जेसन सरणी से डेटा कुछ तालिका में भरने के लिए जाएगा, तो आप "डीरेफ़रेंसिंग" फ़ील्ड में बहुत कुछ सहेज सकते हैं और json_poplate_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_प्रकार, pg_निर्भर, 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