Re: pg_get_*_ddl() needs a redesign

On Mon, Oct 19, 2026 at 12:04 PM Mario González <gonzalemario(at)gmail(dot)com> wrote:
> a) Split pg_get_role_ddl() into smaller functions: one for the CREATE
> ROLE, one for the role's settings, one for its memberships. None of
> them tries to put statements in restore order; the caller decides when
> to run each part.
> (Meant to address Noah's ordering concern.)
>
> b) pg_dumpall calls these functions instead of building the same SQL
> itself, so there is only one copy of the code that turns catalog rows
> into SQL. For servers too old to have the functions, pg_dumpall keeps
> its current code, which no longer needs to change because those
> servers won't change either. I reckon this is close to what Tom
> sketched [2]. (Meant to address the 'two implementations' concern.)
>
> c) Tests compare the functions' output with pg_dumpall's for the same
> objects, as v6 begins to. They'd need to be broader than v6's,
> perhaps driven by pg_dump's own tests, to catch a newly added option.
> (Meant to address Robert's silent-drift and Andres's cross-check concerns.)

I think you're going to find it very difficult to make this work.
There are cases where pg_dump splits things up in different ways
depending on the situation. I'm not sure how many such cases remain
off-hand, but I wouldn't be surprised if it isn't enough to make (a) a
non-viable direction. But also, even if you did that, how would it
help? pg_dump builds DDL on the client side so that the DDL matches
the pg_dump version, not the server version. Tom seems to think that
quote_all_identifiers would solve most of that problem, but I don't
agree. I think if you go down the path you propose here, this is going
to get nowhere.

If I were working on this project, what I'd be thinking about is what
the test framework could look like. I don't think that needs to be
pg_dump or pg_dumpall; but I think it needs to be something. For
example, imagine that you take the regression database -- i.e. the
final state left behind by a run of the 'regress' suite -- which we
already use to test pg_upgrade. For each DDL object in the database,
you use pg_get...ddl(), then drop the object, then put the object back
by running the SQL, and then at the end you compare pg_dump output
before and after you did all that. Well, that's not going to be quite
good enough, because a lot of objects will probably have dependencies
and not be droppable, and some objects might have circular
dependencies which might be really complicated, but you could
experiment with creating a TAP test that can work out the dependency
ordering from pg_(sh)depend and drop objects in reverse dependency
order and then put them all back. Maybe it's not crazy complicated.

Another idea could be to actually run the main regression test suite
and have an event trigger that fires every time an object is created
or dropped and try to do DDL replication using this feature: you run
the get-ddl function and replay it in another database that has no
actual data. This kind of sucks because it's expensive and most of the
objects getting created and dropped are fairly boring, but it would
probably test more cases than the previous idea, so it might be worth
doing at least once just to flush bugs out of your implementation,
even if we don't ever commit the result. Another advantage of this is
that, if properly coded, it cannot fail to go kaboom if a new type of
DDL object is added and there's no matching get-ddl function.

(By the way, why are we talking about adding a separate get-ddl
function for every object type, instead of a single entrypoint that
takes an object type and a function OID as separate arguments? The
latter seems much more natural and more consistent with existing
practice.)

I'm not sure either of the above testing ideas are the right final
answer. Maybe there is no right final answer. But in this era of LLMs,
anybody can churn out an implementation of the functions themselves.
Someone could probably create an entire patch set from scratch in less
than a day, and somebody really good at LLM things could possibly do
the whole thing in a single prompt. So, just producing a bunch of code
isn't really adding a lot of value; anybody could do it. Where you add
value is in figuring out how to solve all the problems that your first
design idea creates. And that's where I would gently push back on your
email here a little bit: you're proposing a design which I'm fairly
sure is not going to actually solve those problems, but you seem to
think it will. So one of us is wrong. If it's me, you can demonstrate
that by making it work. If it's you, you could figure that out by
trying it, seeing what goes wrong, and then thinking about how to deal
with that. I'm not saying you shouldn't ask for advice here, but you
should solicit that advice at the point where you come to a real fork
in the road, and "will the design work" isn't that.

After writing this email, I asked Claude to read the thread using the
pg.ddx.io MCP connector and do a technical assessment of your last
proposal. Here's what it said:

>>>
On the technical merits, I'd still expect it to fail. Splitting
pg_get_role_ddl() into CREATE ROLE, settings and membership functions
is sound: each function describes one thing, and ordering moves to the
caller. That fixes the restore-ordering problem at the API level. The
cost is that the caller has to do the multi-pass grantor ordering
itself, which removes most of the convenience for anyone who isn't
pg_dumpall. The central idea, having pg_dumpall call the source
server's functions so there's only one implementation, doesn't hold
up. The client needs to control its output to deal with version skew:
quoting newly reserved keywords, syntax the target server no longer
accepts, binary-upgrade variants, and bugs in the generator.
Server-side output is frozen at whatever minor release the source
runs, so the client would need its own code for these cases anyway,
and you'd have two implementations again with a more complicated split
between them. The functions would also need dump-specific modes
(target version, binary-upgrade, quote-all, and so on), so they would
grow into a backend copy of pg_dump's option handling. Roles are the
easiest case because there are few objects, the syntax is simple, and
per-object calls don't hurt performance. That makes them a misleading
pilot. Tables, with constraints split out for data loading and
partitioning, plus per-object calls on large schemas, are where this
breaks. The proposed equivalence tests are worth having, but they only
catch drift for options someone has already exercised. A new DDL
option still passes silently unless a test happens to use it.
Something like a catalog-coverage check that fails when a column isn't
handled would be needed to make drift detection reliable. The version
that could work is narrower: standalone "show me this object"
functions that make no promise to replace pg_dump, backed by coverage
tests strong enough to catch new options automatically.
<<<

It took less than a minute to get that answer, and writing this email
has taken me a lot more than a minute, and while I venture to hope
that my email is clearer than Claude's output, it does pretty much
tell you why your idea isn't going to work. I think that for a project
like this, if the level of due diligence that you're going to do
before posting is less than "paste your proposal into an LLM and see
what it says," the project probably won't succeed. Lots of people (me
included) would like this feature to exist, so the fact that it
doesn't exist is evidence that coming up with something good is
difficult. Even before LLMs, writing the code was never the hard part.
The hard part has always been figuring out a design that can answer
the inevitable objections. If someone here already knew how to do
that, this would likely be done already. Until you've thought about
this problem than everyone on this thread and have better ideas than
we do, there's probably no point in proposing anything.

--
Robert Haas
Databricks