pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger
2+ messages / 2 participants
[nested] [flat]
* BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger
@ 2023-11-17 14:31 PG Bug reporting form <noreply@postgresql.org>
2023-11-17 15:17 ` Re: BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2023-11-17 14:31 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: eric.cyr@gmail.com
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=#
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger
2023-11-17 14:31 BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger PG Bug reporting form <noreply@postgresql.org>
@ 2023-11-17 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-11-17 15:17 UTC (permalink / raw)
To: eric.cyr@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-11-17 15:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-17 14:31 BUG #18202: pg_constraint data isn't refreshed when using alter rename to on a constraint trigger PG Bug reporting form <noreply@postgresql.org>
2023-11-17 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
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