Thread: Command order bug in pg_dump

tested against 706cbed351037fb5e886815506515d1281e62d40

Execute this in first db (say, db1):

```
create table tfk (i int unique) partition by range (i );
create table tfk_po partition of tfk for values from ( 0 ) to (1);
create table tt (i int) partition by range (i );
create table tt_po partition of tt for values from ( 0 ) to (1);
ALTER TABLE public.tt
    ADD CONSTRAINT tt_i_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
ALTER TABLE public.tt
    ADD CONSTRAINT tt_a_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
```

create new database and dump-restore

./pgbin/bin/pg_dump -d db1 --schema-only > dump-p.sql
./pgbin/bin/createdb db2


restore fails

db2=# \i dump-p.sql
SET
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER INDEX
ALTER TABLE
psql:dump-p.sql:120: ERROR:  constraint "tt_i_fkey" for relation "tt"
already exists
db2=#


This bug is about now postgresql chooses generated name for inherited contains

I think this is a problem, when pg_dump creates sql which is
non-applicable for restore. Bug discovered when digging out pg_upgarde
failure reasons.


-- 
Best regards,
Kirill Reshke

Kirill Reshke <reshkekirill@gmail.com> writes:
> psql:dump-p.sql:120: ERROR:  constraint "tt_i_fkey" for relation "tt"
> already exists

I don't think this is pg_dump's fault: there is no such constraint
when the ALTER TABLE starts.  Something inside the ALTER TABLE
recursion seems to be messing up if there is already another
similar FK constraint.  This trace is pretty interesting:

regression=# create database db1;
CREATE DATABASE
regression=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# create table tfk (i int unique) partition by range (i );
CREATE TABLE
db1=# create table tfk_po partition of tfk for values from ( 0 ) to (1);
CREATE TABLE
db1=# create table tt (i int) partition by range (i );
CREATE TABLE
db1=# create table tt_po partition of tt for values from ( 0 ) to (1);
CREATE TABLE
db1=# ALTER TABLE public.tt
db1-#     ADD CONSTRAINT tt_a_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
ALTER TABLE
db1=# ALTER TABLE public.tt
db1-#     ADD CONSTRAINT tt_i_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
ERROR:  constraint "tt_i_fkey" for relation "tt" already exists
db1=# ALTER TABLE public.tt drop CONSTRAINT tt_a_fkey;
ALTER TABLE
db1=# ALTER TABLE public.tt                           
    ADD CONSTRAINT tt_i_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
ALTER TABLE

Doesn't seem to be a new problem, either ... this trace is against
v13.

            regards, tom lane

I wrote:
> I don't think this is pg_dump's fault: there is no such constraint
> when the ALTER TABLE starts.  Something inside the ALTER TABLE
> recursion seems to be messing up if there is already another
> similar FK constraint.

Oh, I see what's happening.  We create both the specified constraint
and an inherited child constraint on the named partitioned table.
If tt_a_fkey is added first, the name chosen for its child is
"tt_i_fkey", breaking the later attempt by the user to use that name.
Apparently there is some hack in psql to not show that child
constraint entry, because you don't see it in \d output, which
is what confused me before (and would confuse most people
hitting this).

We could fool around with the generation rule for the child
constraint's name, but fundamentally we're infringing on user
namespace here, so I think that's likely to just move the problem
cases around.  Why do we need this child pg_constraint entry at all?

If we can't avoid having it, probably choosing a name like
"tt_a_fkey1" would be marginally less likely to cause trouble
... but only marginally.

In any case, it's pretty awful that we make these entries but
\d does not show them.

            regards, tom lane

On Mon, 21 Apr 2025 at 19:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Doesn't seem to be a new problem, either ... this trace is against
> v13.

Sure, repro was on 12=>13 pg_upgrade.


> We could fool around with the generation rule for the child
constraint's name, but fundamentally we're infringing on user
namespace here, so I think that's likely to just move the problem
cases around.

My view of this problem is that pg_dump fails its purpose (to produce
restorable dump) because... Lack of control? What if we can force
inherited child constraint names?
So, along with AT ADD CONSTRAINT, we can provide a list of names and
say: instead of using a constraint name generation rule, the server
should choose these names in order. I understand this is too much code
for this minor matter, and the fix will be probably just to change
generation rule.

> Why do we need this child pg_constraint entry at all?

Currently no idea.


> In any case, it's pretty awful that we make these entries but
\d does not show them.

Okay... Perhaps, but since the user did not specifically request this,
perhaps we shouldn't display it.

-- 
Best regards,
Kirill Reshke

On Mon, 21 Apr 2025 at 22:30, Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> My view of this problem is that pg_dump fails its purpose (to produce
> restorable dump) because... Lack of control? What if we can force
> inherited child constraint names?
> So, along with AT ADD CONSTRAINT, we can provide a list of names and
> say: instead of using a constraint name generation rule, the server
> should choose these names in order.

Forget this nonsense, this is a bad idea.

-- 
Best regards,
Kirill Reshke

On 2025-Apr-21, Tom Lane wrote:

> I don't think this is pg_dump's fault: there is no such constraint
> when the ALTER TABLE starts.  Something inside the ALTER TABLE
> recursion seems to be messing up if there is already another
> similar FK constraint.  This trace is pretty interesting:
> 
> regression=# create database db1;
> CREATE DATABASE
> regression=# \c db1
> You are now connected to database "db1" as user "postgres".
> db1=# create table tfk (i int unique) partition by range (i );
> CREATE TABLE
> db1=# create table tfk_po partition of tfk for values from ( 0 ) to (1);
> CREATE TABLE
> db1=# create table tt (i int) partition by range (i );
> CREATE TABLE
> db1=# create table tt_po partition of tt for values from ( 0 ) to (1);
> CREATE TABLE
> db1=# ALTER TABLE public.tt
> db1-#     ADD CONSTRAINT tt_a_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
> ALTER TABLE
> db1=# ALTER TABLE public.tt
> db1-#     ADD CONSTRAINT tt_i_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
> ERROR:  constraint "tt_i_fkey" for relation "tt" already exists

Oh hah.  So constraint tt_a_fkey has chosen the name tt_i_fkey for its
internal sub-constraint object to point to the partitions, so when the
user wants to created tt_i_fkey, the name available anymore.

Maybe we can change the naming policy so that these internal constraint
objects have names that are unlikely to be chosen by users, maybe by
suffixing "fkey_int" instead of "fkey", or something like that.  (We
could even do "$1" and so on for this kind of constraint).  In
hindsight, it isn't such a great idea to let the system choose the best
name for an internal implementation object.

I'd probably not change this in versions 13 and 14 at all in any case,
because the code is too different.  I'm unsure whether this is enough of
a bug to consider backpatching to 15-17; maybe we should just change 18
at this point, since I haven't heard of a user complaining about this.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Maybe we can change the naming policy so that these internal constraint
> objects have names that are unlikely to be chosen by users, maybe by
> suffixing "fkey_int" instead of "fkey", or something like that.  (We
> could even do "$1" and so on for this kind of constraint).  In
> hindsight, it isn't such a great idea to let the system choose the best
> name for an internal implementation object.

I experimented with the attached, which approximates "add some digits
to the name used for the parent constraint".  (We could refactor
ChooseConstraintName if we wanted a less approximate version of that
rule, but I'm not sure it's worth the trouble.)

The extent to which these derived names leak out to the user, as
illustrated by the regression test changes, makes me even less happy
about the fact that \d doesn't show them.  I think we really ought
to try to find a way to not need these entries.  But that is clearly
not v18 material at this point.

> I'd probably not change this in versions 13 and 14 at all in any case,
> because the code is too different.  I'm unsure whether this is enough of
> a bug to consider backpatching to 15-17; maybe we should just change 18
> at this point, since I haven't heard of a user complaining about this.

Kirill's complaint isn't that?  But I agree that changing this rule in
stable branches would probably be a net negative user experience,
seeing that the names are plenty user-visible.

            regards, tom lane

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 265b1c397fb..3c263583451 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10712,15 +10712,24 @@ addFkConstraint(addFkConstraintSides fkside,

     /*
      * Caller supplies us with a constraint name; however, it may be used in
-     * this partition, so come up with a different one in that case.
+     * this partition, so come up with a different one in that case.  Unless
+     * truncation to NAMEDATALEN dictates otherwise, the new name will be the
+     * supplied name with digit(s) appended.
      */
     if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
                              RelationGetRelid(rel),
                              constraintname))
-        conname = ChooseConstraintName(RelationGetRelationName(rel),
-                                       ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
+    {
+        char       *trimname = pstrdup(constraintname);
+        int            trimlen = strlen(trimname);
+
+        if (trimlen > 5 && strcmp(trimname + trimlen - 5, "_fkey") == 0)
+            trimname[trimlen - 5] = '\0';
+        conname = ChooseConstraintName(trimname,
+                                       NULL,
                                        "fkey",
                                        RelationGetNamespace(rel), NIL);
+    }
     else
         conname = constraintname;

diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 92e441a16cd..ad69d2fbbd6 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -641,9 +641,9 @@ CREATE TABLE parted_fk_naming_1 (
 );
 ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
 SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
-            conname
---------------------------------
- parted_fk_naming_1_id_abc_fkey
+      conname
+-------------------
+ dummy_constr_fkey
 (1 row)

 DROP TABLE parted_fk_naming;
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index c49abc3f0f6..bf68a4b1ab6 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -3306,7 +3306,7 @@ INSERT INTO fk_r_1 (id, p_id, p_jd) VALUES (2, 1, 2); -- should fail
 ERROR:  insert or update on table "fk_r_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey"
 DETAIL:  Key (p_id, p_jd)=(1, 2) is not present in table "fk_p".
 DELETE FROM fk_p; -- should fail
-ERROR:  update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_1_p_id_p_jd_fkey1" on table
"fk_r_1"
+ERROR:  update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey7" on table "fk_r_1"
 DETAIL:  Key (id, jd)=(1, 1) is still referenced from table "fk_r_1".
 ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);
 ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index e38472079cc..32a9863bee0 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -2319,7 +2319,7 @@ UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-
 -- should fail:
 UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
   WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey"on table "temporal_partitioned_fk_rng2rng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_fkey" on
table"temporal_partitioned_fk_rng2rng" 
 DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table
"temporal_partitioned_fk_rng2rng".
 --
 -- partitioned FK referenced deletes NO ACTION
@@ -2331,7 +2331,7 @@ INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[
 DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- should fail:
 DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey"on table "temporal_partitioned_fk_rng2rng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_fkey" on
table"temporal_partitioned_fk_rng2rng" 
 DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table
"temporal_partitioned_fk_rng2rng".
 --
 -- partitioned FK referenced updates CASCADE
@@ -2441,7 +2441,7 @@ UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016
 -- should fail:
 UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01'))
   WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1"on table "temporal_partitioned_fk_mltrng2mltrng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_mltrng2mltrng_fk_fkey1"on table "temporal_partitioned_fk_mltrng2mltrng" 
 DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table
"temporal_partitioned_fk_mltrng2mltrng".
 --
 -- partitioned FK referenced deletes NO ACTION
@@ -2453,7 +2453,7 @@ INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALU
 DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01',
'2018-03-01'));
 -- should fail:
 DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01',
'2018-02-01'));
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1"on table "temporal_partitioned_fk_mltrng2mltrng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_mltrng2mltrng_fk_fkey1"on table "temporal_partitioned_fk_mltrng2mltrng" 
 DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table
"temporal_partitioned_fk_mltrng2mltrng".
 --
 -- partitioned FK referenced updates CASCADE

On 2025-Apr-21, Tom Lane wrote:

> I experimented with the attached, which approximates "add some digits
> to the name used for the parent constraint".  (We could refactor
> ChooseConstraintName if we wanted a less approximate version of that
> rule, but I'm not sure it's worth the trouble.)

This seems a better implementation of the idea than what I had in mind.
For my part, please feel free to go ahead with this.  (I can focus on
the remaining problems with self-referencing FKs ... sigh)

> The extent to which these derived names leak out to the user, as
> illustrated by the regression test changes, makes me even less happy
> about the fact that \d doesn't show them.  I think we really ought
> to try to find a way to not need these entries.  But that is clearly
> not v18 material at this point.

Well, for starters we could change psql to show the hidden constraint
names.  Since this appears to be a problem for users, maybe even change
\d+ to show them in backbranches.  For the longer term (19) I can try to
work on reimplementing things so that these names don't appear anywhere
and are more collision-resistant (though frankly I would prefer to spend
my time on REPACK and column reordering)

> > I'd probably not change this in versions 13 and 14 at all in any case,
> > because the code is too different.  I'm unsure whether this is enough of
> > a bug to consider backpatching to 15-17; maybe we should just change 18
> > at this point, since I haven't heard of a user complaining about this.
> 
> Kirill's complaint isn't that?

I didn't see it as one as first, but I reread his OP and now I agree
that it is.


FWIW I've been migrating my main email address to a new domain.  Finally
got sick of noip.com.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La vida es para el que se aventura"

Alvaro Herrera <alvherre@kurilemu.de> writes:
> On 2025-Apr-21, Tom Lane wrote:
>> I experimented with the attached, which approximates "add some digits
>> to the name used for the parent constraint".  (We could refactor
>> ChooseConstraintName if we wanted a less approximate version of that
>> rule, but I'm not sure it's worth the trouble.)

> This seems a better implementation of the idea than what I had in mind.
> For my part, please feel free to go ahead with this.

OK.  I'll take a look first at whether the aforesaid refactoring
is easy enough to be worth doing.

> FWIW I've been migrating my main email address to a new domain.  Finally
> got sick of noip.com.

Got it, thanks for the heads-up.

            regards, tom lane

I wrote:
> Alvaro Herrera <alvherre@kurilemu.de> writes:
>> On 2025-Apr-21, Tom Lane wrote:
>>> I experimented with the attached, which approximates "add some digits
>>> to the name used for the parent constraint".  (We could refactor
>>> ChooseConstraintName if we wanted a less approximate version of that
>>> rule, but I'm not sure it's worth the trouble.)

>> This seems a better implementation of the idea than what I had in mind.
>> For my part, please feel free to go ahead with this.

> OK.  I'll take a look first at whether the aforesaid refactoring
> is easy enough to be worth doing.

After poking at that, it's easy to get ChooseConstraintName to do
something just slightly different from what I said above: the rule is
now "add an underscore and some digits to the name used for the parent
constraint".  I like this even better than the previous idea, because
I think it makes it more obvious that the name is derived from the
parent constraint.  However, this changes the chosen name in more
cases than my previous hack did.  So I'm reposting the patch to see
if anyone feels this is too much churn.  I think it's okay as a
v18-only patch, though I wouldn't propose it for back-patch.

            regards, tom lane

diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 70528679e57..2d5ac1ea813 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -495,6 +495,8 @@ ConstraintNameExists(const char *conname, Oid namespaceid)
  * name1, name2, and label are used the same way as for makeObjectName(),
  * except that the label can't be NULL; digits will be appended to the label
  * if needed to create a name that is unique within the specified namespace.
+ * If the given label is empty, we only consider names that include at least
+ * one added digit.
  *
  * 'others' can be a list of string names already chosen within the current
  * command (but not yet reflected into the catalogs); we will not choose
@@ -523,8 +525,11 @@ ChooseConstraintName(const char *name1, const char *name2,

     conDesc = table_open(ConstraintRelationId, AccessShareLock);

-    /* try the unmodified label first */
-    strlcpy(modlabel, label, sizeof(modlabel));
+    /* try the unmodified label first, unless it's empty */
+    if (label[0] != '\0')
+        strlcpy(modlabel, label, sizeof(modlabel));
+    else
+        snprintf(modlabel, sizeof(modlabel), "%s%d", label, ++pass);

     for (;;)
     {
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 265b1c397fb..2705cf11330 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10712,14 +10712,16 @@ addFkConstraint(addFkConstraintSides fkside,

     /*
      * Caller supplies us with a constraint name; however, it may be used in
-     * this partition, so come up with a different one in that case.
+     * this partition, so come up with a different one in that case.  Unless
+     * truncation to NAMEDATALEN dictates otherwise, the new name will be the
+     * supplied name with an underscore and digit(s) appended.
      */
     if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
                              RelationGetRelid(rel),
                              constraintname))
-        conname = ChooseConstraintName(RelationGetRelationName(rel),
-                                       ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
-                                       "fkey",
+        conname = ChooseConstraintName(constraintname,
+                                       NULL,
+                                       "",
                                        RelationGetNamespace(rel), NIL);
     else
         conname = constraintname;
diff --git a/src/test/isolation/expected/detach-partition-concurrently-2.out
b/src/test/isolation/expected/detach-partition-concurrently-2.out
index 6f025d81f5e..10cce9044f3 100644
--- a/src/test/isolation/expected/detach-partition-concurrently-2.out
+++ b/src/test/isolation/expected/detach-partition-concurrently-2.out
@@ -41,7 +41,7 @@ a

 step s3i1: INSERT INTO d_lp_fk_r VALUES (1);
 step s2d: ALTER TABLE d_lp_fk DETACH PARTITION d_lp_fk_1 CONCURRENTLY;
-ERROR:  removing partition "d_lp_fk_1" violates foreign key constraint "d_lp_fk_r_a_fkey1"
+ERROR:  removing partition "d_lp_fk_1" violates foreign key constraint "d_lp_fk_r_a_fkey_1"
 step s1c: COMMIT;

 starting permutation: s1b s1s s3i2 s2d s1c
diff --git a/src/test/isolation/expected/detach-partition-concurrently-4.out
b/src/test/isolation/expected/detach-partition-concurrently-4.out
index b652522e424..79b29ae4c10 100644
--- a/src/test/isolation/expected/detach-partition-concurrently-4.out
+++ b/src/test/isolation/expected/detach-partition-concurrently-4.out
@@ -298,7 +298,7 @@ step s1updcur: update d4_fk set a = 1 where current of f;
 step s2detach: alter table d4_primary detach partition d4_primary1 concurrently; <waiting ...>
 step s1c: commit;
 step s2detach: <... completed>
-ERROR:  removing partition "d4_primary1" violates foreign key constraint "d4_fk_a_fkey1"
+ERROR:  removing partition "d4_primary1" violates foreign key constraint "d4_fk_a_fkey_1"

 starting permutation: s2snitch s1b s1s s2detach s3insert s1c
 step s2snitch: insert into d4_pid select pg_backend_pid();
diff --git a/src/test/isolation/expected/fk-partitioned-1.out b/src/test/isolation/expected/fk-partitioned-1.out
index 45f2f8cba71..686f7184d0b 100644
--- a/src/test/isolation/expected/fk-partitioned-1.out
+++ b/src/test/isolation/expected/fk-partitioned-1.out
@@ -54,7 +54,7 @@ step s2a: alter table pfk attach partition pfk1 for values in (1);
 step s1d: delete from ppk1 where a = 1; <waiting ...>
 step s2c: commit;
 step s1d: <... completed>
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;

 starting permutation: s1b s2b s2a s2c s1d s1c
@@ -63,7 +63,7 @@ step s2b: begin;
 step s2a: alter table pfk attach partition pfk1 for values in (1);
 step s2c: commit;
 step s1d: delete from ppk1 where a = 1;
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;

 starting permutation: s2b s1b s1d s1c s2a s2c
@@ -92,7 +92,7 @@ step s2a: alter table pfk attach partition pfk1 for values in (1);
 step s1d: delete from ppk1 where a = 1; <waiting ...>
 step s2c: commit;
 step s1d: <... completed>
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;

 starting permutation: s2b s1b s2a s2c s1d s1c
@@ -101,7 +101,7 @@ step s1b: begin;
 step s2a: alter table pfk attach partition pfk1 for values in (1);
 step s2c: commit;
 step s1d: delete from ppk1 where a = 1;
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;

 starting permutation: s2b s2a s1b s1d s2c s1c
@@ -111,7 +111,7 @@ step s1b: begin;
 step s1d: delete from ppk1 where a = 1; <waiting ...>
 step s2c: commit;
 step s1d: <... completed>
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;

 starting permutation: s2b s2a s1b s2c s1d s1c
@@ -120,7 +120,7 @@ step s2a: alter table pfk attach partition pfk1 for values in (1);
 step s1b: begin;
 step s2c: commit;
 step s1d: delete from ppk1 where a = 1;
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;

 starting permutation: s2b s2a s2c s1b s1d s1c
@@ -129,5 +129,5 @@ step s2a: alter table pfk attach partition pfk1 for values in (1);
 step s2c: commit;
 step s1b: begin;
 step s1d: delete from ppk1 where a = 1;
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;
diff --git a/src/test/isolation/expected/fk-partitioned-2.out b/src/test/isolation/expected/fk-partitioned-2.out
index 8c6c714d059..db621bee2d6 100644
--- a/src/test/isolation/expected/fk-partitioned-2.out
+++ b/src/test/isolation/expected/fk-partitioned-2.out
@@ -57,7 +57,7 @@ step s2i: insert into pfk values (1);
 step s1d: delete from ppk where a = 1; <waiting ...>
 step s2c: commit;
 step s1d: <... completed>
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;

 starting permutation: s1b s2bs s2i s1d s2c s1c
@@ -72,5 +72,5 @@ step s2i: insert into pfk values (1);
 step s1d: delete from ppk where a = 1; <waiting ...>
 step s2c: commit;
 step s1d: <... completed>
-ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey_1" on table "pfk"
 step s1c: commit;
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 92e441a16cd..eba4c0be0d8 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -641,9 +641,9 @@ CREATE TABLE parted_fk_naming_1 (
 );
 ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
 SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
-            conname
---------------------------------
- parted_fk_naming_1_id_abc_fkey
+    conname
+----------------
+ dummy_constr_1
 (1 row)

 DROP TABLE parted_fk_naming;
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index c49abc3f0f6..d05c6b1ac5c 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1903,20 +1903,20 @@ ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitione
 -- Constraint will be invalid.
 SELECT conname, convalidated FROM pg_constraint
 WHERE conrelid = 'fk_notpartitioned_fk'::regclass ORDER BY oid::regclass::text;
-            conname             | convalidated
---------------------------------+--------------
- fk_notpartitioned_fk_a_b_fkey  | f
- fk_notpartitioned_fk_a_b_fkey1 | f
+             conname             | convalidated
+---------------------------------+--------------
+ fk_notpartitioned_fk_a_b_fkey   | f
+ fk_notpartitioned_fk_a_b_fkey_1 | f
 (2 rows)

 ALTER TABLE fk_notpartitioned_fk VALIDATE CONSTRAINT fk_notpartitioned_fk_a_b_fkey;
 -- All constraints are now valid.
 SELECT conname, convalidated FROM pg_constraint
 WHERE conrelid = 'fk_notpartitioned_fk'::regclass ORDER BY oid::regclass::text;
-            conname             | convalidated
---------------------------------+--------------
- fk_notpartitioned_fk_a_b_fkey  | t
- fk_notpartitioned_fk_a_b_fkey1 | t
+             conname             | convalidated
+---------------------------------+--------------
+ fk_notpartitioned_fk_a_b_fkey   | t
+ fk_notpartitioned_fk_a_b_fkey_1 | t
 (2 rows)

 DROP TABLE fk_notpartitioned_fk, fk_partitioned_pk;
@@ -2589,40 +2589,40 @@ INSERT into pk VALUES (1), (1000), (2000), (3000), (4000), (4500);
 INSERT into fk VALUES (1), (1000), (2000), (3000), (4000), (4500);
 -- should fail: referencing value present
 DELETE FROM pk WHERE a = 1;
-ERROR:  update or delete on table "pk1" violates foreign key constraint "fk_a_fkey1" on table "fk"
+ERROR:  update or delete on table "pk1" violates foreign key constraint "fk_a_fkey_1" on table "fk"
 DETAIL:  Key (a)=(1) is still referenced from table "fk".
 DELETE FROM pk WHERE a = 1000;
-ERROR:  update or delete on table "pk2" violates foreign key constraint "fk_a_fkey2" on table "fk"
+ERROR:  update or delete on table "pk2" violates foreign key constraint "fk_a_fkey_2" on table "fk"
 DETAIL:  Key (a)=(1000) is still referenced from table "fk".
 DELETE FROM pk WHERE a = 2000;
-ERROR:  update or delete on table "pk3" violates foreign key constraint "fk_a_fkey3" on table "fk"
+ERROR:  update or delete on table "pk3" violates foreign key constraint "fk_a_fkey_3" on table "fk"
 DETAIL:  Key (a)=(2000) is still referenced from table "fk".
 DELETE FROM pk WHERE a = 3000;
-ERROR:  update or delete on table "pk4" violates foreign key constraint "fk_a_fkey4" on table "fk"
+ERROR:  update or delete on table "pk4" violates foreign key constraint "fk_a_fkey_4" on table "fk"
 DETAIL:  Key (a)=(3000) is still referenced from table "fk".
 DELETE FROM pk WHERE a = 4000;
-ERROR:  update or delete on table "pk51" violates foreign key constraint "fk_a_fkey6" on table "fk"
+ERROR:  update or delete on table "pk51" violates foreign key constraint "fk_a_fkey_6" on table "fk"
 DETAIL:  Key (a)=(4000) is still referenced from table "fk".
 DELETE FROM pk WHERE a = 4500;
-ERROR:  update or delete on table "pk52" violates foreign key constraint "fk_a_fkey7" on table "fk"
+ERROR:  update or delete on table "pk52" violates foreign key constraint "fk_a_fkey_7" on table "fk"
 DETAIL:  Key (a)=(4500) is still referenced from table "fk".
 UPDATE pk SET a = 2 WHERE a = 1;
-ERROR:  update or delete on table "pk1" violates foreign key constraint "fk_a_fkey1" on table "fk"
+ERROR:  update or delete on table "pk1" violates foreign key constraint "fk_a_fkey_1" on table "fk"
 DETAIL:  Key (a)=(1) is still referenced from table "fk".
 UPDATE pk SET a = 1002 WHERE a = 1000;
-ERROR:  update or delete on table "pk2" violates foreign key constraint "fk_a_fkey2" on table "fk"
+ERROR:  update or delete on table "pk2" violates foreign key constraint "fk_a_fkey_2" on table "fk"
 DETAIL:  Key (a)=(1000) is still referenced from table "fk".
 UPDATE pk SET a = 2002 WHERE a = 2000;
-ERROR:  update or delete on table "pk3" violates foreign key constraint "fk_a_fkey3" on table "fk"
+ERROR:  update or delete on table "pk3" violates foreign key constraint "fk_a_fkey_3" on table "fk"
 DETAIL:  Key (a)=(2000) is still referenced from table "fk".
 UPDATE pk SET a = 3002 WHERE a = 3000;
-ERROR:  update or delete on table "pk4" violates foreign key constraint "fk_a_fkey4" on table "fk"
+ERROR:  update or delete on table "pk4" violates foreign key constraint "fk_a_fkey_4" on table "fk"
 DETAIL:  Key (a)=(3000) is still referenced from table "fk".
 UPDATE pk SET a = 4002 WHERE a = 4000;
-ERROR:  update or delete on table "pk51" violates foreign key constraint "fk_a_fkey6" on table "fk"
+ERROR:  update or delete on table "pk51" violates foreign key constraint "fk_a_fkey_6" on table "fk"
 DETAIL:  Key (a)=(4000) is still referenced from table "fk".
 UPDATE pk SET a = 4502 WHERE a = 4500;
-ERROR:  update or delete on table "pk52" violates foreign key constraint "fk_a_fkey7" on table "fk"
+ERROR:  update or delete on table "pk52" violates foreign key constraint "fk_a_fkey_7" on table "fk"
 DETAIL:  Key (a)=(4500) is still referenced from table "fk".
 -- now they should work
 DELETE FROM fk;
@@ -2661,19 +2661,19 @@ CREATE TABLE dropfk (a int REFERENCES droppk);
 INSERT into dropfk VALUES (1), (1000), (1500), (2000);
 -- these should all fail
 ALTER TABLE droppk DETACH PARTITION droppk_d;
-ERROR:  removing partition "droppk_d" violates foreign key constraint "dropfk_a_fkey5"
+ERROR:  removing partition "droppk_d" violates foreign key constraint "dropfk_a_fkey_5"
 DETAIL:  Key (a)=(2000) is still referenced from table "dropfk".
 ALTER TABLE droppk2 DETACH PARTITION droppk2_d;
-ERROR:  removing partition "droppk2_d" violates foreign key constraint "dropfk_a_fkey4"
+ERROR:  removing partition "droppk2_d" violates foreign key constraint "dropfk_a_fkey_4"
 DETAIL:  Key (a)=(1500) is still referenced from table "dropfk".
 ALTER TABLE droppk DETACH PARTITION droppk1;
-ERROR:  removing partition "droppk1" violates foreign key constraint "dropfk_a_fkey1"
+ERROR:  removing partition "droppk1" violates foreign key constraint "dropfk_a_fkey_1"
 DETAIL:  Key (a)=(1) is still referenced from table "dropfk".
 ALTER TABLE droppk DETACH PARTITION droppk2;
-ERROR:  removing partition "droppk2" violates foreign key constraint "dropfk_a_fkey2"
+ERROR:  removing partition "droppk2" violates foreign key constraint "dropfk_a_fkey_2"
 DETAIL:  Key (a)=(1000) is still referenced from table "dropfk".
 ALTER TABLE droppk2 DETACH PARTITION droppk21;
-ERROR:  removing partition "droppk21" violates foreign key constraint "dropfk_a_fkey3"
+ERROR:  removing partition "droppk21" violates foreign key constraint "dropfk_a_fkey_3"
 DETAIL:  Key (a)=(1000) is still referenced from table "dropfk".
 -- dropping partitions is disallowed
 DROP TABLE droppk_d;
@@ -2742,15 +2742,15 @@ SELECT pg_describe_object('pg_constraint'::regclass, oid, 0), confrelid::regclas
 FROM pg_catalog.pg_constraint
 WHERE conrelid IN (SELECT relid FROM pg_partition_tree('fk'))
 ORDER BY conrelid::regclass::text, conname;
-         pg_describe_object         | confrelid |               case
-------------------------------------+-----------+-----------------------------------
+         pg_describe_object         | confrelid |                case
+------------------------------------+-----------+------------------------------------
  constraint fk_a_fkey on table fk   | pk        | TOP
- constraint fk_a_fkey1 on table fk  | pk1       | constraint fk_a_fkey on table fk
- constraint fk_a_fkey2 on table fk  | pk11      | constraint fk_a_fkey1 on table fk
- constraint fk_a_fkey3 on table fk  | pk2       | constraint fk_a_fkey on table fk
- constraint fk_a_fkey4 on table fk  | pk3       | constraint fk_a_fkey on table fk
- constraint fk_a_fkey5 on table fk  | pk31      | constraint fk_a_fkey4 on table fk
- constraint fk_a_fkey6 on table fk  | pk32      | constraint fk_a_fkey4 on table fk
+ constraint fk_a_fkey_1 on table fk | pk1       | constraint fk_a_fkey on table fk
+ constraint fk_a_fkey_2 on table fk | pk11      | constraint fk_a_fkey_1 on table fk
+ constraint fk_a_fkey_3 on table fk | pk2       | constraint fk_a_fkey on table fk
+ constraint fk_a_fkey_4 on table fk | pk3       | constraint fk_a_fkey on table fk
+ constraint fk_a_fkey_5 on table fk | pk31      | constraint fk_a_fkey_4 on table fk
+ constraint fk_a_fkey_6 on table fk | pk32      | constraint fk_a_fkey_4 on table fk
  constraint fk_a_fkey on table fk1  | pk        | constraint fk_a_fkey on table fk
  constraint fk_a_fkey on table fk11 | pk        | constraint fk_a_fkey on table fk1
  constraint fk_a_fkey on table fk2  | pk        | constraint fk_a_fkey on table fk
@@ -2853,10 +2853,10 @@ CREATE TABLE pt1 PARTITION OF pt1_2 FOR VALUES IN (1);
 CREATE TABLE pt2 PARTITION OF pt1_2 FOR VALUES IN (2);
 CREATE TABLE ref(f1 int, f2 int, f3 int);
 ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;
-ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey1
+ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey_1
   DEFERRABLE INITIALLY DEFERRED;    -- fails
-ERROR:  cannot alter constraint "ref_f1_f2_fkey1" on relation "ref"
-DETAIL:  Constraint "ref_f1_f2_fkey1" is derived from constraint "ref_f1_f2_fkey" of relation "ref".
+ERROR:  cannot alter constraint "ref_f1_f2_fkey_1" on relation "ref"
+DETAIL:  Constraint "ref_f1_f2_fkey_1" is derived from constraint "ref_f1_f2_fkey" of relation "ref".
 HINT:  You may alter the constraint it derives from instead.
 ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey
   DEFERRABLE INITIALLY DEFERRED;
@@ -2958,7 +2958,7 @@ ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk ON UPDATE RESTRICT ON DELETE RE
 CREATE TABLE fk_d PARTITION OF fk DEFAULT;
 INSERT INTO fk VALUES (20), (30);
 DELETE FROM pk WHERE a = 20;
-ERROR:  update or delete on table "pk11" violates RESTRICT setting of foreign key constraint "fk_a_fkey2" on table
"fk"
+ERROR:  update or delete on table "pk11" violates RESTRICT setting of foreign key constraint "fk_a_fkey_2" on table
"fk"
 DETAIL:  Key (a)=(20) is referenced from table "fk".
 UPDATE pk SET a = 90 WHERE a = 30;
 ERROR:  update or delete on table "pk" violates RESTRICT setting of foreign key constraint "fk_a_fkey" on table "fk"
@@ -3306,7 +3306,7 @@ INSERT INTO fk_r_1 (id, p_id, p_jd) VALUES (2, 1, 2); -- should fail
 ERROR:  insert or update on table "fk_r_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey"
 DETAIL:  Key (p_id, p_jd)=(1, 2) is not present in table "fk_p".
 DELETE FROM fk_p; -- should fail
-ERROR:  update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_1_p_id_p_jd_fkey1" on table
"fk_r_1"
+ERROR:  update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey_7" on table "fk_r_1"
 DETAIL:  Key (id, jd)=(1, 1) is still referenced from table "fk_r_1".
 ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);
 ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
@@ -3326,13 +3326,13 @@ Foreign-key constraints:
 Number of partitions: 1 (Use \d+ to list them.)

 DELETE FROM fk_p; -- should fail
-ERROR:  update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey2" on table "fk_r"
+ERROR:  update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey_2" on table "fk_r"
 DETAIL:  Key (id, jd)=(1, 1) is still referenced from table "fk_r".
 -- these should all fail
 ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
 ERROR:  cannot drop inherited constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_1"
-ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1;
-ERROR:  cannot drop inherited constraint "fk_r_p_id_p_jd_fkey1" of relation "fk_r"
+ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey_1;
+ERROR:  cannot drop inherited constraint "fk_r_p_id_p_jd_fkey_1" of relation "fk_r"
 ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
 ERROR:  cannot drop inherited constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_2"
 SET client_min_messages TO warning;
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index e38472079cc..ea607bed0a4 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -2319,7 +2319,7 @@ UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-
 -- should fail:
 UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
   WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey"on table "temporal_partitioned_fk_rng2rng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_1" on
table"temporal_partitioned_fk_rng2rng" 
 DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table
"temporal_partitioned_fk_rng2rng".
 --
 -- partitioned FK referenced deletes NO ACTION
@@ -2331,7 +2331,7 @@ INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[
 DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- should fail:
 DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey"on table "temporal_partitioned_fk_rng2rng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_1" on
table"temporal_partitioned_fk_rng2rng" 
 DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table
"temporal_partitioned_fk_rng2rng".
 --
 -- partitioned FK referenced updates CASCADE
@@ -2441,7 +2441,7 @@ UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016
 -- should fail:
 UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01'))
   WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1"on table "temporal_partitioned_fk_mltrng2mltrng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_mltrng2mltrng_fk_2"
ontable "temporal_partitioned_fk_mltrng2mltrng" 
 DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table
"temporal_partitioned_fk_mltrng2mltrng".
 --
 -- partitioned FK referenced deletes NO ACTION
@@ -2453,7 +2453,7 @@ INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALU
 DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01',
'2018-03-01'));
 -- should fail:
 DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01',
'2018-02-01'));
-ERROR:  update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1"on table "temporal_partitioned_fk_mltrng2mltrng" 
+ERROR:  update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_mltrng2mltrng_fk_2"
ontable "temporal_partitioned_fk_mltrng2mltrng" 
 DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table
"temporal_partitioned_fk_mltrng2mltrng".
 --
 -- partitioned FK referenced updates CASCADE
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index f478d17745a..25b09a39a76 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2017,7 +2017,7 @@ CREATE TABLE pt1 PARTITION OF pt1_2 FOR VALUES IN (1);
 CREATE TABLE pt2 PARTITION OF pt1_2 FOR VALUES IN (2);
 CREATE TABLE ref(f1 int, f2 int, f3 int);
 ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;
-ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey1
+ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey_1
   DEFERRABLE INITIALLY DEFERRED;    -- fails
 ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey
   DEFERRABLE INITIALLY DEFERRED;
@@ -2334,7 +2334,7 @@ DELETE FROM fk_p; -- should fail

 -- these should all fail
 ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
-ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1;
+ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey_1;
 ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;

 SET client_min_messages TO warning;

On Wed, 23 Apr 2025 at 01:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  So I'm reposting the patch to see
> if anyone feels this is too much churn.

WFM

-- 
Best regards,
Kirill Reshke

On 2025-Apr-22, Tom Lane wrote:

> After poking at that, it's easy to get ChooseConstraintName to do
> something just slightly different from what I said above: the rule is
> now "add an underscore and some digits to the name used for the parent
> constraint".  I like this even better than the previous idea, because
> I think it makes it more obvious that the name is derived from the
> parent constraint.  However, this changes the chosen name in more
> cases than my previous hack did.  So I'm reposting the patch to see
> if anyone feels this is too much churn.  I think it's okay as a
> v18-only patch, though I wouldn't propose it for back-patch.

I think the new constraint names are better, so +1 for this version of
the patch for 18.  I agree that we don't necessarily want to backpatch
this to stable branches though.

I wonder if it would make pg_upgrade users' lives easier if we had
pg_upgrade --check notify them about possible collisions on these
constraints (for the older branches).  I don't have good ideas on how to
implement that though other than a trial dump/restore, which is perhaps
unreasonable.

(My position on pg_upgrade is that if pg_upgrade --check passes, then
you shouldn't need any additional tests for assurance that running
pg_upgrade for real is going to work.  So I would be happier if we could
detect this problem.)

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)

Alvaro Herrera <alvherre@kurilemu.de> writes:
> I think the new constraint names are better, so +1 for this version of
> the patch for 18.  I agree that we don't necessarily want to backpatch
> this to stable branches though.

Cool, I'll push that patch in a bit then.

> I wonder if it would make pg_upgrade users' lives easier if we had
> pg_upgrade --check notify them about possible collisions on these
> constraints (for the older branches).  I don't have good ideas on how to
> implement that though other than a trial dump/restore, which is perhaps
> unreasonable.

Yeah, I'm not seeing a simple way to do that either.  I'm hesitant to
put a ton of work into it, because in the end the situation seems like
self-inflicted damage.  It's not quite true that you need duplicate
foreign key constraints to hit this, but it's close: the default
table-and-column-name-based name for one constraint has to match the
actual name of a second constraint.  If the second constraint isn't
a duplicate of the first then it has a very misleadingly chosen name.
Either way it's pretty poor DBA practice.

Between that thought and the fact that the problem has gone unnoticed
since v12, I'm content to make this change in the default names and
stop here.

            regards, tom lane