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
