Re: Test instability when pg_dump orders by OID

Hello Noah,

07.07.2025 22:26, Noah Misch wrote:
> 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