Horton

Test instability when pg_dump orders by OID

Noah Misch <noah@leadboat.com>

View thread
From: Noah Misch <noah@leadboat.com>
To: pgsql-hackers@postgresql.org
Date: 2025-07-07T19:26:54Z
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 →

  1. Rewrite previous commit's test for TestUpgradeXversion compatibility.

  2. Sort DO_DEFAULT_ACL dump objects independent of OIDs.

  3. Remove, from stable branches, the new assertion of no pg_dump OID sort.

  4. Sort dump objects independent of OIDs, for the 7 holdout object types.

  5. pg_dump: provide a stable sort order for rules.

  6. pg_dump: include comments on not-null constraints on domains, too

  7. Verify roundtrip dump/restore of regression database

  8. In pg_dump, use simplehash.h to look up dumpable objects by OID.

  9. Tweak pg_dumpall to add GRANT CONNECT ON DATABASE ... TO PUBLIC when dumping

  10. Restructure operator classes to allow improved handling of cross-data-type

  11. First phase of project to use fixed OIDs for all system catalogs and

  12. Tweak pg_dump to say GRANT ALL when appropriate, rather than enumerating

Attachments

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


pg_dump uses pg_constraint.oid as one sort key, and "pg_restore -j" opens the
door for OID assignment order to vary.  The first attached patch demonstrates
this by simulation.  It yields that diff and some operator order diffs.

Let's get rid of pg_dump's need to sort by OID, apart from catalog corruption
scenarios.  Adding an assert found a total of seven affected object types.
See the second attached patch.  The drawback is storing five more fields in
pg_dump memory: oprleft, oprright, opcmethod, opfmethod, and collencoding.
That seems minor relative to existing pg_dump memory efficiency.  Since this
is a source of test flakes in v18, I'd like to back-patch to v18.  I'm not
sure why the buildfarm hasn't seen the above diff, but I expect the diff could
happen there.  This is another nice win for the new test from commit
172259afb563d35001410dc6daad78b250924038.  The order instability was always
bad for users, but the test brought it to the forefront.  One might argue for
back-patching $SUBJECT further, too.

Thanks,
nm