Re: Test instability when pg_dump orders by OID

Alexander Lakhin <exclusion@gmail.com>

From: Alexander Lakhin <exclusion@gmail.com>

To: Noah Misch <noah@leadboat.com>, pgsql-hackers@postgresql.org

Date: 2025-07-30T05:00:01Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Rewrite previous commit's test for TestUpgradeXversion compatibility.
- fb75e1ef7212 13.23 landed
- 22c6a44f01fd 14.20 landed
- 090c9c9608b3 15.15 landed
- c6dca7c3dd65 18.0 landed
- 49a09c6c51c0 17.7 landed
- 412d29fd2166 16.11 landed
- ad4412480d3f 19 (unreleased) landed
Sort DO_DEFAULT_ACL dump objects independent of OIDs.
- 4948bb9df421 14.20 landed
- 05341b2e9914 13.23 landed
- fbf967e996dd 15.15 landed
- e8d22095e5df 17.7 landed
- e68fa9a830f0 16.11 landed
- 7652142f4c14 18.0 landed
- b61a5c4bed7d 19 (unreleased) landed
Remove, from stable branches, the new assertion of no pg_dump OID sort.
- bc05590c7fcd 13.22 landed
- 7846f470964c 14.19 landed
- 70637d7ae0a2 15.14 landed
- 28e7252e450a 17.6 landed
- 216683296101 16.10 landed
- 0d2734eac345 18.0 landed
Sort dump objects independent of OIDs, for the 7 holdout object types.
- 04bc2c42f765 13.22 landed
- 7ee7c1cd389e 14.19 landed
- 22f126da6cce 15.14 landed
- 1ca1889ea6a3 17.6 landed
- 0ac1581c3f2d 16.10 landed
- c0ae03384fa3 18.0 landed
- 0decd5e89db9 19 (unreleased) landed
pg_dump: provide a stable sort order for rules.
- cc9a62c51a18 13.22 landed
- 25388fb2cb84 14.19 landed
- e99010cbd8e2 15.14 landed
- 9affed26349a 16.10 landed
- 5dd4957b28df 17.6 landed
pg_dump: include comments on not-null constraints on domains, too
- da71717f0a7c 19 (unreleased) cited
Verify roundtrip dump/restore of regression database
- 172259afb563 18.0 cited
In pg_dump, use simplehash.h to look up dumpable objects by OID.
- 92316a4582a5 15.0 cited
Tweak pg_dumpall to add GRANT CONNECT ON DATABASE ... TO PUBLIC when dumping
- 2ee56b6a3ab8 8.3.0 cited
Restructure operator classes to allow improved handling of cross-data-type
- a78fcfb51243 8.3.0 cited
First phase of project to use fixed OIDs for all system catalogs and
- 7c13781ee7a6 8.1.0 cited
Tweak pg_dump to say GRANT ALL when appropriate, rather than enumerating
- 1b68bcfad33e 7.2.1 cited

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