From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: eric.cyr@gmail.com
Subject: BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger
Date: Fri, 17 Nov 2023 14:31:35 +0000
Message-ID: <18202-0783b23955b5d2fd@postgresql.org> (raw)
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=#
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, eric.cyr@gmail.com
Subject: Re: BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger
In-Reply-To: <18202-0783b23955b5d2fd@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