FK Constraint sort order with pg_dump - Mailing list pgsql-general

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