BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger

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