MIME-Version: 1.0
References: 
 <CAM2+6=UstF2jQc8tZMbb3A-ag84-UhKs2OnYQn7pwwarY9i2nA@mail.gmail.com>
 <CAEze2Wip=0RjcXMYqTHPKf337MSvQOVoGYBvF8b+LULgjMziQA@mail.gmail.com>
 <CAM2+6=XrUN5K=OT4X=tOH6VzXHydJ-k4krKJE1+JE+JtPvjuwA@mail.gmail.com>
 <5835b04f-c53a-4f45-84a3-ea681f3fb338@eisentraut.org>
In-Reply-To: <5835b04f-c53a-4f45-84a3-ea681f3fb338@eisentraut.org>
From: Paul Ramsey <pramsey@cleverelephant.ca>
Date: Mon, 25 May 2026 14:06:41 -0700
Message-ID: 
 <CACowWR2sycYmpaxp1uj2c5xaLjccByzqim0cDuR_aXEULnfVyA@mail.gmail.com>
Subject: Re: Add --extra-dependencies and immediate data dumping for
 pg_dump/pg_upgrade
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Jeevan Chalke <jeevan.chalke@enterprisedb.com>,
	Matthias van de Meent <boekewurm+postgres@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Regina Obe <lr@pcorp.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACowWR2sycYmpaxp1uj2c5xaLjccByzqim0cDuR_aXEULnfVyA%40mail.gmail.com>
Precedence: bulk

On Fri, Mar 13, 2026 at 6:23=E2=80=AFAM Peter Eisentraut <peter@eisentraut.=
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