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.