PostgreSQL ანტიპატერნები: მდგომარეობის შეფასება SQL-ში

SQL არ არის C++ და არც JavaScript. ამრიგად, ლოგიკური გამონათქვამების შეფასება განსხვავებულია და ეს საერთოდ არ არის იგივე:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL მოთხოვნის შესრულების გეგმის ოპტიმიზაციისას შეუძლია თვითნებურად „გადააწყოს“ ეკვივალენტური პირობები, არ გამოთვალოთ რომელიმე მათგანი ინდივიდუალური ჩანაწერებისთვის, მიმართეთ გამოყენებული ინდექსის მდგომარეობას... მოკლედ, ყველაზე მარტივი გზაა ვივარაუდოთ, რომ თქვენ ვერ ახერხებს თანმიმდევრობა, რომლითაც ისინი იქნებიან (და გამოითვლება თუ არა ისინი საერთოდ) თანაბარი პირობები.

ამიტომ, თუ მაინც გსურთ პრიორიტეტების მართვა, საჭიროა სტრუქტურულად გახადეთ ეს პირობები არათანაბარი პირობით გამონათქვამები и ოპერატორები.

მონაცემები და მათთან მუშაობა არის საფუძველი ჩვენი VLSI კომპლექსი, ამიტომ ჩვენთვის ძალიან მნიშვნელოვანია, რომ მათზე ოპერაციები შესრულდეს არა მხოლოდ სწორად, არამედ ეფექტურად. მოდით გადავხედოთ კონკრეტულ მაგალითებს, სადაც შეიძლება დაშვებული იყოს შეცდომები გამოხატვის შეფასებაში და სადაც ღირს მათი ეფექტურობის გაუმჯობესება.

#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- პირობები:

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

ეს მიდგომა საშუალებას გაძლევთ შეინახოთ სერვერის რესურსები გარანტიით, თუ მდგომარეობა მცდარია.

#2: OR/AND ჯაჭვი

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

წინააღმდეგ შემთხვევაში, შეიძლება მივიღოთ, რომ ორივე EXISTS მართალია, მაგრამ ორივე აღსრულდება.

მაგრამ თუ დანამდვილებით ვიცით, რომ ერთი მათგანი ბევრად უფრო ხშირად არის „მართალი“ (ან „ცრუ“ - ამისთვის AND-ჯაჭვები) - შესაძლებელია როგორმე "გაიზარდოს მისი პრიორიტეტი" ისე, რომ მეორე კიდევ ერთხელ არ შესრულდეს?

გამოდის, რომ ეს შესაძლებელია - ალგორითმულად მიდგომა ახლოსაა სტატიის თემასთან PostgreSQL ანტიპატერნები: იშვიათი ჩანაწერი აღწევს JOIN-ის შუა რიცხვებს.

მოდით, უბრალოდ "გადავიტანოთ 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: უტოლობა არ ითვალისწინებს 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: bool უპირატესობა

მოდით დავაფორმოთ წყარო:

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