On Mon, Oct 19, 2026 at 12:04 PM Mario González <[email protected]> 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
