Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vbIyG-0040Ew-2V for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Jan 2026 13:44:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vbIyE-008Pt1-14 for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Jan 2026 13:44:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vbIyD-008Pst-2o for pgsql-hackers@lists.postgresql.org; Thu, 01 Jan 2026 13:44:06 +0000 Received: from mail-ot1-x32c.google.com ([2607:f8b0:4864:20::32c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vbIyB-003exV-0E for pgsql-hackers@postgresql.org; Thu, 01 Jan 2026 13:44:04 +0000 Received: by mail-ot1-x32c.google.com with SMTP id 46e09a7af769-7c750b10e14so4668041a34.2 for ; Thu, 01 Jan 2026 05:44:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1767275041; x=1767879841; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ikvq69GD40VBGRlfKEGUyPyN6PUMC+K9YezSbOhbeHw=; b=DqkU0wNY8NMTTGjuWUv1vNIokQFGQTlhdSE7Y/Q07wX7Gu7lOUot0b45CAZUNBAHA2 r1If2VwtrAr0zjAlqiQTo/FKXMrm0PDgkNL9uIxTKevLcqy2uqy0MZd95ZZmzVPtS/xo KyBAHESizWdOxwPkUJ6zTEalVkU7CI9hkFDu6OF1ryU/xXwk6iqtB+GiXblivtiYf/lF 6hilprU8hAsz5Knr6kJdxom9KABvU2OmQU6HNBrqxyY4/D+rEcCeXdudUMZwgQnCjIYt 1xsO/4HcKrQFoeTZ9qQ15Hy7HbKpm9b54cxkuWf39/LZ8GnS35xXZaPcUZFH/ekcwNZr I6LA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1767275041; x=1767879841; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=ikvq69GD40VBGRlfKEGUyPyN6PUMC+K9YezSbOhbeHw=; b=wQjlzEAbESNDEktOb65yBxpNcRwh86XOW1XJwKJNkt4INEYfYzFNli47X8OkQcEjNG otOiIpFBTweRXW/sIj9A1zilvoejKA5Xe+gQvzfdlWu3Hho6uuHWbNqFN2xUjwuvK2Ty 5or2CLf4G7/5GR0b3MNiFbos1MWoOz+lQXUt6YLgNzyYXL+lP9qGwh5bWTwQWWHBjqwG zLJRQqoXmmsFkV4wmTnQ/lpmj4MsTO4TwZbZ//2NXf2uaBsu88Ot7sP8HKgoD7TQxMcg Lotx+enze7iCQAfi28HjJQmnNAqq6bx8Y6ZMIGq5obfT7JE9FfoHbVvq98kmnp+/AlXe J29A== X-Gm-Message-State: AOJu0YzYZqarGr3/Y9gGwJqZtY9eVyaI3/LBZ0mfXtZeRh0iKFX28SRy FP8BlJ0eSfKMOccfAhDsdqczB8gp8Ij+y+1WSAtAGGck+6SOsRrU8Hdd9/MQaMHoueiaVk4sGrC xTiKQ1TgEmENldQLyK3L5s5gdqjmc5jN0y9GE3E31 X-Gm-Gg: AY/fxX7rRLSac50RKeF0CnGNZxU/pajk/1MK0tIERgdysNN5DBdK4EaA4SEGDZSwLcc 47cqxtcW015ChpnMENkGhbS+Ohu+Jjh7PhuM2W8q/Lsm0R/PbOGCEiDuuLqS8s1/hlO+2a2JVp2 vaJvC8F1+NaofDmeyB/dzTkCcodQeE61hq9XyfXed0uLq+tpYopaWtDtyFLaG1bpuyF+l3z+Ux+ RRA1OKG/1fBKRaUUXRHuDMhremBE2iyXPhVDjTbqXv8X3P7KC8adBWKwXSYipYMdBe1l/zr X-Google-Smtp-Source: AGHT+IEs4nQ52QSpjfQ2T5N7LfivJH/FzkUX2p5A+zMLC9j1GcnWOKDEciwMCDk7mr9sNBXRuS4fK5/9Bu9d+RrrE4Y= X-Received: by 2002:a05:6830:4123:b0:7b8:f2a2:46b8 with SMTP id 46e09a7af769-7cc66a6efa8mr21761848a34.17.1767275041239; Thu, 01 Jan 2026 05:44:01 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Jeevan Chalke Date: Thu, 1 Jan 2026 19:13:25 +0530 X-Gm-Features: AQt7F2p5uW4OUIvLMZmxJL9cwiK8PgbQoPS6y4Bd4hcKwGGlsZEKxNpBHbixoSg Message-ID: Subject: Re: Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade To: Matthias van de Meent Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="0000000000000dc58a064753c9d6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000dc58a064753c9d6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Dec 25, 2025 at 2:22=E2=80=AFAM Matthias van de Meent < boekewurm+postgres@gmail.com> wrote: > On Wed, 24 Dec 2025 at 12:45, Jeevan Chalke > wrote: > > > > Hello Hackers, > > > > We have identified a dependency issue=E2=80=94most notably observed wit= h the > PostGIS extension=E2=80=94where a table's column definition relies on dat= a existing > in another table's catalog at restore time. Because pg_dump typically > separates schema and data into distinct sections, these implicit data-lev= el > 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 canno= t > 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, --=20 *Jeevan Chalke* *Principal Engineer, Engineering Manager* *Product Development* enterprisedb.com --0000000000000dc58a064753c9d6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, Dec 25,= 2025 at 2:22=E2=80=AFAM Matthias van de Meent <boekewurm+postgres@gmail.com> wrote:
On Wed, 24 Dec 2025 a= t 12:45, Jeevan Chalke
<jee= van.chalke@enterprisedb.com> wrote:
>
> Hello Hackers,
>
> We have identified a dependency issue=E2=80=94most notably observed wi= th the PostGIS extension=E2=80=94where a table's column definition reli= es 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 duri= ng pg_upgrade or pg_restore.
>
> Jakub Wartak previously reported a detailed example of this issue here= : https://www.postgresql.org/message-id/CAKZiRmwWyh-yGM8Hrvuuo04JiYFy= 8S4TLM-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. Cur= rent 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 agr= ee with your assessment. Currently, Postgres lacks a native mechanism for t= racking dependencies between a table and the specific rows of another table= . While certain extensions like PostGIS introduce these patterns, they rema= in 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 i= s a significantly less invasive approach. Notably, this patch triggers an i= mmediate dump of the referenced table data=C2=A0-- an unconventional behavi= or that is better handled in the client binaries than in the backend. Conse= quently, this approach would require new options for these binaries to expl= icitly inject those dependency details.
=C2=A0

Kind regards,

Matthias van de Meent


Regards= ,

--
=
Jeevan ChalkePrincipal Engineer, Engineering Manager=
Product Development

enterprisedb.com
--0000000000000dc58a064753c9d6--