Re: Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade - Mailing list pgsql-hackers

On Wed, 24 Dec 2025 at 12:45, Jeevan Chalke
<jeevan.chalke@enterprisedb.com> 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.

Kind regards,

Matthias van de Meent