Re: pg_get_*_ddl() needs a redesign

Hi,

On 2026-09-11 00:55:54 -0400, Tom Lane wrote:
> I'm a bit confused why you say that. To my mind, if we were going
> to proceed in this direction, version skew would ideally be solved
> like this:
>
> 1. Server-side DDL-reconstruction functions only need to work with
> their particular server version; necessarily so, since that's the
> only catalog data they'll ever see.
>
> 2. pg_dump just calls the source server's DDL-reconstruction functions
> and doesn't have to worry about version skew.

Isn't the problem with that that we occasionally need to adapt the syntax
between versions to some degree? I think it has become less common, but I'm a
bit sceptical that it's wise to decide we won't ever again need that...

Binary upgrade mode is probably where we hit this most often these days.

I wonder if the right thing here would be to separate the "metadata
collection" aspect from the "SQL generation" aspect. I.e. have a small library
that can generate DDL statements from its inputs, without any catalog
accesses. We'd still end up - annoyingly - with two versions of the metadata
collection (one from syscaches for the get_ddl* SQL functions, one via queries
for pg_dump), but at least the SQL generation aspect would be deduplicated.

I think such a separation could be useful for other things as well,
e.g. logical replication of DDLs.

> (A) pg_dump is optimized to gather data across the entire database
> at once. Converting it to call functions that produce info about
> a single object at a time would almost certainly be catastrophic
> for performance on large databases.

Hm. I don't think I really understand that:

From what I can see the main benefits of entire-database-at-once isn't catalog
lookup efficiency, it's the avoidance of per-object roundtrips and avoiding
osme serialization cost.

Sure, there's also some benefits of turning more things into sequential scans,
rather than index lookups (via sycaches), but there's also a lot of overhead
in using full blown joins for everything, and in doing redundant lookups for
data that's repeated in almost every query (e.g. pg_namespace, name of owner,
etc).

For comparable performance I think you'd obviously have to call the DDL
generating functions for all objects of a class in one go, but I don't think
that'd be hard.

> (B) pg_dump slices and dices the data in ways that don't necessarily
> make sense for standalone usage. For example it doesn't want to
> mention a primary key when creating a table, rather it wants to add
> that constraint (and build the index) after loading data.

Yep, that seems nontrivial.

Greetings,

Andres Freund