PostgreSQL Antipatterns: SQL-də parametr və seçmələrin ötürülməsi

Fərdi tərtibatçının müəyyən aralıqlarla ehtiyacı olur sorğuda bir sıra parametr və ya hətta tam bir seçmə «giriş» olaraq ötürmək. Bəzən bu məqsədlə çox qəribə həllərlə qarşılaşırıq.

İşə «geri dönmə» prinsipi ilə başlayaraq, hansı şəkildə edilməməli olduğunu, niyə belə olduğunu və daha yaxşı necə edə biləcəyimizi nəzərdən keçirək.

Sorğunun bədəninə birbaşa «yerləşdirmə»

Adətən təxminən belə görünür:

query = "SELECT * FROM tbl WHERE id = " + value

… ya da belə:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

Bu üsul haqqında kifayət qədər yazılıb, danışılıb və hətta rəsm çəkilib :

Demək olar ki, hər zaman bu — SQL-inyeksiyalara birbaşa yol açır və «sizin sorğunuzun» sətrini «yapışdırmaq» məcburiyyətində olan biznes məntiqi üçün əlavə yük yaradır.

Bu yanaşmanın bir az əsaslandırılmış olması yalnız sectiyaların istifadəsi zərurəti halında PostgreSQL 10 və aşağı versiyalarda daha səmərəli plan əldə etmək üçün mümkündür. Bu versiyalarda taranan bölmələrin siyahısı yalnız sorğunun bədəninə əsaslanaraq, ötürülən parametrləri nəzərə almadan müəyyən edilir.

$n-argumentlər

İstifadəsi placeholder-lar parametrlər — bu yaxşıdır, çünki bu PREPARED STATEMENTS, həm biznes məntiqinə yükü azaldır (sorğu sətri yalnız bir dəfə formalaşdırılıb ötürülür), həm də verilənlər bazası serverinə (hər bir sorğu nümunəsi üçün yenidən təhlil və planlaşdırma tələb olunmur).

Dəyişən sayda argumentlər

Problemlər, əvvəlcədən bilinməyən sayda argumentlər ötürməyə çalışdığımız zaman bizi gözləyir:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Əgər sorğunu belə buraxsaq, bu, potensial inyeksiyalardan bizi qoruya bilər, lakin yəni birləşdirmək/mütəxəssis etmək zərurətinə səbəb olacaq argumentlər sayısının hər versiyası üçün sorğu. Artıq bunu hər dəfə etməkdən daha yaxşıdır, lakin bundan da imtina edə bilərik.

Sadəcə bir parametr ötürmək kifayətdir, bu da massivin seriyalaşdırılmış təqdimatıdır:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

Yeganə fərq — argumenti lazım olan massiv tipinə açıq şəkildə çevirmək zərurətidir. Lakin bu, heç bir problem yaratmır, çünki biz hara yönəldiyimizi öncədən bilirik.

Seçmənin (matriksin) ötürülməsi

Adətən, bu, verilənlərin bazaya «bir sorğu ilə» daxil edilməsi üçün müxtəlif yolları əhatə edir:

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Yuxarıda qeyd olunan «birləşdirmə» sorğusu ilə bağlı problemlərə əlavə olaraq, bu bizi out of memory və serverin çöküşünə aparacaq. Səbəb sadədir — PG argumentləri üçün əlavə yaddaş ayırır, amma dəstədəki qeydlərin sayı yalnız biznes məntiqinin istəkləri ilə məhdudlaşır. Xüsusi klinik hallarda 9000-dən çox «ədədi» argumentlər — belə etməyin.

Sorğunu yenidən yazaq, artıq «iki pilləli» seriyalaşdırma tətbiq etməklə:

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;

Bəli, "çətin" dəyərlərin olduğu array-lar içərisində onları кавычками сжать требуется.
Beləliklə, bu üsulla istənilən sayda sahə ilə seçimi "açmağın" mümkün olduğunu başa düşürük.

unnest, unnest, ...

Arada "arraylar" əvəzinə bir neçə "sütun array" ötürülməsi variantları ilə rastlaşa bilərik ki, mən də bunun haqqında danışmışdım. keçən yazıda:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Bu üsuldan istifadə edərkən, müxtəlif sütunlar üçün dəyər siyahılarını generasiya edərkən səhv edərək tamamilə gözlənilməz nəticələr əldə etmək çox asandır, server versiyasından da asılı olaraq:

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

PostgreSQL-də 9.3 versiyasından etibarən json tipi ilə işləmək üçün tam funksiyalar ortaya çıxdı. Beləliklə, əgər giriş parametrlərinin təyini brauzerdə baş verirsə, orada birbaşa json-obyektini формировать edə bilərsiniz. SQL sorğusu üçün json-obyektini:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Əvvəlki versiyalar üçün eyni üsulu istifadə etmək mümkündür each(hstore), lakin hstore-də mürəkkəb obyektlərin düzgün "sərtləşdirilməsi" problemlər yarada bilər.

json_populate_recordset

Əgər giriş json-array-dən olan məlumatların hansısa cədvəlin doldurulması üçün istifadə ediləcəyini əvvəldən bilirsinizsə, sahələrin "de-referencing" və lazımi tiplərlə qurulmasında xeyli qənaət edə bilərsiniz, json_populate_recordset funksiyasından istifadə edərək:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

Bu funksiya ötürülmüş obyektlər array-ni seçiminə "açır", cədvəl formatından asılı olmayaraq:

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

TEMPORARY TABLE

Amma əgər ötürülən seçimin məlumat həcmi çox böyükdürsə, bunu bir seri şəklində bir parametrlə yüklemek — çətindir, bəzən isə mümkünsüzdür, çünki tək bir dəfə böyük həcmlərin ayrılmasında tələb olunur. Məsələn, uzun müddət boyunca xarici sistemdən hadisələri toplamaq lazım gəlir və sonra onu bir dəfəlik olaraq DB tərəfdə işləmək istəyirsiniz.

Bu halda ən yaxşı həll yolu mövsümi cədvəlləri:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- həddindən artıqlama ilə dəfələrlə təkrarlayın
...
-- burada bütün bu cədvəllə nəsə faydalı edir

Bu üsul xüsusən böyük verilənlərinin nadir hallarda ötürülməsi üçün yaxşıdır məlumat.
Verilənlərinizin strukturunu təsvir edərkən müvəqqəti cədvəl "adi" cədvəldən yalnız bir cəhətdən fərqlənir sistem cədvəlində pg_class, pg_type, pg_depend, pg_attribute, pg_attrdef, ... – beləliklə, heç bir şeylə.

Buna görə də, çox sayda qısa müddətli bağlantıları olan web sistemlərində hər biri üçün belə bir cədvəl, verilənlər bazası ilə bağlantı bağlandıqda hər dəfə yeni sistem qeydləri yaradır. Nəticədə, TEMP TABLE-dan nəzarətsiz istifadə pg_catalog cədvəllərinin "şişməsinə" səbəb olur və onları istifadə edən bir çox əməliyyatların sürətini azaldır.
Əlbəttə, bununla mübarizədə müntəzəm VACUUM FULL keçidi ilə sistem kataloqu cədvəllərində bu problemi aradan qaldıra bilərik.

Sessiya dəyişənləri

Tutaq ki, əvvəlki halların məlumatlarının emalı bir SQL sorğusu üçün kifayət qədər mürəkkəbdir, amma bunu tez-tez etmək istəyirik. Yəni biz DO-blokundaprosedur emalını istifadə etmək istəyirik, lakin verilənlərin müvəqqəti cədvəllər vasitəsilə ötürülməsi çox baha başa gələcək.

$n-parametrlərdən anonim blokda ötürmə üçün istifadə edə bilmirik. Problemi həll etmək üçün sessiya dəyişənləri və current_setting.

funksiyası köməyimizə gəlir. 9.2 versiyasından əvvəl xüsusi bir ad sahəsi lazım idi custom_variable_classes sessiya dəyişənləri üçün. Hazırda versiyalarda isə təxminən belə yazmaq mümkündür: 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

Digər dəstəklənən prosedur dillərində başqa həllər də tapmaq mümkündür.

Başqa üsulları bilirsiniz? Şərhlərdə paylaşın!

Gentoo inkişaf etdiriciləri Linux nüvəsinin ikili toplanmalarını hazırlamağı nəzərdən keçirirlər

Mənbə: habr.com