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(at)gmail(dot)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.