ProHoster > Blog > башкаруу > PostgreSQL Antipatterns: SQLге топтомдорду жана тандоолорду өткөрүү

PostgreSQL Antipatterns: SQLге топтомдорду жана тандоолорду өткөрүү

Мезгил-мезгили менен иштеп чыгуучу керек суроо-талапка параметрлердин топтомун же бүтүндөй тандоону өткөрүңүз "кире бериште". Кээде сиз бул маселенин абдан кызыктай чечимдерине туш болосуз.
PostgreSQL Antipatterns: SQLге топтомдорду жана тандоолорду өткөрүү
Келгиле, артка кайрылып, эмне кылбоо керектигин, эмне үчүн жана кантип жакшыраак кыла аларыбызды карап көрөлү.

Сурамдын денесине маанилерди түз киргизүү

Ал, адатта, мындай көрүнөт:

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

...же ушул сыяктуу:

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

Бул ыкма айтылган, жазылган жана ал тургай тартылган көп:

PostgreSQL Antipatterns: SQLге топтомдорду жана тандоолорду өткөрүү

Дээрлик ар дайым бул SQL инъекцияларына түз жол жана суроо сызыгын "жабыштырып" аргасыз болгон бизнес логикасына ашыкча жүк.

Мындай мамиле зарыл болгон учурда гана жарым-жартылай актоого болот бөлүүнү колдонуу натыйжалуураак планды алуу үчүн PostgreSQL 10 жана төмөнкү версияларында. Бул версияларда сканерленген бөлүмдөрдүн тизмеси берилген параметрлерди эсепке албастан, суроо-талаптын органынын негизинде гана аныкталат.

$n-аргументтер

пайдалануунун толтургучтар параметрлери жакшы, ал колдонууга мүмкүндүк берет ДАЯРДАЛГАН БИЛДИРҮҮЛӨР, жүктү кыскартуу бизнес логикасында (суроо сап бир гана жолу түзүлөт жана берилет) жана маалымат базасынын серверинде (ар бир суроонун инстанциясы үчүн кайра талдоо жана пландаштыруу талап кылынбайт).

Аргументтердин өзгөрүлмө саны

Белгисиз сандагы аргументтерди өткөргүбүз келгенде бизди көйгөйлөр күтүп турат:

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

Эгерде биз өтүнүчтү ушул формада калтырсак, ал бизди потенциалдуу инъекциялардан коргогонуна карабастан, бул суроону бириктирүү/талдоо зарылдыгына алып келет аргументтердин санына жараша ар бир вариант үчүн. Муну ар дайым жасагандан көрө жакшы, бирок ансыз деле кыла аласыз.

Камтыган бир эле параметрди өтүү жетиштүү серияланган массивди көрсөтүү:

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

Бир гана айырмасы аргументти каалаган массив түрүнө ачык айландыруунун зарылдыгы. Бирок бул көйгөй жаратпайт, анткени биз кайда бара жатканыбызды алдын ала билебиз.

Үлгү өткөрүү (матрица)

Адатта булар "бир суроо-талапта" маалымат базасына киргизүү үчүн берилиштер топтомун өткөрүп берүүнүн бардык түрлөрү:

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

өтүнүчтү "кайра чаптоо" менен жогоруда сүрөттөлгөн көйгөйлөрдөн тышкары, бул да бизди алып келиши мүмкүн эсинде жок жана сервердин бузулушу. Себеби жөнөкөй - PG аргументтер үчүн кошумча эстутумду сактайт, ал эми топтомдогу жазуулардын саны бизнес логикасынын колдонмо муктаждыктары менен гана чектелет. Өзгөчө клиникалык учурларда мен көрүшүм керек болчу "Сан" аргументтери 9000 доллардан ашат - мындай кылба.

Келгиле, суранычты мурунтан эле колдонуп кайра жазалы "эки баскычтуу" сериялаштыруу:

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;

Ооба, массивдин ичиндеги "татаал" баалуулуктар болсо, алар тырмакчалар менен курчалган болушу керек.
Бул жол менен сиз талаалардын ыктыярдуу саны менен тандоону "кеңейтүү" мүмкүн экени түшүнүктүү.

жок кылуу, уятка калтырбоо, …

Мезгил-мезгили менен мен айткан "массивдердин массивинин" ордуна бир нече "мамычалардын массивдерин" өткөрүү варианттары бар. мурунку макалада:

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

Бул ыкма менен, ар кандай мамычалар үчүн баалуулуктардын тизмесин түзүүдө ката кетирсеңиз, аны алуу абдан оңой. күтүлбөгөн натыйжалар, алар сервердин версиясына да көз каранды:

-- $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 версиясынан бери PostgreSQL json түрү менен иштөө үчүн толук кандуу функцияларга ээ. Ошондуктан, киргизүү параметрлеринин аныктамасы сиздин браузерде пайда болсо, аны ошол жерден түзө аласыз json SQL суроосу үчүн объект:

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

Мурунку версиялар үчүн ушул эле ыкманы колдонсо болот ар бир (hstore), бирок hstore ичиндеги татаал объекттерден качуу менен туура "конволюция" көйгөйлөрдү жаратышы мүмкүн.

json_populate_recordset

Эгер сиз "input" json массивиндеги маалыматтар кээ бир таблицаларды толтуруу үчүн колдонулаарын алдын ала билсеңиз, json_populate_recordset функциясын колдонуу менен "dereferencing" талааларында көп нерсени сактап, аларды керектүү түргө чыгара аласыз:

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

json_to_recordset

Жана бул функция таблица форматына ишенбестен, объекттердин өткөн массивдерин тандоого "кеңейтет":

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

УБАКТЫЛУУ СТОЛ

Ал эми өткөрүлүп берилген үлгүдөгү маалыматтардын көлөмү өтө чоң болсо, анда аны бир катарлаштырылган параметрге ыргытуу кыйын жана кээде мүмкүн эмес, анткени ал бир жолку талап кылат. эстутумдун чоң көлөмүн бөлүү. Мисалы, сиз узак, узак убакыт бою тышкы тутумдан болгон окуялар боюнча маалыматтардын чоң пакетин чогултушуңуз керек, андан кийин аны бир жолу маалымат базасы тарабында иштеткиңиз келет.

Бул учурда, мыкты чечим колдонуу болуп саналат убактылуу столдор:

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

Метод жакшы чоң көлөмдөгү мезгил-мезгили менен которуулар үчүн маалыматтар.
Убактылуу таблица анын берилиштеринин структурасын сүрөттөп берүү жагынан “кадимки” таблицадан бир гана жагынан айырмаланат. pg_class системалык таблицасындажана pg_type, pg_depend, pg_attribute, pg_attrdef, … - эч нерсе эмес.

Ошондуктан, алардын ар бири үчүн көп сандагы кыска мөөнөттүү байланыштары бар веб-системаларда мындай таблица ар бир жолу жаңы системалык жазууларды жаратат, алар маалымат базасына байланыш жабылганда өчүрүлөт. Жыйынтыгында, TEMP TABLEди көзөмөлсүз колдонуу pg_catalog ичиндеги таблицалардын “шишишине” алып келет жана аларды колдонгон көптөгөн операцияларды басаңдатуу.
Албетте, муну колдонуу менен чечсе болот мезгилдүү өтүү VACUUM FULL системалык каталог таблицаларына ылайык.

Сеанс өзгөрмөлөрү

Келгиле, мурунку иштин берилиштерин иштетүү бир SQL суроосу үчүн өтө татаал, бирок сиз муну көп жасагыңыз келет деп коёлу. Башкача айтканда, биз процедуралык процессти колдонгубуз келет БЛОКТОЙТ, бирок убактылуу таблицалар аркылуу берилиштерди өткөрүү өтө кымбатка турат.

Биз ошондой эле анонимдүү блокко өтүү үчүн $n-параметрлерди колдоно албайбыз. Сеанс өзгөрмөлөрү жана функция бул абалдан чыгууга жардам берет учурдагы_жөндөө.

9.2 версиясына чейин алдын ала конфигурациялоо керек болчу атайын аттар мейкиндиги custom_variable_classes "сиздин" сеанс өзгөрмөлөрү үчүн. Учурдагы версияларда сиз төмөнкүдөй нерсени жаза аласыз:

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

Башка чечимдерди башка колдоого алынган процедуралык тилдерде тапса болот.

Башка жолдорун билесизби? Комментарийлерде бөлүшүңүз!

Source: www.habr.com

Комментарий кошуу