🥇PostgreSQL अँटीपॅटर्न: SQL ला सेट आणि निवडी पास करणे

वेळोवेळी विकासकाची गरज असते विनंतीसाठी पॅरामीटर्सचा संच किंवा संपूर्ण निवड पास करा "प्रवेशद्वारावर". कधी कधी या समस्येवर तुम्हाला खूप विचित्र उपाय सापडतात.

चला मागे जाऊ आणि काय करू नये, का आणि आपण ते अधिक चांगले कसे करू शकतो ते पाहू.

विनंतीच्या मुख्य भागामध्ये थेट मूल्ये समाविष्ट करणे

हे सहसा असे काहीतरी दिसते:

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

...किंवा यासारखे:

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

ही पद्धत सांगितले गेले आहे, लिहिले आहे आणि अगदी काढले भरपूर:

जवळजवळ नेहमीच हे असते एसक्यूएल इंजेक्शन्सचा थेट मार्ग आणि बिझनेस लॉजिकवर अनावश्यक भार, ज्याला तुमची क्वेरी लाइन "गोंद" करण्यास भाग पाडले जाते.

आवश्यक असल्यासच हा दृष्टिकोन अंशतः न्याय्य ठरू शकतो विभाजन वापरणे अधिक कार्यक्षम योजना मिळविण्यासाठी 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 वितर्कांसाठी अतिरिक्त मेमरी राखून ठेवते आणि सेटमधील रेकॉर्डची संख्या केवळ व्यावसायिक तर्काच्या अनुप्रयोगाच्या गरजेनुसार मर्यादित आहे. विशेषतः क्लिनिकल प्रकरणांमध्ये मला पहावे लागले "संख्या" वितर्क $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;

होय, अॅरेमधील "जटिल" मूल्यांच्या बाबतीत, ते अवतरणांनी वेढलेले असले पाहिजेत.
हे स्पष्ट आहे की अशा प्रकारे आपण फील्डच्या अनियंत्रित संख्येसह निवड "विस्तारित" करू शकता.

unnest, uncest, …

वेळोवेळी "अ‍ॅरे ऑफ अ‍ॅरे" ऐवजी मी नमूद केलेल्या "कॉलमचे अॅरे" ऐवजी पास करण्याचे पर्याय आहेत. मागील लेखात:

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 ऑब्जेक्ट:

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

मागील आवृत्त्यांसाठी, समान पद्धत वापरली जाऊ शकते प्रत्येक (hstore), परंतु hstore मधील गुंतागुंतीच्या वस्तू बाहेर काढण्यासाठी योग्य "Convolution" समस्या निर्माण करू शकतात.

json_populate_recordset

जर तुम्हाला आधीच माहित असेल की "इनपुट" json अॅरेमधील डेटा काही टेबल भरण्यासाठी वापरला जाईल, तर तुम्ही json_populate_recordset फंक्शन वापरून "dereferencing" फील्डमध्ये खूप बचत करू शकता आणि त्यांना आवश्यक प्रकारांमध्ये कास्ट करू शकता:

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 मधील टेबलांना "सूज" नेतो आणि त्यांचा वापर करणाऱ्या अनेक ऑपरेशन्स कमी करणे.
अर्थात, हे वापरून हाताळले जाऊ शकते नियतकालिक रस्ता व्हॅक्यूम पूर्ण सिस्टम कॅटलॉग सारण्यांनुसार.

सत्र चल

चला असे गृहीत धरू की मागील केसमधील डेटावर प्रक्रिया करणे एका एसक्यूएल क्वेरीसाठी खूप क्लिष्ट आहे, परंतु तुम्हाला ते बरेचदा करायचे आहे. म्हणजेच, आम्ही मध्ये प्रक्रियात्मक प्रक्रिया वापरू इच्छितो ब्लॉक करा, परंतु तात्पुरत्या सारण्यांद्वारे डेटा हस्तांतरण वापरणे खूप महाग असेल.

निनावी ब्लॉकला पास करण्यासाठी आम्ही $n-पॅरामीटर्स देखील वापरू शकणार नाही. सत्र व्हेरिएबल्स आणि फंक्शन आपल्याला या परिस्थितीतून बाहेर पडण्यास मदत करतील चालू_सेटिंग.

आवृत्ती 9.2 पूर्वी पूर्व-कॉन्फिगर करणे आवश्यक होते विशेष नेमस्पेस 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

इतर उपाय इतर समर्थित प्रक्रियात्मक भाषांमध्ये आढळू शकतात.

तुम्हाला इतर काही मार्ग माहित आहेत का? टिप्पण्यांमध्ये सामायिक करा!

स्त्रोत: www.habr.com

PostgreSQL अँटीपॅटर्न: सेट्स पास करणे आणि SQL ला निवडणे