Как использовать переменные настройки в триггерных функциях?

Я хотел бы записать идентификатор пользователя в сеансе/транзакции, используя SET, поэтому я мог бы получить доступ к нему позже в функции триггера, используя current_setting. В принципе, я пытаюсь выбрать вариант n2 из очень похожего билета, опубликованного ранее, с той разницей, что я использую PG 10.1.

Я пытался 3 подхода к настройке переменной:

SET local myvars.user_id = 4, тем самым установив его локально в транзакции;
SET myvars.user_id = 4, тем самым установив его в сеансе;
SELECT set_config('myvars.user_id', '4', false), который в зависимости от последнего аргумента будет ярлыком для предыдущих двух параметров.

Ни один из них не может использоваться в триггере, который получает NULL при получении переменной через current_setting. Вот сценарий, который я разработал для устранения неполадок (его можно легко использовать с изображением докеры postgres):

database=$POSTGRES_DB
user=$POSTGRES_USER
[ -z "$user" ] && user="postgres"

psql -v ON_ERROR_STOP=1 --username "$user" $database <<-EOSQL
    DROP TRIGGER IF EXISTS add_transition1 ON houses;
    CREATE TABLE IF NOT EXISTS houses (
        id SERIAL NOT NULL,
        name VARCHAR(80),
        created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
        PRIMARY KEY(id)
    );

    CREATE TABLE IF NOT EXISTS transitions1 (
        id SERIAL NOT NULL,
        house_id INTEGER,
        user_id INTEGER,
        created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
        PRIMARY KEY(id),
        FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE

    );

    CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS \$\$
        DECLARE
            user_id integer;
        BEGIN
            user_id := current_setting('myvars.user_id')::integer || NULL;
            INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id);
            RETURN NULL;
        END;
    \$\$ LANGUAGE plpgsql;

    CREATE TRIGGER add_transition1 AFTER INSERT OR UPDATE ON houses FOR EACH ROW EXECUTE PROCEDURE add_transition1();

    BEGIN;
    %1% SELECT current_setting('myvars.user_id');
    %2% SELECT set_config('myvars.user_id', '55', false);
    %3% SELECT current_setting('myvars.user_id');
    INSERT INTO houses (name) VALUES ('HOUSE PARTY') RETURNING houses.id;
    SELECT * from houses;
    SELECT * from transitions1;
    COMMIT;
    DROP TRIGGER IF EXISTS add_transition1 ON houses;
    DROP FUNCTION IF EXISTS add_transition1;
    DROP TABLE transitions1;
        DROP TABLE houses;
EOSQL

Я пришел к выводу, что функция запускается в другой транзакции и другой (?) Сеанс. Это что-то, что можно настроить, чтобы все происходило в одном контексте?

Ответ 1

Обращайтесь со всеми возможными случаями для настраиваемого варианта:

опция еще не установлена

Все ссылки на него вызывают исключение, включая current_setting() если не missing_ok со вторым параметром missing_ok. Руководство:

Если нет настройки имени setting_name, current_setting выдает ошибку, если missing_ok не поставляется и это true.
опция установлена в действительный целочисленный литерал
опция установлена на недопустимый целочисленный литерал
сброс опции (который сжигает до особого случая 3.)

Например, если вы настроили настраиваемый параметр с помощью SET LOCAL или set_config('myvars.user_id3', '55', true), значение опции будет сброшено в конце транзакции. Он по-прежнему существует, можно ссылаться, но теперь он возвращает пустую строку (''), которая не может быть передана integer.

Очевидные ошибки в вашей демонстрации в сторону, вам нужно подготовиться ко всем 4 случаям. Так:

CREATE OR REPLACE FUNCTION add_transition1()
  RETURNS trigger AS
$func$
DECLARE
   _user_id text := current_setting('myvars.user_id', true);  -- see 1.
BEGIN
   IF _user_id ~ '^\d+$' THEN  -- one or more digits?

      INSERT INTO transitions1 (user_id, house_id)
      VALUES (_user_id::int, NEW.id);  -- valid int, cast is safe

   ELSE

      INSERT INTO transitions1 (user_id, house_id)
      VALUES (NULL, NEW.id);           -- use NULL instead

      RAISE WARNING 'Invalid user_id % for house_id % was reset to NULL!'
                  , quote_literal(_user_id), NEW.id;  -- optional
   END IF;

   RETURN NULL;  -- OK for AFTER trigger
END
$func$  LANGUAGE plpgsql;

db <> скрипка здесь

Заметки:

Избегайте имен переменных, соответствующих именам столбцов. Очень склонна к ошибкам. Одно из популярных соглашений об именах состоит в том, чтобы добавлять имена переменных с помощью подчеркивания: _user_id.
Назначьте время объявления, чтобы сохранить одно задание. Обратите внимание на text типа данных. Мы отправим позже, после сортировки недопустимого ввода.
Избегайте, если возможно, поднимать/захватывать исключение. Руководство:

Блок, содержащий предложение EXCEPTION, значительно дороже для входа и выхода, чем блок без него. Поэтому не используйте EXCEPTION без необходимости.
Проверьте правильность целых строк. Это простое регулярное выражение допускает только цифры (без указателей, без пробелов): _user_id ~ '^\d+$'. Я возвращаю значение NULL для любого недопустимого ввода. Адаптируйте к вашим потребностям.
Я добавил дополнительное WARNING для удобства отладки.
Случаи 3. и 4. возникают только потому, что настраиваемые параметры являются строковыми литералами (тип text), допустимые типы данных не могут быть принудительно введены автоматически.

Связанные с:

Все, что в стороне, могут быть более элегантные решения для того, что вы пытаетесь сделать без индивидуальных вариантов, в зависимости от ваших точных требований. Может быть, это:

Самый быстрый способ получить текущий OID пользователя в Postgres?

Ответ 2

Непонятно, почему вы пытаетесь выполнить NULL для user_id но это, очевидно, является причиной проблемы. Избавиться от этого:

CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$
    DECLARE
        user_id integer;
    BEGIN
        user_id := current_setting('myvars.user_id')::integer;
        INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

Обратите внимание, что

SELECT 55 || NULL

всегда дает NULL.

Ответ 3

Вы можете поймать исключение, когда значение не существует - вот изменения, которые я сделал, чтобы заставить это работать:

CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$
    DECLARE
        user_id integer;
    BEGIN
        BEGIN
            user_id := current_setting('myvars.user_id')::integer;
        EXCEPTION WHEN OTHERS THEN
            user_id := 0;
        END;

        INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

 CREATE OR REPLACE FUNCTION insert_house() RETURNS void as $$
 DECLARE
    user_id integer;
 BEGIN 
   PERFORM set_config('myvars.user_id', '55', false);

   INSERT INTO houses (name) VALUES ('HOUSE PARTY');
 END; $$ LANGUAGE plpgsql;