Re: Order of tables dumped by pg_dump

On Sat, 16 May 2026 at 13:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> > However, I didn't think to mention that the database is running 14.22
> while
> > the client pg_dump is 18.1.
>
> Hmm, that might be relevant. However, I tried your example
> both with current-HEAD server + current-HEAD pg_dump and
> with 14.23 server + current-HEAD pg_dump. For me, the output
> is stable across dump/reload except for the random \restrict keys.
>
> I know that our handling of NOT NULL constraints changed significantly
> in v18 and there have been multiple bug fixes in that area, so maybe
> you'd have better luck with 18.4? I didn't spot any smoking guns in
> a quick trawl of the commit log back to 18.1, but I might've been
> searching for the wrong keywords.
>

Thanks for this effort. I'll see if I can make any progress on identifying
what triggers my problem to occur.

> CREATE TABLE ijmorlan.test_child (
> > NOT NULL f
> > )
> > INHERITS (ijmorlan.test_parent);
>
> > The CREATE TABLE for test_child is not syntactically valid.
>
> Yeah it is. The standalone constraint clause is perfectly fine,
> the column declaration comes from the parent, and the primary
> key constraint gets added on later (after loading data). In
> any case, I think you'd have a hard time demonstrating a dump order
> inconsistency with only two tables. If there is a problem here,
> it'd likely require three or more somehow-related tables.

My apologies for the confusion, I now see that one of the syntax
definitions of table_constraint in 18 is "NOT NULL column_name". This form
doesn't exist in 14.

The 2 sample table definitions were just to show what I had misunderstood
to be a bug in pg_dump, not to illustrate anything about my table order
problem, which involves how 3 tables get ordered relative to the rest of
the tables.