BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger - Mailing list pgsql-bugs

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=#