public inbox for [email protected]
help / color / mirror / Atom feedRe: Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade
2+ messages / 2 participants
[nested] [flat]
* Re: Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade
@ 2026-01-01 13:43 Jeevan Chalke <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Jeevan Chalke @ 2026-01-01 13:43 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: pgsql-hackers
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.gma...
>
> 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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade
@ 2026-05-25 21:06 Paul Ramsey <[email protected]>
parent: Jeevan Chalke <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Paul Ramsey @ 2026-05-25 21:06 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Jeevan Chalke <[email protected]>; Matthias van de Meent <[email protected]>; pgsql-hackers; Regina Obe <[email protected]>
On Fri, Mar 13, 2026 at 6:23 AM Peter Eisentraut <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-05-25 21:06 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-01 13:43 Re: Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade Jeevan Chalke <[email protected]>
2026-05-25 21:06 ` Paul Ramsey <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox