PostgreSQL Antipatterns: Предаване на набори и избори към SQL

От време на време разработчикът се нуждае подайте набор от параметри или дори цяла селекция към заявката "на входа". Понякога има много странни решения на този проблем.

Нека да отидем "от обратното" и да видим как да не го правим, защо и как можете да го направите по-добре.

Директно "вмъкване" на стойности в тялото на заявката

Обикновено изглежда по следния начин:

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),...

Освен описаните по-горе проблеми с "презалепването" на заявката, това може да ни доведе и до недостатъчна памет и срив на сървъра. Причината е проста - PG запазва допълнителна памет за аргументите, а броят на записите в набора е ограничен само от приложението Wishlist на бизнес логиката. В особено клинични случаи е необходимо да се види "номерирани" аргументи, по-големи от $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

Ако знаете предварително, че данните от „входния“ 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 | 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 заявка, но искате да я правите доста често. Тоест искаме да използваме процедурна обработка в DO блок, но използването на пренос на данни чрез временни таблици ще бъде твърде скъпо.

Също така не можем да използваме $n-параметри за преминаване към анонимен блок. Сесийните променливи и функцията ще ни помогнат да излезем от ситуацията. текуща_настройка.

Преди версия 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

Има други решения, налични на други поддържани процедурни езици.

Знаете ли още начини? Споделете в коментарите!

Източник: www.habr.com