触发功能中的无限循环 [英] Endless loop in trigger function

问题描述

这是一个触发器，可以通过在表上进行插入，更新或删除来调用.保证调用表的所有列都受影响，并且删除表也存在.

This is a trigger that is called by either an insert, update or a delete on a table. It is guaranteed the calling table has all the columns impacted and a deletes table also exists.

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分支触发一个无限循环.可能会有更多问题. 如何解决?

The ELSE branch triggers an endless loop. There may be more problems. How to fix it?

推荐答案

ELSE分支可以从根本上简化.但是还有另外两件事是低效率的/不准确的/危险的:

The ELSE branch can be radically simplified. But a couple more things are inefficient / inaccurate / dangerous:

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-它将再次触发相同的触发器，从而导致无限循环.那是主要的错误.

  • In the ELSE branch just assign to NEW directly. No need for more dynamic SQL - which would fire the same trigger again causing an endless loop. That's the primary error.

    RETURN NEW;会破坏您对DELETE的触发功能，因为未将NEW分配给DELETE.

    RETURN NEW; outside the IF construct would break your trigger function for DELETE, since NEW is not assigned for DELETEs.

    一个主要功能是使用 hstore和hstore运算符#= 可动态更改著名行类型的两个选定字段-当时为 未知 编写代码.这样，您就不会篡改原始的OLD值，如果在事件链中有更多的触发器，则可能会产生令人惊讶的副作用.

    A key feature is the use of hstore and the hstore operator #= to dynamically change two selected fields of the well-known row type - that is unknown at the time of writing the code. This way you do not tamper with the original OLD value, which might have surprising side effect if you have more triggers down the chain of events.

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

    必须安装附加模块hstore.详细信息:

    The additional module hstore must be installed. Details:

    • 如何设置复合变量的值动态SQL字段
    • 动态地为PostgreSQL中的一个记录变量
    • How to set value of composite variable field using dynamic SQL
    • Passing column names dynamically for a record variable in PostgreSQL

    在此处使用 hstore(text[], text[]) 变体来构造hstore具有多个字段的动态值.

    Using the hstore(text[], text[]) variant here to construct an hstore value with multiple fields on the fly.

    plpgsql中的赋值运算符为:=:

    The assignment operator in plpgsql is :=:

    • 被遗忘的赋值运算符"="和普通的":: ="

    请注意，我使用列名mod_datetime而不是误导的mod_date，因为该列显然是timestamp而不是date.

    Note that I used the column name mod_datetime instead of the misleading mod_date, since the column is obviously a timestamp and not a date.

    在此过程中，我还添加了其他一些改进.触发器本身应如下所示:

    I added a couple of other improvements while being at it. And the trigger itself should look like this:

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

    SQL提琴.

    这篇关于触发功能中的无限循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！