Thread: BUG #18570: Drop event trigger for DDL finishes successfully but trigger still executed on DDL

The following bug has been logged on the website:

Bug reference:      18570
Logged by:          Paul P
Email address:      p2wap3@gmail.com
PostgreSQL version: 16.1
Operating system:   AWS RDS
Description:

I created a function and an event trigger but then decided to drop the event
trigger (w/o dropping the function yet).

Creation script:

```
CREATE OR REPLACE FUNCTION public.awsdms_intercept_ddl()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
  declare _qry text;
BEGIN
  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE'
or tg_tag = 'CREATE TABLE AS') then
        SELECT current_query() into _qry;
        insert into public.awsdms_ddl_audit
        values
        (
        default,current_timestamp,current_user,cast(TXID_CURRENT()as
varchar(16)),tg_tag,0,'',current_schema,_qry
        );
        delete from public.awsdms_ddl_audit;
 end if;
END;
$function$
;

CREATE EVENT TRIGGER awsdms_intercept_ddl ON ddl_command_end
EXECUTE FUNCTION public.awsdms_intercept_ddl()
```

After some time I decided to drop the event trigger. The command is executed
successfully (DROP EVENT TRIGGER <name> CASCADE) 
I double checked by querying the information_schema.triggers (the list is
empty)

But every time I try to execute a DDL operation, I encounter an error saying
that DDL function was called.
The error was saying the operation failed because awsdms_intercept_ddl()
function. 
Why the function is called if the event trigger is supposedly dropped. 
Your documentation says nothing about caching event triggers , especially if
information_schema.triggers was updated.

Eventually, the solution that worked for me was to drop the function.

Btw, at first when I tried to drop the function, I have got this error:
```
DROP function IF EXISTS awsdms_intercept_ddl;
ERROR:  cannot drop function awsdms_intercept_ddl() because other objects
depend on it 
DETAIL:  event trigger awsdms_intercept_ddl depends on function
awsdms_intercept_ddl()
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
```

I think this is undesirable behavior , if user just wants to drop the
trigger w/o removing the function it self.

On 2024-Aug-06, PG Bug reporting form wrote:

> After some time I decided to drop the event trigger. The command is executed
> successfully (DROP EVENT TRIGGER <name> CASCADE) 
> I double checked by querying the information_schema.triggers (the list is
> empty)
> 
> But every time I try to execute a DDL operation, I encounter an error saying
> that DDL function was called.
> The error was saying the operation failed because awsdms_intercept_ddl()
> function. 

Maybe you're dropping the event trigger in one database, but it still
exist in other databases.  For example, if you originally created it in
template1 and it got propagated by CREATE DATABASE into some new one,
then if you drop it from template1 it'll still exist in the databases
you created later.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
¡Ay, ay, ay!  Con lo mucho que yo lo quería (bis)
se fue de mi vera ... se fue para siempre, pa toíta ... pa toíta la vida
¡Ay Camarón! ¡Ay Camarón!                                (Paco de Lucía)

PG Bug reporting form <noreply@postgresql.org> writes:
> I created a function and an event trigger but then decided to drop the event
> trigger (w/o dropping the function yet).

DROP EVENT TRIGGER works as expected for me.  Can you provide
a fully self-contained example of what you did that didn't work?
The most obvious explanation is that you forgot to commit the DROP,
or something along that line.

> I double checked by querying the information_schema.triggers (the list is
> empty)

That proves exactly nothing, because event triggers are never shown
in that view anyway.  They are not a SQL-standard feature, but
information_schema is all about SQL-standard things.

psql's "\dy" command could be used to check for event triggers.

            regards, tom lane