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

अनुरोध "पुनः gluing" संग माथि वर्णित समस्याहरु को अतिरिक्त, यसले हामीलाई पनि नेतृत्व गर्न सक्छ मेमोरी बाहिर र सर्भर क्र्यास। कारण सरल छ - 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;

हो, एरे भित्र "जटिल" मानहरूको अवस्थामा, तिनीहरू उद्धरणहरूद्वारा घेरिएको हुनुपर्छ।
यो स्पष्ट छ कि यस तरिकाले तपाईले क्षेत्रहरूको मनमानी संख्याको साथ चयन "विस्तार" गर्न सक्नुहुन्छ।

नस्ट, नस्ट,…

समय-समयमा त्यहाँ "एरेको एरे" धेरै "स्तम्भहरूको एरे" को सट्टा पास गर्ने विकल्पहरू छन् जुन मैले उल्लेख गरेको छु। पछिल्लो लेखमा:

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 मा जटिल वस्तुहरू छोडेर सही "convolution" ले समस्या निम्त्याउन सक्छ।

json_populate_recordset

यदि तपाईंलाई पहिले नै थाहा छ कि "इनपुट" json array बाट डाटा केहि तालिका भर्न प्रयोग गरिनेछ, तपाइँ "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 प्रणाली तालिकामा, र in pg_type, pg_depend, pg_attribute, pg_attrdef, ... - केहि पनि होईन।

तसर्थ, वेब प्रणालीहरूमा तिनीहरूमध्ये प्रत्येकको लागि धेरै संख्यामा छोटो-अवस्था जडानहरू छन्, यस्तो तालिकाले प्रत्येक पटक नयाँ प्रणाली रेकर्डहरू उत्पन्न गर्नेछ, जुन डाटाबेसमा जडान बन्द हुँदा मेटाइन्छ। अन्ततः, TEMP TABLE को अनियन्त्रित प्रयोगले pg_catalog मा टेबलको "सुजन" निम्त्याउँछ र तिनीहरूलाई प्रयोग गर्ने धेरै अपरेशनहरू सुस्त पार्दै।
निस्सन्देह, यो प्रयोग गरेर व्यवहार गर्न सकिन्छ आवधिक मार्ग VACUUM FULL प्रणाली सूची तालिका अनुसार।

सत्र चर

मानौं कि अघिल्लो केसबाट डाटा प्रशोधन गर्नु एक SQL क्वेरीको लागि एकदम जटिल छ, तर तपाइँ यसलाई प्राय: गर्न चाहनुहुन्छ। त्यो हो, हामी प्रक्रियागत प्रशोधन प्रयोग गर्न चाहन्छौं ब्लक गर्नुहोस्, तर अस्थायी तालिकाहरु मार्फत डाटा स्थानान्तरण प्रयोग धेरै महँगो हुनेछ।

हामी पनि अज्ञात ब्लकमा पास गर्नको लागि $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