Re: pg_get_*_ddl() needs a redesign

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> That said, if this is coming out of v19 anyway, then IMHO it is fair
> game to reopen the question of whether we want it in v20. But if we're
> going to do that, we should hash that out now rather than in six
> months from now or twelve months from now. Personally, I have mixed
> feelings about this feature. On the one hand, I am sure it will get
> some use, and I have wanted it myself. On the other hand, it will get
> far less use, and far less testing, than the equivalent code in
> pg_dump, so it's more likely to have bugs now and in the future, and
> the fact that there's no reasonable way (at least, not that I can see)
> to handle version skew within a facility like this is a major
> disadvantage vs. pg_dump.

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.

The difficulty is how do we get to #2 from where we are. Part of that
could be handled by saying "well, pg_dump calls the server functions
when source server version >= N, and otherwise does it locally",
and expecting that we could eventually retire the "locally" code.
Since the "locally" code would only need to work with frozen server
versions, that doesn't seem too unmaintainable. 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.

regards, tom lane