pg_event_trigger_ddl_commands() ERROR: stack depth limit exceeded

32 views Asked by At

I've got 3 databases and they are on different hosts. There is event trigger, which is the same as on other bases, but on one base it doesnt work.

Here is a code. Its not full, but I left part of code which doesnt work

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$
;

and the error is

SQL Error [54001]: ERROR: stack depth limit exceeded Подсказка: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate. Где: SQL statement "select * from pg_event_trigger_ddl_commands()" PL/pgSQL function monitoring.fn_rdwh_ddl_trigger() line 10 at FOR over SELECT rows SQL statement "drop table if exists pub_tab"

I think that it is because statement "drop table if exists pub_tab;" again call itself. But why on other bases it works fine?

Table pub_tab is a temp table. I need it to make some calculations. There is no other table with this name in DB

Important!!! Version of Postgres where trigger function doesnt work is 13 and where works 11

0

There are 0 answers