FK Constraint sort order with pg_dump

[Christian Barthel]

Hello,

The sorting order of FK constraints with the same name is based on the
OID (because it lands in the “Usually shouldn’t get here” OID comparison
block at [1]).  Wouldn’t it be better if the order of those constraints
were based on the table name?

Details:

The above schema is identical except in the order how the constraints
were added (the constraint name is the same on those two tables):

--8<---------------cut here---------------start------------->8---
  -- --- Schema Version 1:
  CREATE TABLE a (id int unique);

  CREATE TABLE b (id int);
  ALTER TABLE b ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);
  CREATE TABLE c (id int);
  ALTER TABLE c ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);

  -- --- Schema Version 2:

  CREATE TABLE a (id int unique);

  CREATE TABLE c (id int);
  ALTER TABLE c ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);
  CREATE TABLE b (id int);
  ALTER TABLE b ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES a(id);
--8<---------------cut here---------------end--------------->8---

Doing a pg_dump on Version 1 and Version 2 leads to two different dumps
despite being the same schema: (*)

--8<---------------cut here---------------start------------->8---
--- version1    2022-07-21 19:16:31.369010843 +0200
+++ version2    2022-07-21 19:16:26.688976178 +0200
@@ -86,18 +86,18 @@
 
 
 --
--- Name: b x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
+-- Name: c x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
 --
 
-ALTER TABLE ONLY public.b
+ALTER TABLE ONLY public.c
     ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES public.a(id);
 
 
 --
--- Name: c x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
+-- Name: b x_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bch
 --
 
-ALTER TABLE ONLY public.c
+ALTER TABLE ONLY public.b
     ADD CONSTRAINT x_fkey FOREIGN KEY (id) REFERENCES public.a(id);
--8<---------------cut here---------------end--------------->8---

Attached is a patch file that adds a string comparison function call to
sort FK constraints (based on the table if it exists).  Any thoughts on
that?

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/pg_dump/pg_dump_sort.c;h=80641cd79a2e6ce0a10bd55218b10d22ac369ed5;hb=7c850320d8cfa5503ecec61c2559661b924f7595#l212

(*) Tested on 14.4
-- 
Christian Barthel


modified   src/bin/pg_dump/pg_dump_sort.c
@@ -300,6 +300,23 @@ DOTypeNameCompare(const void *p1, const void *p2)
                if (cmpval != 0)
                        return cmpval;
        }
+	else if (obj1->objType == DO_FK_CONSTRAINT)
+	{
+		ConstraintInfo *cobj1 = *(ConstraintInfo *const *)p1;
+		ConstraintInfo *cobj2 = *(ConstraintInfo *const *)p2;
+
+		if (cobj1->contable != NULL && cobj2->contable != NULL) {
+			/* Sort two FK constraints with the same name by their
+			 * corresponding relationname:
+			 */
+			cmpval = strcmp(
+				cobj1->contable->dobj.name,
+				cobj2->contable->dobj.name);
+			if (cmpval != 0)
+				return cmpval;
+		}
+	}

        /* Usually shouldn't get here, but if we do, sort by OID */
        return oidcmp(obj1->catId.oid, obj2->catId.oid);