↪️ 🚣 🎶 PostgreSQL Antipatterns: menghitung kondisi dalam SQL 👩🏿‍🤝‍👨🏼 👩🏻‍🤝‍👨🏼 🌵

SQL bukan C ++, juga JavaScript. Oleh karena itu, perhitungan ekspresi logis berbeda, dan ini bukan hal yang sama:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Dalam proses mengoptimalkan rencana eksekusi kueri, PostgreSQL dapat secara sewenang-wenang "mengatur ulang" kondisi yang setara , tidak menghitung satu pun dari mereka untuk catatan individual, menetapkannya pada kondisi indeks yang digunakan ... Singkatnya, paling mudah untuk mengasumsikan bahwa Anda tidak dapat mengontrol dalam urutan mana Kondisi rekan akan dihitung (dan apakah sama sekali) .

Oleh karena itu, jika Anda masih ingin mengontrol prioritas, Anda harus secara struktural membuat kondisi ini tidak merata menggunakan ekspresi kondisional dan operator .

Data dan bekerja dengan mereka adalah dasar dari kompleks VLSI kami , sehingga sangat penting bagi kami bahwa operasi pada mereka dilakukan tidak hanya dengan benar, tetapi juga secara efisien. Mari kita lihat contoh spesifik di mana kesalahan perhitungan ekspresi dapat dibuat, dan di mana efisiensinya layak ditingkatkan.

# 0: RTFM

Mulai contoh dari dokumentasi :

Ketika urutan perhitungan penting, itu bisa diperbaiki menggunakan konstruksi CASE. Misalnya, cara untuk menghindari pembagian dengan nol dalam kalimat tidak WHEREdapat diandalkan:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Opsi aman:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Konstruksi yang digunakan dengan cara ini CASEmelindungi ekspresi dari optimisasi, oleh karena itu hanya boleh digunakan jika perlu.

# 1: kondisi dalam pemicu

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

Tampaknya semuanya terlihat baik, tapi ... Tidak ada yang berjanji bahwa yang terlampir SELECTtidak akan dieksekusi jika kondisi pertama salah. Benar menggunakan bersarangIF :

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

Sekarang mari kita perhatikan dengan seksama - seluruh tubuh fungsi pemicu ternyata "dibungkus" IF. Dan ini berarti bahwa tidak ada yang mencegah kita menghapus kondisi ini dari prosedur menggunakan WHEN-kondisi :

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

Pendekatan ini memungkinkan penghematan sumber daya server yang dijamin dalam kondisi yang salah.

# 2: ATAU / DAN rantai

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

Kalau tidak, Anda bisa mendapatkan bahwa keduanya EXISTSakan "benar", tetapi keduanya akan terpenuhi .

Tetapi jika kita tahu pasti bahwa salah satu dari mereka "benar" jauh lebih sering (atau "salah" untuk ANDrantai), apakah mungkin untuk "meningkatkan prioritasnya" sehingga yang kedua tidak dilakukan sekali lagi?

Ternyata itu mungkin - pendekatan algoritma dekat dengan topik artikel Antipatterns PostgreSQL: catatan langka akan mencapai bagian tengah GABUNGAN .

Mari kita "muncul di bawah CASE" kedua kondisi berikut:

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

Dalam hal ini, kami tidak menentukan ELSE-value, yaitu, jika kedua kondisi salah , ia CASEakan kembali NULL, yang ditafsirkan sebagai FALSEdalam WHERE-condition.

Contoh ini dapat dikombinasikan dengan cara lain - sesuai selera dan warna:

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

# 3: bagaimana [tidak] menulis kondisi

Kami menghabiskan dua hari menganalisis alasan pemicu "aneh" pemicu ini - mari kita lihat mengapa.

Sumber:

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

Masalah # 1: Ketimpangan Tidak Mempertimbangkan NULL

Bayangkan semua- OLDbidang itu penting NULL. Apa yang akan terjadi?

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

Dan dari sudut pandang bekerja, kondisinya NULLsetara FALSE, seperti yang disebutkan di atas.

Solusi : gunakan operator IS DISTINCT FROMdari ROW-operator, bandingkan seluruh catatan sekaligus:

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

Masalah nomor 2: implementasi berbeda dari fungsi yang sama

Membandingkan:

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

Mengapa ada sarang tambahan SELECT? Bagaimana dengan fungsinya to_regclass? Dan dengan berbagai cara, mengapa? ..

Perbaiki:

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

Masalah # 3: prioritas operasi bool

Format sumber:

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

Ups ... Faktanya, ternyata dalam kasus kebenaran salah satu dari dua kondisi pertama, seluruh kondisi berubah menjadi TRUE, tanpa memperhitungkan ketidaksetaraan. Dan ini sama sekali bukan yang kita inginkan.

Memperbaiki:

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

Masalah 4 (kecil): kondisi OR kompleks untuk satu bidang

Sebenarnya, kami punya masalah di No. 3 justru karena ada tiga syarat. Tapi alih-alih, Anda bisa melakukannya, menggunakan mekanisme coalesce ... IN:

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

Jadi kita NULL“tangkap”, dan komplek ORdengan kurung tidak harus dipagari.

Total

Kami memperbaiki apa yang kami dapatkan:

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

Dan jika kita memperhitungkan bahwa fungsi pemicu ini hanya dapat digunakan di UPDATE-trigger karena adanya OLD/NEWlevel teratas dalam kondisi, maka kondisi ini secara umum dapat dimasukkan ke dalam WHEN-kondisi, seperti yang ditunjukkan pada # 1 ...

PostgreSQL Antipatterns: menghitung kondisi dalam SQL