PostgreSQL एंटीपैटर्न: SQL में स्थिति का मूल्यांकन

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

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

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

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

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

#0: आरटीएफएम

शुरुआत दस्तावेज़ीकरण से उदाहरण:

जब मूल्यांकन का क्रम महत्वपूर्ण हो, तो इसे निर्माण के साथ तय किया जा सकता है 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-स्थितियाँ:

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-चेन) - क्या किसी तरह "अपनी प्राथमिकता बढ़ाना" संभव है ताकि दूसरा एक बार फिर निष्पादित न हो?

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

आइए इन दोनों स्थितियों को "CASE के अंतर्गत रखें":

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: असमानता शून्य का कारण नहीं बनती

चलिए मान लेते हैं कि सबकुछ 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-स्थिति जैसा कि #1 में दिखाया गया है...

स्रोत: www.habr.com