Перыядычна ў распрацоўніка ўзнікае неабходнасць перадаць у запыт набор параметраў ці нават цэлую выбарку "на ўваход". Часам трапляюцца вельмі дзіўныя рашэнні гэтай задачы.
Пойдзем "ад зваротнага" і паглядзім, як рабіць не варта, чаму, і як можна зрабіць лепш.
Прамая «ўрэзка» значэнняў у цела запыту
Выглядае звычайна прыкладна так:
query = "SELECT * FROM tbl WHERE id = " + value… ці так:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Пра гэты спосаб сказана, напісана і звышдастаткова:
Амаль заўсёды гэта - прамы шлях да 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),...Апроч апісаных вышэй праблем з «пераклейваннем» запыту, гэта нас можа прывесці яшчэ і да out of memory і падзення сервера. Чыннік простая - пад аргументы 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;
Так, у выпадку "складаных" значэнняў усярэдзіне масіва, іх патрабуецца апраўляць двукоссямі.
Зразумела, што такім спосабам можна "разгарнуць" выбарку з адвольнай колькасцю палёў.
unnest, unnest, …
Перыядычна сустракаюцца варыянты перадачы замест "масіву масіваў" некалькіх "масіваў слупкоў", пра якія я згадваў :
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}'Для папярэдніх версій такі ж спосаб можна выкарыстоўваць для each(hstore), Але карэктная «скрутка» з экранаваннем складаных аб'ектаў у hstore можа выклікаць праблемы.
json_populate_recordset
Калі вы загадзя ведаеце, што дадзеныя з "уваходнага" json-масіва пайдуць для запаўнення нейкай табліцы, можна моцна зэканоміць у "разнайменні" палёў і прывядзенні да патрэбных тыпаў, скарыстаўшыся функцыяй json_populate_recordset:
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 | 2TEMPORARY TABLE
Але калі аб'ём дадзеных у перадаваемай выбарцы вельмі вялікі, то закінуць яго ў адзін серыялізаваны параметр - цяжка, а часам і немагчыма, паколькі патрабуе разавага вылучэнні вялікага аб'ёму памяці. Напрыклад, вам неабходна доўга-доўга збіраць вялікі пакет дадзеных па падзеях са знешняй сістэмы, а потым хочаце разава яго апрацаваць на баку БД.
У гэтым выпадку лепшым рашэннем стане выкарыстанне :
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, … - дык і зусім нічым.
Таму ў web-сістэмах з вялікай колькасцю кароткажывучых падлучэнняў для кожнага з іх такая табліца будзе спараджаць новыя сістэмныя запісы кожны раз, якія выдаляюцца з зачыненнем злучэння з БД. У выніку, некантралюемае выкарыстанне TEMP TABLE прыводзіць да "распухання" табліц у pg_catalog і запаволенні шматлікіх аперацый, якія выкарыстоўваюць іх.
Вядома, з гэтым можна дужацца з дапамогай перыядычнага праходу VACUUM FULL па табліцах сістэмнага каталога.
Пераменныя сесіі
Выкажам здагадку, апрацоўка дадзеных з папярэдняга выпадку досыць складаная для аднаго SQL-запыту, але рабіць яе жадаецца досыць часта. Гэта значыць, мы хочам выкарыстоўваць працэдурную апрацоўку ў , Але выкарыстоўваць перадачу дадзеных праз часовыя табліцы будзе занадта накладна.
Выкарыстоўваць $n-параметры для перадачы ў ананімны блок мы таксама не зможам. Выйсці са становішча нам дапамогуць зменныя сесіі і функцыя current_setting.
Да версіі 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На іншых працэдурных мовах, якія падтрымліваюцца, можна знайсці і іншыя рашэнні.
Ведаеце яшчэ спосабы? Падзяліцеся ў каментарах!
Крыніца: habr.com