PostgreSQL drop function executes given function and doesn't remove trigger function

Question

I created the trigger function with the below code:

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    select pgr_createTopology('public.roads_noded', 0.001);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();

It is located in the public schema. Now I want to drop this function but when I try to drop it executes the given function and the server hangs. After restarting the server, the same thing happens. I can't create or drop any table. Is there a way to remove this trigger function?

Patrick · Accepted Answer · 2022-05-02 09:00:09Z

3

The DROP FUNCTION command is itself a DDL command so the trigger will fire. You should first remove the trigger using DROP EVENT TRIGGER, then the function.

edited May 2, 2022 at 9:00

answered May 2, 2022 at 8:21

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HumanoVirtual Over a year ago

thanks for the response, I tried DROP TRIGGER snitch but it requires a table name. This is an event trigger and not assigned to any table, so, it gives an error without a table name.

Patrick Over a year ago

The command should be DROP EVENT TRIGGER. Answer updated

Collectives™ on Stack Overflow

PostgreSQL drop function executes given function and doesn't remove trigger function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related