MIME-Version: 1.0
References: 
 <CAM2+6=UstF2jQc8tZMbb3A-ag84-UhKs2OnYQn7pwwarY9i2nA@mail.gmail.com>
In-Reply-To: 
 <CAM2+6=UstF2jQc8tZMbb3A-ag84-UhKs2OnYQn7pwwarY9i2nA@mail.gmail.com>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Wed, 24 Dec 2025 21:52:18 +0100
Message-ID: 
 <CAEze2Wip=0RjcXMYqTHPKf337MSvQOVoGYBvF8b+LULgjMziQA@mail.gmail.com>
Subject: Re: Add --extra-dependencies and immediate data dumping for
 pg_dump/pg_upgrade
To: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAEze2Wip%3D0RjcXMYqTHPKf337MSvQOVoGYBvF8b%2BLULgjMziQA%40mail.gmail.com>
Precedence: bulk

On Wed, 24 Dec 2025 at 12:45, Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:
>
> Hello Hackers,
>
> We have identified a dependency issue=E2=80=94most notably observed with =
the PostGIS extension=E2=80=94where a table's column definition relies on d=
ata existing in another table's catalog at restore time. Because pg_dump ty=
pically separates schema and data into distinct sections, these implicit da=
ta-level dependencies are not captured, leading to failures during pg_upgra=
de or pg_restore.
>
> Jakub Wartak previously reported a detailed example of this issue here: h=
ttps://www.postgresql.org/message-id/CAKZiRmwWyh-yGM8Hrvuuo04JiYFy8S4TLM-3M=
n-zi9Rfqc744Q%40mail.gmail.com

Ah, yes, that does sound like an issue.

> Following a discussion with Alvaro Herrera, we have developed a patch bas=
ed 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_dum=
p 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.


Kind regards,

Matthias van de Meent