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

PG Bug reporting form <noreply@postgresql.org> writes:
> When we rename a constraint trigger it seems like the name is not updated in
> pg_constraint.

Hmm, yeah ... renametrig() is intent on renaming the entry in pg_trigger,
but I see no code there that pays any attention to pg_constraint.
Conversely, ALTER TABLE RENAME CONSTRAINT updates the pg_constraint
entry but not the associated trigger.  It's not real clear to me
whether this is intentional.  There's no a-priori reason why the
two objects have to have the same name; but I doubt we have pg_dump
support for recreating a situation where they are different.
[ experiments ... ] Interestingly, pg_dump seems to dump the name
from pg_trigger not the one from pg_constraint.

            regards, tom lane