ProHoster > Блог > адміністрування > 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-аргументи

Використання плейсхолдерів параметрів - це добре, воно дозволяє використовувати 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),...

Крім описаних вище проблем із «переклеюванням» запиту, це нас може призвести ще й до недостатньо помяті та падіння сервера. Причина проста - під аргументи 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

TEMPORARY 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-запиту, але робити її хочеться досить часто. Тобто ми хочемо використовувати процедурну обробку в DO-блоці, але використовувати передачу даних через тимчасові таблиці буде занадто дорого.

Використовувати $n-параметри передачі в анонімний блок ми теж зможемо. Вийти зі становища нам допоможуть змінні сесії та функція current_setting.

До версії 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

На інших підтримуваних мовах можна знайти й інші рішення.

Чи знаєте ще способи? Поділіться у коментарях!

Джерело: habr.com

Додати коментар або відгук