Re: Retail DDL

Hi,

On Jul 25, 2025 at 21:35 +0800, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, wrote:
> =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre(at)kurilemu(dot)de> writes:
> > Reproducing a table might need multiple commands. Do you intend to
> > return a single string containing multiple semicolon-separated commands,
> > or are you thinking in a RETURNS SETOF where each row contains a single
> > command?
>
> In the same vein: would we expect this command to also build the
> table's indexes? What about foreign key constraints, which might
> well reference tables that don't exist yet?
>
> Once you start crawling down this rabbit-hole, you soon realize
> why pg_dump is as complicated as it is.

First of all, +1 to this suggestion.
I've long believed there should be a standard way to get a table's DDL (like MySQL and Oracle have), especially when our DBAs encounter issues in customer
environments or when we need to cross-validate problems across different cluster versions.
This would make problem reproduction much more convenient. Currently, we're using pg_dump as our workaround.

Regarding the complexity you mentioned - absolutely, it's a real challenge.
MySQL's approach is to include all of a table's indexes in the DDL output. But this becomes problematic when dealing with foreign key dependencies between tables.

I think we could start with implementing basic table DDL and index generation first, as these are the most commonly needed features in practice.
For other objects related to the table, we can clearly document them.

Additionally, I have another suggestion - could we have a quick backslash command to display DDL? Something like \d+ t1, or perhaps \dddl? Looking at the code,
it seems there aren't many available command slots remaining.

--
Zhang Mingli
HashData