From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: p2wap3@gmail.com
Subject: BUG #18570: Drop event trigger for DDL finishes successfully but trigger still executed on DDL
Date: Tue, 06 Aug 2024 08:53:19 +0000
Message-ID: <18570-43bc1b85bebd2572@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, p2wap3@gmail.com
Subject: Re: BUG #18570: Drop event trigger for DDL finishes successfully but trigger still executed on DDL
In-Reply-To: <18570-43bc1b85bebd2572@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox