The following bug has been logged on the website:
Bug reference: 18202
Logged by: Eric Cyr
Email address: eric.cyr@gmail.com
PostgreSQL version: 14.10
Operating system: Ubuntu 22.04.2 LTS, macOS 14.4.1
Description:
Hi,
When we rename a constraint trigger it seems like the name is not updated in
pg_constraint.
Thank you and have a good day
How to reproduce:
--
--
--
DROP TABLE IF EXISTS public.reproduce_issue;
CREATE TABLE public.reproduce_issue
(
id integer primary key
);
CREATE OR REPLACE FUNCTION public.func_reproduce_issue() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
RETURN NEW;
END;
$$;
CREATE CONSTRAINT TRIGGER cons_trigger_reproduce_issue AFTER INSERT ON
public.reproduce_issue FOR EACH ROW EXECUTE PROCEDURE
public.func_reproduce_issue();
SELECT conname FROM pg_constraint WHERE conname =
'cons_trigger_reproduce_issue';
ALTER TRIGGER cons_trigger_reproduce_issue ON public.reproduce_issue RENAME
TO new_name_cons_trigger_reproduce_issue;
SELECT conname FROM pg_constraint WHERE conname =
'new_name_cons_trigger_reproduce_issue';
SELECT conname FROM pg_constraint WHERE conname =
'cons_trigger_reproduce_issue';
--
--
--
Results
NOTE:
Even when disconnecting/reconnecting or using a new backend connection
pg_constraint still returns the old name of the trigger.
tmp=# SELECT conname FROM pg_constraint WHERE conname =
'cons_trigger_reproduce_issue';
conname
------------------------------
cons_trigger_reproduce_issue
(1 row)
tmp=# ALTER TRIGGER cons_trigger_reproduce_issue ON public.reproduce_issue
RENAME TO new_name_cons_trigger_reproduce_issue;
ALTER TRIGGER
tmp=# SELECT conname FROM pg_constraint WHERE conname =
'new_name_cons_trigger_reproduce_issue';
conname
---------
(0 rows)
tmp=# SELECT conname FROM pg_constraint WHERE conname =
'cons_trigger_reproduce_issue';
conname
------------------------------
cons_trigger_reproduce_issue
(1 row)
tmp=#