PostgreSQL Antipatterns: передача наборов и выборок в 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

    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 необходимо было предварительно сконфигурировать специальное пространство имен 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

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

    Знаете еще способы? Поделитесь в комментариях!
    Тензор
    Разработчик системы СБИС

    Похожие публикации

    Комментарии 3

      0
      >Проблемы будут ждать нас, когда мы захотим передать заранее неизвестное количество аргументов
      А в чем проблема?
        0
        … приведет к необходимости склейки/разбора запроса для каждого варианта от количества аргументов.
        0

        JDBC позволяет работать с массивами нативно, и не нужно городить огород с сериализацией и временными таблицами.
        https://jdbc.postgresql.org/documentation/head/arrays.html


        Spring также поддерживает это и с JdbcTemplate и с JPA. Напоминаю, что для получения текущего коннекта в Spring нужно использовать DataSourceUtils. См исходный код для понимания того, как именно это нужно делать.


        Кстати для JPA, если вы хотите хранить массивы Java в таблице как есть, используя нативные типы Postgres, то можно не тратить время на написание TypeDescriptor-ов самому, а просто взять готовую библиотеку от славного Vlad Mihalcea, использование которой он описал тут: https://vladmihalcea.com/how-to-map-java-and-sql-arrays-with-jpa-and-hibernate/

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое