ProHoster > Blog > İdarə > PostgreSQL Antipatternləri: Dəstləri və Seçmələri SQL-ə ötürmək

PostgreSQL Antipatternləri: Dəstləri və Seçmələri SQL-ə ötürmək

Zaman zaman tərtibatçıya ehtiyac var sorğuya bir sıra parametrlər və ya hətta bütün seçimi ötürmək "girişdə". Bəzən bu problemin çox qəribə həll yolları var.
PostgreSQL Antipatternləri: Dəstləri və Seçmələri SQL-ə ötürmək
Gəlin "əks tərəfdən" gedək və bunu necə etməməyi, niyə və necə daha yaxşı edə biləcəyinizi görək.

Sorğu orqanına dəyərlərin birbaşa "daxil edilməsi"

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

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

... və ya bu kimi:

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

Bu üsul haqqında deyilir, yazılır və hətta çəkilmişdir yetər:

PostgreSQL Antipatternləri: Dəstləri və Seçmələri SQL-ə ötürmək

Demək olar ki, həmişə belədir SQL inyeksiyasına birbaşa yol və sorğu sətirinizi “yapışdırmağa” məcbur olan iş məntiqinə əlavə yük.

Bu yanaşma yalnız zəruri hallarda qismən əsaslandırıla bilər. bölmədən istifadə edin daha səmərəli plan üçün PostgreSQL 10 və aşağıda versiyalarında. Bu versiyalarda skan edilmiş bölmələrin siyahısı ötürülən parametrlər nəzərə alınmadan, yalnız sorğu orqanı əsasında müəyyən edilir.

$n arqumentlər

Istifadə yer tutanlar parametrləri yaxşıdır, istifadə etməyə imkan verir HAZIRLANMIŞ BƏYANATLAR, həm biznes məntiqində (sorğu sətri yalnız bir dəfə formalaşır və ötürülür), həm də verilənlər bazası serverində (sorğunun hər bir nümunəsi üçün yenidən təhlil və planlaşdırma tələb olunmur) yükün azaldılması.

Arqumentlərin dəyişən sayı

Əvvəlcədən naməlum sayda arqument ötürmək istədikdə bizi problemlər gözləyəcək:

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

Sorğunu bu formada tərk etsəniz, bu, bizi potensial inyeksiyalardan xilas etsə də, yenə də sorğunu yapışdırmaq / təhlil etmək ehtiyacına səbəb olacaq. arqumentlərin sayından hər bir seçim üçün. Onsuz da bunu hər dəfə etməkdən daha yaxşıdır, amma onsuz da edə bilərsiniz.

Yalnız bir parametrdən keçmək kifayətdir massivin seriyalı təsviri:

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

Yeganə fərq, arqumenti açıq şəkildə istədiyiniz massiv tipinə çevirmək ehtiyacıdır. Amma bu, problem yaratmır, çünki biz hara müraciət etdiyimizi əvvəlcədən bilirik.

Nümunə ötürülməsi (matris)

Adətən bunlar verilənlər bazasına "bir sorğuda" daxil etmək üçün məlumat dəstlərinin ötürülməsi üçün hər cür seçimdir:

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

Sorğunun “yenidən yapışdırılması” ilə bağlı yuxarıda təsvir olunan problemlərə əlavə olaraq, bu da bizi buna gətirib çıxara bilər yaddaş dolub və server qəzası. Səbəb sadədir - PG arqumentlər üçün əlavə yaddaş saxlayır və dəstdəki qeydlərin sayı yalnız biznes məntiqi tətbiqi İstək siyahısı ilə məhdudlaşdırılır. Xüsusilə kliniki hallarda görmək lazım idi 9000 dollardan çox "nömrələnmiş" arqumentlər - belə etmə.

Artıq müraciət edərək sorğunu yenidən yazaq "iki səviyyəli" serializasiya:

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, massiv daxilindəki "mürəkkəb" dəyərlər vəziyyətində, onlar dırnaq işarələri ilə çərçivəyə salınmalıdır.
Aydındır ki, bu şəkildə seçimi ixtiyari sayda sahə ilə "genişlədə" bilərsiniz.

yıxmaq, yersizləşdirmək, …

Zaman zaman qeyd etdiyim bir neçə "massivlər massivi" əvəzinə keçid variantları var. son məqalədə:

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

Bu üsulla, müxtəlif sütunlar üçün dəyər siyahıları yaradarkən səhv etsəniz, tamamilə əldə etmək çox asandır. gözlənilməz nəticələr, bu da server versiyasından asılıdır:

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

9.3 versiyasından başlayaraq PostgreSQL json növü ilə işləmək üçün tam hüquqlu funksiyalara malikdir. Buna görə də, əgər sizin giriş parametrləriniz brauzerdə müəyyən edilibsə, siz elə oradan formaya sala bilərsiniz SQL sorğusu üçün json obyekti:

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

Əvvəlki versiyalar üçün eyni üsuldan istifadə edilə bilər hər (hstore), lakin hstore-da mürəkkəb obyektlərdən qaçmaqla düzgün "qatlama" problemlər yarada bilər.

json_populate_recordset

Əgər “input” json massivindən verilənlərin hansısa cədvəli doldurmağa gedəcəyini əvvəlcədən bilsəniz, json_populate_recordset funksiyasından istifadə edərək “referencing” sahələrində çox qənaət edə və istədiyiniz tiplərə köçürə bilərsiniz:

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

json_to_recordset

Və bu funksiya sadəcə olaraq cədvəl formatına etibar etmədən, ötürülən obyektlər massivini seçimə “genişləndirəcək”:

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

MÜVƏQƏTİ MASA

Ancaq ötürülən nümunədəki məlumatların miqdarı çox böyükdürsə, onu bir seriallaşdırılmış parametrə atmaq çətindir və bəzən qeyri-mümkündür, çünki birdəfəlik tələb olunur. böyük yaddaş ayrılması. Məsələn, siz uzun, uzun müddətə xarici sistemdən hadisə məlumatlarının böyük partiyasını toplamalısınız və sonra onu verilənlər bazası tərəfində birdəfəlik emal etmək istəyirsiniz.

Bu vəziyyətdə ən yaxşı həll istifadə etmək olardı müvəqqəti masalar:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

Metod yaxşıdır böyük həcmlərin nadir hallarda ötürülməsi üçün məlumatlar.
Verilənlərin strukturunu təsvir etmək baxımından müvəqqəti cədvəl “adi” cədvəldən yalnız bir xüsusiyyətinə görə fərqlənir. pg_class sistem cədvəlində, içində isə pg_type, pg_depend, pg_atribut, pg_attrdef, ... - və ümumiyyətlə heç nə.

Buna görə də, onların hər biri üçün çoxlu sayda qısamüddətli əlaqələri olan veb sistemlərdə belə bir cədvəl hər dəfə yeni sistem qeydləri yaradacaq, verilənlər bazası ilə əlaqə bağlandıqda silinir. Nəhayət, TEMP TABLE-dən nəzarətsiz istifadə pg_catalog-da cədvəllərin "şişməsinə" gətirib çıxarır və onlardan istifadə edən bir çox əməliyyatları yavaşlatmaq.
Təbii ki, bununla mübarizə aparmaq olar dövri keçid VACUUM FULL sistem kataloqu cədvəllərinə uyğun olaraq.

Sessiya Dəyişənləri

Tutaq ki, əvvəlki halda olan məlumatların emalı bir SQL sorğusu üçün olduqca mürəkkəbdir, lakin siz bunu tez-tez etmək istəyirsiniz. Yəni prosessual emaldan istifadə etmək istəyirik blok edin, lakin müvəqqəti cədvəllər vasitəsilə məlumat ötürülməsindən istifadə çox bahalı olacaq.

Biz həmçinin anonim bloka keçmək üçün $n-parametrlərdən istifadə edə bilmərik. Sessiya dəyişənləri və funksiya vəziyyətdən çıxmağımıza kömək edəcək. cari_ayar.

9.2 versiyasından əvvəl siz əvvəlcədən konfiqurasiya etməli idiniz xüsusi ad sahəsi xüsusi_dəyişən_siniflər "onların" sessiya dəyişənləri üçün. Cari versiyalarda belə bir şey yaza bilərsiniz:

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ə mövcuddur.

Daha çox yol bilirsiniz? Şərhlərdə paylaşın!

Mənbə: www.habr.com

Добавить комментарий