Re: Command order bug in pg_dump - Mailing list pgsql-bugs

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;