Re: Test instability when pg_dump orders by OID - Mailing list pgsql-hackers

A 002_pg_upgrade.pl run got swapped order of tags "notnull_tbl1_upg nn" and
"notnull_parent_upg nn" for the schema diff test that commit
172259afb563d35001410dc6daad78b250924038 added in v18:

@@ -436873,14 +436873,14 @@ ALTER TABLE public.insert_tbl     ADD CONSTRAINT ne_insert_tbl_con CHECK (((x + z) = 1)) NOT ENFORCED; --
--- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
+-- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm --
-ALTER TABLE public.notnull_tbl1_upg
+ALTER TABLE public.notnull_parent_upg     ADD CONSTRAINT nn NOT NULL a NOT VALID; --
--- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
+-- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm --
-ALTER TABLE public.notnull_parent_upg
+ALTER TABLE public.notnull_tbl1_upg

It's rather funny that a few days before the fix is going to be pushed,
hippopotamus proved the need for it [1] (I saw no such failures on the
buildfarm before):
...
[17:09:56.372](2.577s) not ok 8 - dump outputs from original and restored regression databases match
[17:09:56.372](0.000s)
[17:09:56.372](0.000s) #   Failed test 'dump outputs from original and restored regression databases match'
[17:09:56.372](0.000s) #   at /home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/../../../src/test/perl/PostgreSQL/Test/Utils.pm line 800.
[17:09:56.373](0.000s) #          got: '1'
#     expected: '0'
=== diff of /home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/src_dump.sql_adjusted and /home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/dest_dump.sql_adjusted
=== stdout ===
--- /home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/src_dump.sql_adjusted    2025-07-28 17:09:55.040029896 +0200
+++ /home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/dest_dump.sql_adjusted    2025-07-28 17:09:56.208057237 +0200
@@ -436960,14 +436960,14 @@
 ALTER TABLE public.insert_tbl
     ADD CONSTRAINT ne_insert_tbl_con CHECK (((x + z) = 1)) NOT ENFORCED;
 --
--- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
+-- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
 --
-ALTER TABLE public.notnull_tbl1_upg
+ALTER TABLE public.notnull_parent_upg
     ADD CONSTRAINT nn NOT NULL a NOT VALID;
 --
--- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
+-- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
 --
-ALTER TABLE public.notnull_parent_upg
+ALTER TABLE public.notnull_tbl1_upg
     ADD CONSTRAINT nn NOT NULL a NOT VALID;
 --
 -- Name: notnul

Thank you for working on the fix!

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hippopotamus&dt=2025-07-28%2015%3A05%3A11

Best regards,
Alexander