Re: Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade

On Fri, Mar 13, 2026 at 6:23 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 01.01.26 14:43, Jeevan Chalke wrote:
> > 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.
>
> How about this: postgis should define its table spatial_ref_sys as
> user_catalog_table, and we change pg_dump to dump the contents of user
> catalog tables before other DDL.
>
> There is still some work to do here, but at least this sounds like a
> more principled approach.

I'm not 100% clear on why extensions are not restored first, in their
entirety (functions, tables, data), before moving on to user table
definition and user data. I have nothing against using
user_catalog_table except that I am unsure of whether the other
effects of that definition actually are good or not. In any event,
spatial_ref_sys and its contents are already important and flagged as
special, as a consequence of being a part of an extension. We already
know they need special handling, even without flagging as
user_catalog_table.

P