PostgreSQL 13事件触发器执行DROP TABLE时栈深度超限问题求助

问题描述 #

我有3个部署在不同主机上的PostgreSQL数据库，配置了相同的DDL事件触发器，但其中一个PostgreSQL 13版本的数据库上触发器无法正常工作，PostgreSQL 11版本的则运行正常。

触发器函数关键代码：

CREATE OR REPLACE FUNCTION monitoring.fn_rdwh_ddl_trigger()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
    declare message_error _text;
    obj_table _text;
    obj_trigger record; 
    
    BEGIN
    
    for obj_trigger in select * from pg_event_trigger_ddl_commands()
    loop
        if obj_trigger.object_type = 'table' and obj_trigger.command_tag = 'ALTER TABLE' then
            obj_table := array_append(obj_table,obj_trigger.object_identity);
            end if;
    end loop;

   drop table if exists pub_tab;

    END;
    $function$
;

执行时触发错误：

SQL Error [54001]: ERROR: stack depth limit exceeded
提示：在确认平台栈深度限制足够后，增加配置参数"max_stack_depth"（当前为2048kB）。
位置：SQL语句"select * from pg_event_trigger_ddl_commands()"
PL/pgSQL函数monitoring.fn_rdwh_ddl_trigger()第10行FOR循环处
SQL语句"drop table if exists pub_tab"

我推测是执行drop table if exists pub_tab;时递归触发了触发器自身，但疑惑为何PostgreSQL 11版本可正常运行？
注：pub_tab为临时表，仅用于计算，库中无同名其他表。

问题根源 #

PostgreSQL版本迭代中调整了DDL事件触发器的触发规则：

• PostgreSQL 11及更早版本：临时表的DDL操作（包括DROP TABLE）不会触发全局DDL事件触发器，所以执行DROP TABLE IF EXISTS pub_tab时不会递归调用触发器函数，自然不会出现栈溢出。
• PostgreSQL 12及之后版本：临时表的DDL操作会触发全局DDL事件触发器，当你的触发器函数执行DROP TABLE IF EXISTS pub_tab时，会再次触发同一个DDL触发器，形成无限递归，最终耗尽栈空间触发错误。

解决方案 #

要避免递归触发，有两种可行方案：

方案1：在触发器函数中跳过临时表的递归触发 #

修改触发器函数，在执行核心逻辑前判断当前操作是否是针对目标临时表的DROP操作，直接跳过：

CREATE OR REPLACE FUNCTION monitoring.fn_rdwh_ddl_trigger()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
    declare message_error _text;
    obj_table _text;
    obj_trigger record; 
    
    BEGIN
    -- 跳过针对pub_tab的DROP操作，避免递归
    if current_query() ~* 'DROP TABLE IF EXISTS pub_tab' then
        RETURN;
    end if;
    
    for obj_trigger in select * from pg_event_trigger_ddl_commands()
    loop
        if obj_trigger.object_type = 'table' and obj_trigger.command_tag = 'ALTER TABLE' then
            obj_table := array_append(obj_table,obj_trigger.object_identity);
        end if;
    end loop;

    drop table if exists pub_tab;

    END;
    $function$
;

方案2：执行DROP操作时临时禁用触发器 #

使用SET LOCAL临时切换会话复制角色，临时禁用事件触发器，执行完DROP操作后恢复：

CREATE OR REPLACE FUNCTION monitoring.fn_rdwh_ddl_trigger()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
    declare message_error _text;
    obj_table _text;
    obj_trigger record; 
    
    BEGIN
    for obj_trigger in select * from pg_event_trigger_ddl_commands()
    loop
        if obj_trigger.object_type = 'table' and obj_trigger.command_tag = 'ALTER TABLE' then
            obj_table := array_append(obj_table,obj_trigger.object_identity);
        end if;
    end loop;

    -- 临时禁用事件触发器，避免递归
    SET LOCAL session_replication_role = replica;
    drop table if exists pub_tab;
    -- 恢复触发器
    SET LOCAL session_replication_role = DEFAULT;

    END;
    $function$
;

补充说明 #

• 方案1的判断逻辑可以更精准，比如通过pg_event_trigger_ddl_commands()返回的schema_name判断是否为临时表专属的pg_temp_xxx模式。
• 方案2需要函数执行者拥有足够权限（通常为超级用户权限）来修改session_replication_role参数。

内容的提问来源于stack exchange，提问作者Gerzzog