Horton

Re: Test instability when pg_dump orders by OID

Robert Haas <robertmhaas@gmail.com>

View thread
From: Robert Haas <robertmhaas@gmail.com>
To: Noah Misch <noah@leadboat.com>
Cc: pgsql-hackers@postgresql.org
Date: 2025-07-21T13:40:02Z
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

On Fri, Jul 18, 2025 at 3:17 PM Noah Misch <noah@leadboat.com> wrote:
> > This comment is useful, but if I were to be critical, it does a better
> > job saying what this field isn't than what it is.
>
> True.  I've changed it to this:

That looks great.

> -       /* To have a stable sort order, break ties for some object types */
> +       /*
> +        * To have a stable sort order, break ties for some object types.  Most
> +        * catalogs have a natural key, e.g. pg_proc_proname_args_nsp_index.
> +        * Where the above "namespace" and "name" comparisons don't cover all
> +        * natural key columns, compare the rest here.
> +        *
> +        * The natural key usually refers to other catalogs by surrogate keys.
> +        * Hence, this translates each of those references to the natural key of
> +        * the referenced catalog.  That may descend through multiple levels of
> +        * catalog references.  For example, to sort by pg_proc.proargtypes,
> +        * descend to each pg_type and then further to its pg_namespace, for an
> +        * overall sort by (nspname, typname).
> +        */

I really like this.

> +                * Sort by encoding, per pg_collation_name_enc_nsp_index.  Wherever
> +                * this changes dump order, restoring the dump fails anyway.  CREATE
> +                * COLLATION can't create a tie for this to break, because it imposes
> +                * restrictions to make (nspname, collname) uniquely identify a
> +                * collation within a given DatabaseEncoding.  While
> +                * pg_import_system_collations() can create a tie, pg_dump+restore
> +                * fails after pg_import_system_collations('my_schema') does so.
> +                * There's little to gain by ignoring one natural key column on the
> +                * basis of those limitations elsewhere, so respect the full natural
> +                * key like we do for other object types.

This is also good. I suggest s/Wherever/Technically, this is not
necessary, because wherever/ and s/There's/However, there's/.

-- 
Robert Haas
EDB: http://www.enterprisedb.com