Бесконечный цикл в функции триггера

Question

Это триггер, который вызывается либо вставкой, обновлением или удалением таблицы. Гарантируется, что в вызывающей таблице затронуты все столбцы, и таблица удалений также существует.

CREATE OR REPLACE FUNCTION sample_trigger_func() RETURNS TRIGGER AS $$
DECLARE
    operation_code char;
    table_name varchar(50);
    delete_table_name varchar(50);
    old_id integer; 

BEGIN
table_name = TG_TABLE_NAME;
delete_table_name = TG_TABLE_NAME || '_deletes';

SELECT SUBSTR(TG_OP, 1, 1)::CHAR INTO operation_code;

IF TG_OP = 'DELETE' THEN
    OLD.mod_op = operation_code;
    OLD.mod_date = now();

    RAISE INFO 'OLD: %', (OLD).name;

    EXECUTE format('INSERT INTO %s VALUES %s', delete_table_name, (OLD).*);

ELSE
    EXECUTE format('UPDATE TABLE %s SET mod_op = %s AND mod_date = %s'
                  , TG_TABLE_NAME, operation_code, now());
END IF;

RETURN NEW;
END;

$$ LANGUAGE plpgsql;

ELSE ветвь запускает бесконечный цикл. Там может быть больше проблем. Как это исправить?

Answer 1

ELSE Ветка может быть радикально упрощена. Но еще пара вещей неэффективны / неточны / опасны:

CREATE OR REPLACE FUNCTION sample_trigger_func()
  RETURNS TRIGGER AS
$func$
BEGIN
   IF TG_OP = 'DELETE' THEN
      RAISE INFO 'OLD: %', OLD.name;

      EXECUTE format('INSERT INTO %I SELECT ($1).*', TG_TABLE_NAME || '_deletes')
      USING OLD #= hstore('{mod_op, mod_datetime}'::text[]
                         , ARRAY[left(TG_OP, 1), now()::text]);
      RETURN OLD;
   ELSE  -- insert, update
      NEW.mod_op       := left(TG_OP, 1);
      NEW.mod_datetime := now();

      RETURN NEW;
   END IF;
END
$func$  LANGUAGE plpgsql;

• в ELSE филиал просто назначить NEW непосредственно. Нет необходимости в более динамическом SQL, который снова запускает тот же триггер, вызывая бесконечный цикл. Это основная ошибка.

• RETURN NEW; вне IF конструкция сломает вашу триггерную функцию для DELETE, поскольку NEW не назначен для УДАЛЕНИЯ.

• Ключевой особенностью является использование hstore и оператор магазина #= динамически изменять два выбранных поля хорошо известного типа строки - что неизвестно на момент написания кода. Таким образом, вы не вмешиваться в оригинал OLD значение, которое может иметь неожиданный побочный эффект, если у вас есть больше триггеров в цепочке событий.

  OLD #= hstore('{mod_op, mod_datetime}'::text[]
               , ARRAY[left(TG_OP, 1), now()::text]);
  

  Дополнительный модуль hstore должен быть установлен. Подробности:

  • Как установить значение поля составной переменной с помощью динамического SQL
  • Динамическая передача имен столбцов для переменной записи в PostgreSQL

  С использованием hstore(text[], text[]) вариант здесь, чтобы построить hstore значение с несколькими полями на лету.

• Оператор присваивания в plpgsql :=:

  • Забытый оператор присваивания "=" и банальное ":="

• Обратите внимание, что я использовал имя столбца mod_datetime вместо того, чтобы вводить в заблуждение mod_date поскольку столбец, очевидно, timestamp и не date,

Я добавил несколько других улучшений, находясь в этом. И сам триггер должен выглядеть так:

CREATE TRIGGER insupdel_bef
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW EXECUTE PROCEDURE sample_trigger_func();

SQL Fiddle.