Re: pg_get_*_ddl() needs a redesign

On 2026-09-11 Fr 12:55 AM, Tom Lane wrote:
> The hard parts as I see it are
>
> (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.
>
> (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.
>
> I do not feel that these problems are necessarily insoluble, but
> they do require some hard work to define the server-side functionality
> properly.
>
>

I think you've put your finger on some of the main issues. The main use
case in my mind from the get go was not "How do we make this work for
pg_dump?" but "I want to create an object that's like this thing but a
bit different. Give me the DDL for this thing so I can tinker with it."
That's the use case that people just about always give me when I've
talked to them about this.

Maybe point A is not insoluble, but I've thought all along that it was a
pretty significant barrier, and I don't have a good solution for it.
Making this feature contingent on solving that problem would sacrifice
the originally intended use case, which would be very sad.

Your point B is also well taken. pg_dump formats and arranges the SQL to
suit its purposes, but that should not be taken as the canonical
arrangement for other uses. Perhaps these functions should have a "dump"
flavor that produces output like pg_dump/all.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com