С времена на време, програмер треба проследити скуп параметара или чак цео избор захтеву "на улазу". Понекад постоје веома чудна решења за овај проблем.
Хајдемо „од супротног“ и да видимо како то не радити, зашто и како то учинити боље.
Директно „убацивање“ вредности у тело захтева
Обично изгледа отприлике овако:
query = "SELECT * FROM tbl WHERE id = " + value... или овако:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)О овој методи се каже, пише и довољно:
Скоро увек јесте директан пут до СКЛ ињекције и додатно оптерећење пословне логике, која је принуђена да „залепи“ ваш стринг упита.
Овај приступ се може делимично оправдати само ако је потребно. користите партиционисање у ПостгреСКЛ верзијама 10 и старијим за ефикаснији план. У овим верзијама, листа скенираних секција се утврђује без узимања у обзир пренетих параметара, само на основу тела захтева.
$н аргумената
Коришћење параметри су добри, омогућава вам да користите , смањујући оптерећење и на пословној логици (стринг упита се формира и преноси само једном) и на серверу базе података (поновно анализирање и планирање није потребно за сваку инстанцу захтева).
Променљиви број аргумената
Проблеми ће нас чекати када желимо унапред да пренесемо непознат број аргумената:
... 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),...Поред горе описаних проблема са „поновним лепљењем“ захтева, ово нас такође може довести до тога без меморије и пад сервера. Разлог је једноставан – ПГ резервише додатну меморију за аргументе, а број записа у сету је ограничен само апликацијом пословне логике Висхлист. У посебно клиничким случајевима било је неопходно видети „нумерисани“ аргументи већи од 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 |ЈСОН
Почевши од верзије 9.3, ПостгреСКЛ има пуноправне функције за рад са јсон типом. Стога, ако су ваши улазни параметри дефинисани у претраживачу, можете одмах тамо и формирати јсон објекат за СКЛ упит:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'За претходне верзије, исти метод се може користити за сваки (хстор), али исправно "преклапање" са избегавањем сложених објеката у хсторе-у може изазвати проблеме.
јсон_популате_рецордсет
Ако унапред знате да ће подаци из „инпут“ јсон низа ићи да попуне неку табелу, можете много да уштедите у „дереференцирању“ поља и пребацивању на жељене типове помоћу функције јсон_популате_рецордсет:
SELECT
*
FROM
json_populate_recordset(
NULL::pg_class
, $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
);јсон_то_рецордсет
А ова функција ће једноставно „проширити“ прослеђени низ објеката у селекцију, без ослањања на формат табеле:
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); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
Метода је добра за ретке преносе великих количина podataka.
Са становишта описа структуре својих података, привремена табела се разликује од „обичне“ табеле само по једној особини. у системској табели пг_цласс, и у пг_типе, пг_депенд, пг_аттрибуте, пг_аттрдеф, ... — и баш ништа.
Дакле, у веб системима са великим бројем краткотрајних веза за сваку од њих, таква табела ће сваки пут генерисати нове системске записе, који се бришу када се веза са базом података затвори. Коначно, неконтролисана употреба ТЕМП ТАБЛЕ доводи до "набујања" табела у пг_цаталог и успоравање многих операција које их користе.
Наравно, са овим се може борити периодични пролаз ВАЦУУМ ФУЛЛ према табелама системског каталога.
Променљиве сесије
Претпоставимо да је обрада података из претходног случаја прилично сложена за један СКЛ упит, али желите да то радите прилично често. То јест, желимо да користимо процедуралне обраде у , али коришћење преноса података преко привремених табела биће прескупо.
Такође не можемо да користимо $н-параметре за пролазак у анонимни блок. Променљиве сесије и функција ће нам помоћи да се извучемо из ситуације. Актуелна поставка.
Пре верзије 9.2, морали сте да унапред конфигуришете прилагођене_променљиве_класе за „њихове“ варијабле сесије. На тренутним верзијама можете написати нешто овако:
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Постоје и друга решења доступна на другим подржаним процедуралним језицима.
Знате више начина? Поделите у коментарима!
Извор: ввв.хабр.цом