PostgreSQL Антипатерни: предаване на набори и селекции в SQL

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

Нека започнем 'от обратно' и да видим какво не трябва да се прави, защо и как може да се направи по-добре.

Пряка 'врезка' на стойности в тялото на заявката

Обикновено изглежда приблизително така:

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

... или така:

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

За този метод е казано, написано и дори нарисувано в достатъчни количества:

Почти винаги това е— пряк път към SQL инжекции и излишно натоварване на бизнес логиката, която е принудена да 'залепва' стринга на вашата заявка.

Частично оправдан подход може да бъде само в случай на необходимост от секциониране в версии PostgreSQL 10 и по-долу за получаване на по-ефективен план. В тези версии списъкът на сканираните секции се определя без да се взимат предвид предаваните параметри, само на базата на тялото на заявката.

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

Използване плейсхолдери параметри — това е добро, тъй като позволява да се използват PREPARED STATEMENTS, намалявайки натоварването както на бизнес логиката (стринг заявката се формира и предава само един път), така и на сървъра на БД (не е необходимо повторно разбиране и планиране за всеки екземпляр на заявката).

Променливо количество аргументи

Проблеми ще ни очакват, когато пожелаем да предадем предварително неизвестно количество аргументи:

... 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 | 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 за предаване в анонимния блок. Решението ще бъдат сесийните променливи и функцията 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