On Thu, Dec 25, 2025 at 2:22 AM Matthias van de Meent < [email protected]> wrote:
> On Wed, 24 Dec 2025 at 12:45, Jeevan Chalke > <[email protected]> wrote: > > > > Hello Hackers, > > > > We have identified a dependency issue—most notably observed with the > PostGIS extension—where a table's column definition relies on data existing > in another table's catalog at restore time. Because pg_dump typically > separates schema and data into distinct sections, these implicit data-level > dependencies are not captured, leading to failures during pg_upgrade or > pg_restore. > > > > Jakub Wartak previously reported a detailed example of this issue here: > https://www.postgresql.org/message-id/CAKZiRmwWyh-yGM8Hrvuuo04JiYFy8S4TLM-3Mn-zi9Rfqc744Q%40mail.gmail.com > > Ah, yes, that does sound like an issue. > > > Following a discussion with Alvaro Herrera, we have developed a patch > based on his suggestions. > > > > The Problem > > > > In certain extension-heavy schemas, an object's schema definition cannot > be created unless another table's data is already populated. Current > pg_dump logic handles schema-to-schema dependencies via pg_depend, but it > lacks a mechanism to: > > > > Enforce a specific order for dependencies not recorded in pg_depend. > > Interleave data loading with schema creation for specific tables. > > Is there something that prevents PostGIS from recording this kind of > dependency in pg_depend, and by doing so force the right order in > pg_dump? It seems to me that pg_depend's model is generic enough to > enable that kind of dependency; so is the issue that pg_dump doesn't > currently track and resolve that type of dependency in a satisfactory > manner? > > I'm personally not a big fan of new pg_dump and pg_upgrade options to > solve this, as they require a user input to register a dependency that > should've been stored in the catalog; it should've been handled > natively. So, if we could make it work using pg_depend instead of > expecting user input here, then that'd be very much appreciated. > > Thanks for the feedback, Matthias; I agree with your assessment. Currently, Postgres lacks a native mechanism for tracking dependencies between a table and the specific rows of another table. While certain extensions like PostGIS introduce these patterns, they remain non-standard edge cases. Implementing a fix in the core backend seems like overkill for this scenario. Since the failure is specific to the upgrade path, targeting pg_dump and pg_upgrade is a significantly less invasive approach. Notably, this patch triggers an immediate dump of the referenced table data -- an unconventional behavior that is better handled in the client binaries than in the backend. Consequently, this approach would require new options for these binaries to explicitly inject those dependency details. > > Kind regards, > > Matthias van de Meent > Regards, -- *Jeevan Chalke* *Principal Engineer, Engineering Manager* *Product Development* enterprisedb.com <https://www.enterprisedb.com>
