🧗🏽 👩‍👧‍👦 👰🏻 PostgreSQL Antipatterns: SQL में स्थितियों की गणना 🚈 👩🏾‍🚀 🚴

SQL C ++ नहीं है, न ही JavaScript। इसलिए, तार्किक अभिव्यक्तियों की गणना अलग है, और यह एक ही बात नहीं है:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

क्वेरी निष्पादन योजना के अनुकूलन की प्रक्रिया में, PostgreSQL मनमाने ढंग से समतुल्य शर्तों को "पुनर्व्यवस्थित" कर सकता है , व्यक्तिगत रिकॉर्ड के लिए उनमें से किसी की गणना नहीं कर सकता है, उन्हें लागू सूचकांक की स्थिति में असाइन कर सकता है ... संक्षेप में, यह अनुमान लगाना सबसे आसान है कि आप किस क्रम में नियंत्रण कर सकते हैं। सहकर्मी की स्थितियों की गणना की जाएगी (और क्या सभी पर) ।

इसलिए, यदि आप अभी भी प्राथमिकता को नियंत्रित करना चाहते हैं, तो आपको सशर्त अभिव्यक्तियों और ऑपरेटरों का उपयोग करके इन स्थितियों को संरचनात्मक रूप से असमान बनाने की आवश्यकता है ।

डेटा और उनके साथ काम करना हमारे वीएलएसआई कॉम्प्लेक्स का आधार है , इसलिए हमारे लिए यह बहुत महत्वपूर्ण है कि उन पर संचालन न केवल सही ढंग से किया जाता है, बल्कि कुशलतापूर्वक भी किया जाता है। आइए विशिष्ट उदाहरण देखें जहां अभिव्यक्ति गणना की त्रुटियां हो सकती हैं, और जहां उनकी प्रभावशीलता में सुधार के लायक है।

# 0: RTFM

प्रलेखन से उदाहरण शुरू :

जब गणना क्रम महत्वपूर्ण होता है, तो यह निर्माण का उपयोग करके तय किया जा सकता है CASE। उदाहरण के लिए, एक वाक्य में शून्य से विभाजित होने से बचने का ऐसा तरीका WHEREअविश्वसनीय है:
SELECT ... WHERE x > 0 AND y/x > 1.5;
सुरक्षित विकल्प:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
इस तरह से उपयोग किया गया निर्माण CASEअभिव्यक्ति को अनुकूलन से बचाता है, इसलिए इसका उपयोग केवल आवश्यक होने पर ही किया जाना चाहिए।

# 1: ट्रिगर में हालत

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

ऐसा लगता है कि सब कुछ अच्छा लग रहा है, लेकिन ... कोई भी वादा नहीं करता है कि SELECTपहली शर्त झूठी होने पर संलग्न को निष्पादित नहीं किया जाएगा। नेस्टेडIF का उपयोग करके सही :

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

अब आइए ध्यान से देखें - ट्रिगर फ़ंक्शन का पूरा शरीर "लपेटा" गया IF। और इसका मतलब यह है कि कुछ भी हमें इस शर्त को WHEN-condition का उपयोग करने की प्रक्रिया से हटाने से नहीं रोकता है :

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

यह दृष्टिकोण झूठी परिस्थितियों में सर्वर संसाधनों की बचत की गारंटी देता है।

# 2: या / और श्रृंखला

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

अन्यथा, आप प्राप्त कर सकते हैं कि दोनों EXISTS"सच्चे" होंगे, लेकिन दोनों पूरे होंगे ।

लेकिन अगर हम यह सुनिश्चित करते हैं कि उनमें से एक "सच" बहुत अधिक बार (या ANDश्रृंखला के लिए "झूठा" ) है, तो क्या यह संभव है कि किसी तरह "अपनी प्राथमिकता बढ़ाएं" ताकि दूसरा एक बार फिर से प्रदर्शन न करे?

यह पता चला है कि यह संभव है - एल्गोरिथ्म दृष्टिकोण पोस्टग्रेसीक्यू एंटिपर्टेन्ट्स लेख के विषय के करीब है : एक दुर्लभ रिकॉर्ड जोइन के मध्य तक पहुंच जाएगा ।

आइए इन दोनों स्थितियों के बारे में "पॉप इन कैस" देखें:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

इस मामले में, हमने ELSE-वल्यू का निर्धारण नहीं किया है , अर्थात, यदि दोनों स्थितियां झूठी हैं , तो CASEयह वापस आ जाएगी NULL, जिसे व्याख्या के रूप FALSEमें समझा जाता है WHERE।

इस उदाहरण को दूसरे तरीके से जोड़ा जा सकता है - स्वाद और रंग के लिए:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

# 3: कैसे [नहीं] शर्तों को लिखें

हमने इस ट्रिगर के "अजीब" ट्रिगर के कारणों का विश्लेषण करने में दो दिन बिताए - चलो देखते हैं क्यों।

स्रोत:

IF( NEW."_" is null or NEW."_" = (select '""'::regclass::oid) or NEW."_" = (select to_regclass('""')::oid)
     AND (   OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW."" ) ) THEN ...

समस्या # 1: असमानता NULL पर विचार नहीं करती है

कल्पना कीजिए कि ऑल- OLDफील्ड्स मायने रखता है NULL। क्या होगा?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

और बाहर काम करने के दृष्टिकोण से स्थितियां NULLसमान हैं FALSE, जैसा कि ऊपर बताया गया है।

समाधान : -ऑपरेटर IS DISTINCT FROMसे ऑपरेटर का उपयोग करें ROW, एक बार में पूरे रिकॉर्ड की तुलना करें:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

समस्या संख्या 2: एक ही कार्यक्षमता के विभिन्न कार्यान्वयन

की तुलना करें:

NEW."_" = (select '""'::regclass::oid)
NEW."_" = (select to_regclass('""')::oid)

अतिरिक्त घोंसले के शिकार क्यों हैं SELECT? समारोह के बारे में क्या to_regclass? और अलग-अलग तरीकों से, क्यों? ..

ठीक करें:

NEW."_" = '""'::regclass::oid
NEW."_" = '""'::regclass::oid

समस्या # 3: बूल संचालन प्राथमिकता

स्रोत को प्रारूपित करें:

{... IS NULL} OR
{... } OR
{... } AND
( {... } )

उफ ... वास्तव में, यह पता चला कि पहली दो स्थितियों में से किसी की सच्चाई के मामले में, पूरी स्थिति बदल जाती है TRUE, असमानताओं को ध्यान में रखे बिना। और यह बिल्कुल नहीं है जो हम चाहते थे।

ठीक कर:

(
  {... IS NULL} OR
  {... } OR
  {... }
) AND
( {... } )

समस्या 4 (छोटा): एक क्षेत्र के लिए जटिल या स्थिति

वास्तव में, हमें नंबर 3 में समस्याएं ठीक थीं क्योंकि तीन शर्तें थीं। लेकिन उनके बजाय, आप तंत्र का उपयोग कर एक कर सकते हैं coalesce ... IN:

coalesce(NEW."_"::text, '') IN ('', '""', '""')

तो हम NULL"पकड़", और ORकोष्ठक के साथ जटिल बाड़ के लिए नहीं है।

संपूर्ण

हम तय कर लेते हैं कि हमें क्या मिला:

IF (
  coalesce(NEW."_"::text, '') IN ('', '""', '""') AND
  (
    OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  ) IS DISTINCT FROM (
    NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  )
) THEN ...

और अगर हम इस बात को ध्यान में रखते हैं कि इस ट्रिगर फंक्शन का उपयोग केवल- UPDATEउत्प्रेरक में किया जा सकता है OLD/NEW, तो स्थिति में शीर्ष स्तर की उपस्थिति के कारण , तो इस स्थिति को आमतौर पर WHEN-condition में रखा जा सकता है , जैसा कि # 1 में दिखाया गया था ...

PostgreSQL Antipatterns: SQL में स्थितियों की गणना