From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | p2wap3(at)gmail(dot)com |
Subject: | BUG #18570: Drop event trigger for DDL finishes successfully but trigger still executed on DDL |
Date: | 2024-08-06 08:53:19 |
Message-ID: | 18570-43bc1b85bebd2572@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksander Alekseev | 2024-08-06 09:09:30 | Re: BUG #18568: BUG: Result wrong when do group by on partition table! |
Previous Message | Tender Wang | 2024-08-06 08:42:53 | Re: BUG #18568: BUG: Result wrong when do group by on partition table! |