Вопрос:

Повторное использование входных данных, проанализированных в json, в функции postgres plpgsql

postgresql exception insert plpgsql

145 просмотра

3 ответа

4538 Репутация автора

У меня есть функция plpgsql, которая принимает jsonbвходные данные и использует их, чтобы сначала что-то проверить, а затем снова в запросе, чтобы получить результаты. Что-то вроде:

CREATE OR REPLACE FUNCTION public.my_func(
    a jsonb,
    OUT inserted integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF
AS $function$
BEGIN
    -- fail if there's something already there 
    IF EXISTS(
    select t.x from jsonb_populate_recordset(null::my_type, a) f inner join some_table t
    on f.x = t.x and
       f.y = t.y
    ) THEN
    RAISE EXCEPTION 'concurrency violation... already present.';
    END IF;

    -- straight insert, and collect number of inserted
    WITH inserted_rows AS (
        INSERT INTO some_table (x, y, z)
        SELECT f.x, f.y, f.z
        FROM jsonb_populate_recordset(null::my_type, a) f
        RETURNING 1
    )
    SELECT count(*) from inserted_rows INTO inserted
    ;
END

Здесь я использую jsonb_populate_recordset(null::my_type, a)как в IFпроверке, а также в фактической вставке. Есть ли способ выполнить разбор один раз - возможно, с помощью какой-то переменной? Или оптимизатор запросов включится и будет гарантировать, что операция разбора произойдет только один раз?

Автор: ashic Источник Размещён: 02.03.2017 11:28

Ответы (3)


0 плюса

2013 Репутация автора

Если я правильно понимаю, вы смотрите на что-то вроде этого:

CREATE OR REPLACE FUNCTION public.my_func(
    a jsonb,
    OUT inserted integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF
AS $function$
BEGIN
    WITH checked_rows AS (
        SELECT f.x, f.y, f.z, t.x IS NOT NULL as present
        FROM jsonb_populate_recordset(null::my_type, a) f
        LEFT join some_table t
            on f.x = t.x and f.y = t.y
    ), vioalted_rows AS (
        SELECT count(*) AS violated FROM checked_rows AS c WHERE c.present
    ), inserted_rows AS (
        INSERT INTO some_table (x, y, z)
        SELECT c.x, c.y, c.z
        FROM checked_rows AS c
        WHERE (SELECT violated FROM vioalted_rows) = 0
        RETURNING 1
    )
    SELECT count(*) from inserted_rows INTO inserted
    ;

    IF inserted = 0 THEN 
        RAISE EXCEPTION 'concurrency violation... already present.';
    END IF;

END;
$function$;
Автор: Roman Tkachuk Размещён: 02.03.2017 03:10

0 плюса

8248 Репутация автора

JSONB-тип не нужно анализировать более одного раза при назначении:

в то время как данные jsonb хранятся в разложенном двоичном формате, что делает его немного медленнее для ввода из-за дополнительных издержек преобразования, но значительно быстрее для обработки, так как повторный анализ не требуется.

Ссылка

jsonb_populate_recordsetфункция объявлена ​​как STABLE:

STABLE указывает, что функция не может изменить базу данных и что при сканировании одной таблицы она будет последовательно возвращать один и тот же результат для тех же значений аргумента, но что ее результат может изменяться в операторах SQL.

Ссылка

Я не уверен в этом. С одной стороны, вызов UDF рассматривается как отдельные операторы, с другой стороны, UDF может содержать несколько операторов. Требуется уточнение.

Наконец, если вы хотите кешировать такие записи, вы можете использовать массивы:

CREATE OR REPLACE FUNCTION public.my_func(
    a jsonb,
    OUT inserted integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF
AS $function$
DECLARE
    d my_type[]; -- There is variable for caching 
BEGIN
    select array_agg(f) into d from jsonb_populate_recordset(null::my_type, a) as f;
    -- fail if there's something already there 
    IF EXISTS(
      select *
      from some_table t
      where (t.x, t.y) in (select x, y from unnest(d)))
    THEN
      RAISE EXCEPTION 'concurrency violation... already present.';
    END IF;

    -- straight insert, and collect number of inserted
    WITH inserted_rows AS (
        INSERT INTO some_table (x, y, z)
        SELECT f.x, f.y, f.z
        FROM unnest(d) f
        RETURNING 1
    )
    SELECT count(*) from inserted_rows INTO inserted;
END $function$;
Автор: Abelisto Размещён: 02.03.2017 03:39

0 плюса

379311 Репутация автора

Если вы действительно хотите многократно использовать набор результатов , общим решением будет временная таблица. Пример:

Однако это довольно дорого. Похоже, все, что вам нужно, это UNIQUEограничение или индекс:

Простой и безопасный с UNIQUEограничениями

ALTER TABLE some_table ADD CONSTRAINT some_table_x_y_uni UNIQUE (x,y);

В отличие от вашей процессуальной попытки, это также безопасно для параллелизма (без условий гонки). Гораздо быстрее тоже.

Тогда функция может быть очень простой:

CREATE OR REPLACE FUNCTION public.my_func(a jsonb, OUT inserted integer) AS
$func$
BEGIN
   INSERT INTO some_table (x, y, z)
   SELECT f.x, f.y, f.z
   FROM   jsonb_populate_recordset(null::my_type, a) f;

   GET DIAGNOSTICS inserted = ROW_COUNT;  -- OUT param, we're done here
END
$func$  LANGUAGE plpgsql;

Если что-то (x,y)уже присутствует, some_tableвы получите свое исключение. Выберите инструктивное имя для ограничения, которое сообщается в сообщении об ошибке.

И мы можем просто прочитать командную метку с помощью GET DIAGNOSTICS, что существенно дешевле, чем выполнить другой запрос подсчета.

Связанный:

UNIQUE ограничение не возможно?

Для маловероятного случая, когда UNIQUEограничение не должно быть выполнимым, вы все равно можете сделать его довольно простым:

CREATE OR REPLACE FUNCTION public.my_func(a jsonb, OUT inserted integer) AS
$func$
BEGIN
   INSERT INTO some_table (x, y, z)
   SELECT f.x, f.y, f.z  -- empty result set if there are any violations
   FROM  (
      SELECT f.x, f.y, f.z, count(t.x) OVER () AS conflicts
      FROM   jsonb_populate_recordset(null::my_type, a) f
      LEFT   JOIN some_table t USING (x,y)
      ) f
   WHERE  f.conflicts = 0;

   GET DIAGNOSTICS inserted = ROW_COUNT;

   IF inserted = 0 THEN
      RAISE EXCEPTION 'concurrency violation... already present.';
   END IF;

END
$func$  LANGUAGE plpgsql;

Подсчитайте количество нарушений в одном запросе. (count () учитывает только ненулевые значения). Связанный:

В some_table (x,y)любом случае у вас должен быть хотя бы простой индекс .

Важно знать, что plpgsql не возвращает результаты, пока управление не выйдет из функции. Исключение отменяет возврат, пользователь никогда не получает результаты, только сообщение об ошибке. Мы добавили пример кода в руководство.

Однако обратите внимание, что здесь есть условия гонки при одновременной загрузке записи. Связанный:

Будет ли планировщик запросов избегать повторной оценки?

Конечно, не между несколькими операторами SQL.

Даже если сама функция определена STABLEили IMMUTABLE( jsonb_populate_recordset()в примере есть STABLE), планировщик запросов не знает, что значения входных параметров неизменны между вызовами. Было бы дорого следить и следить за этим.
На самом деле, поскольку plpgsql рассматривает операторы SQL как подготовленные операторы, это просто невозможно, поскольку запрос планируется до того, как значения параметров будут переданы в запланированный запрос.

Автор: Erwin Brandstetter Размещён: 03.03.2017 01:10
Вопросы из категории :
32x32