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.
