PostgreSQL Antipatterns: एसक्यूएल के पास सेट और चयन

समय-समय पर, डेवलपर को अनुरोध या "इनपुट" के एक पूरे चयन के लिए मापदंडों का एक सेट पारित करने की आवश्यकता होती है । इस समस्या के बहुत अजीब समाधान कभी-कभी सामने आते हैं।

"विपरीत" से जाने दें और देखें कि यह कैसे करने योग्य है, क्यों, और आप कैसे बेहतर कर सकते हैं।

अनुरोध निकाय में मूल्यों का प्रत्यक्ष सम्मिलन

यह आमतौर पर कुछ इस तरह दिखता है:

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, ... 

यदि आप अनुरोध को इस रूप में छोड़ देते हैं, तो हालांकि यह हमें संभावित इंजेक्शन से बचाएगा, फिर भी यह तर्कों की संख्या से प्रत्येक विकल्प के लिए अनुरोध को gluing / पार्स करने की आवश्यकता को जन्म देगा। हर बार करने से बेहतर पहले से ही है, लेकिन आप इसके बिना कर सकते हैं।

यह सरणी के क्रमबद्ध प्रतिनिधित्व वाले केवल एक पैरामीटर को पास करने के लिए पर्याप्त है:

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

एकमात्र अंतर यह है कि तर्क को वांछित सरणी प्रकार में स्पष्ट रूप से परिवर्तित करने की आवश्यकता है। लेकिन इससे समस्याएं पैदा नहीं होती हैं, क्योंकि हम पहले से ही पहले से जानते हैं कि हम कहां संबोधित कर रहे हैं।

नमूना अंतरण (मैट्रिसेस)

आमतौर पर डेटाबेस में प्रविष्टि के लिए डेटा सेट को "एक अनुरोध में" स्थानांतरित करने के लिए ये सभी प्रकार के विकल्प हैं:

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

अनुरोध के "री-स्टिकिंग" के साथ ऊपर वर्णित समस्याओं के अलावा, यह मेमोरी और सर्वर क्रैश से भी बाहर हो सकता है। कारण सरल है - तर्कों के लिए, पीजी अतिरिक्त मेमोरी रखता है, और सेट में रिकॉर्ड की संख्या केवल आवेदन किए गए विशलिस्ट व्यवसाय तर्क द्वारा सीमित है। विशेष रूप से नैदानिक ​​मामलों में, किसी को $ 9,000 से अधिक "गिने" तर्कों को देखना था - जिसकी कोई आवश्यकता नहीं है।

हम अनुरोध को फिर से लिखते हैं, "दो-स्तरीय" क्रमांकन लागू करते हुए:

 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_populate_recordset

यदि आप पहले से जानते हैं कि "इनपुट" json सरणी से डेटा किसी प्रकार की तालिका को भरने के लिए जाएगा, तो आप "dereferencing" फ़ील्ड में बहुत कुछ बचा सकते हैं और json_populate_recetet फ़ंक्शन का उपयोग करके आवश्यक प्रकारों पर कास्टिंग कर सकते हैं:

 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_dribute, pg_attrdef, ... में भिन्न होती है ... यह बिल्कुल भी नहीं है।

इसलिए, उनमें से प्रत्येक के लिए बड़ी संख्या में अल्पकालिक कनेक्शन वाले वेब-सिस्टम में, ऐसी तालिका हर बार नए सिस्टम रिकॉर्ड उत्पन्न करेगी, जो डेटाबेस बंद होने के कनेक्शन के साथ हटा दिए जाते हैं। परिणामस्वरूप, TEMP TABLE का अनियंत्रित उपयोग pg_catalog में तालिकाओं के "सूजन" की ओर जाता है और कई ऑपरेशनों को धीमा कर देता है जो उनका उपयोग करते हैं।
बेशक, यह सिस्टम कैटलॉग तालिकाओं के माध्यम से VACUUM पूर्ण आवधिक पास की मदद से लड़ा जा सकता है।

सत्र चर

मान लीजिए कि पिछले मामले से डेटा का प्रसंस्करण एकल SQL क्वेरी के लिए काफी जटिल है, लेकिन आप इसे अक्सर पर्याप्त करना चाहते हैं। यही है, हम डीओ-ब्लॉक में प्रक्रियात्मक प्रसंस्करण का उपयोग करना चाहते हैं, लेकिन अस्थायी तालिकाओं के माध्यम से डेटा हस्तांतरण का उपयोग करना बहुत महंगा होगा।

हम अनाम ब्लॉक में भी स्थानांतरण के लिए $ n-पैरामीटर्स का उपयोग नहीं कर पाएंगे। सत्र चर और current_setting फ़ंक्शन हमें इस स्थिति से बाहर निकलने में मदद करेंगे।

संस्करण 9.2 से पहले, "your" सत्र चर के लिए एक custom_variable_classes नामस्थान को पूर्व-कॉन्फ़िगर करना आवश्यक था। वर्तमान संस्करणों पर, आप कुछ इस तरह लिख सकते हैं:

 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 

अन्य समर्थित प्रक्रियात्मक भाषाएँ अन्य समाधान पा सकती हैं।

क्या आप और भी तरीके जानते हैं? टिप्पणियों में साझा करें!