Test instability when pg_dump orders by OID

From: Noah Misch <noah(at)leadboat(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Test instability when pg_dump orders by OID
Date: 2025-07-07 19:26:54
Message-ID: 20250707192654.9e.nmisch@google.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: 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

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

Attachment Content-Type Size
dobjcmp10-demo-v0.patch.noci text/plain 1.4 KB
dobjcmp20-disambiguate-v1.patch text/plain 18.2 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Álvaro Herrera 2025-07-07 20:42:06 Re: Inconsistent LSN format in pg_waldump output
Previous Message Masahiko Sawada 2025-07-07 19:00:45 Re: Conflict detection for update_deleted in logical replication