Feedback-ID: ia2694551:Fastmail
MIME-Version: 1.0
Date: Thu, 02 Jan 2025 19:40:25 +0100
From: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>
To: "Amul Sul" <sulamul@gmail.com>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Message-Id: <c2dcf304-45d6-47c1-8cd3-6ed15aca4da8@app.fastmail.com>
In-Reply-To: 
 <CAAJ_b96Bp=-ZwihPPtuaNX=SrZ0U6ZsXD3+fgARO0JuKa8v2jQ@mail.gmail.com>
References: 
 <CAAJ_b96Bp=-ZwihPPtuaNX=SrZ0U6ZsXD3+fgARO0JuKa8v2jQ@mail.gmail.com>
Subject: Re: Allow NOT VALID foreign key constraints on partitioned tables.
Content-Type: multipart/alternative;
 boundary=f99149d167d64e39b0e57ad003c00e69
Archived-At: <https://www.postgresql.org/message-id/c2dcf304-45d6-47c1-8cd3-6ed15aca4da8%40app.fastmail.com>
Precedence: bulk

--f99149d167d64e39b0e57ad003c00e69
Content-Type: text/plain
Content-Transfer-Encoding: 7bit


On Thu, Jan 2, 2025, at 5:49 PM, Amul Sul wrote:
> When adding a new FK constraint or attaching a partitioned table, where
> matching FK constraints are merged, we allow the parent constraint to be NOT
> VALID while the child constraint remains VALID, which is harmless. However, the
> reverse scenario -- where the parent constraint is VALID and the child is NOT
> VALID -- is incorrect. To address this, when merging a NOT VALID FK constraint
> from the child with a VALID parent constraint, it implicitly validates the
> child constraint against its existing data and marks it as VALID. This behavior
> aligns with adding a new FK constraint directly to the child table, which would
> also validate the existing data.

Hmm, I'm not sure about this, which may cause surprising delays. Maybe it would be better that the operation fails with an error, so that the user can do VALIDATE CONSTRAINT explicitly and retry the ATTACH once all the partitions have been so processed.
--f99149d167d64e39b0e57ad003c00e69
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div><br></div><div>=
<br></div><div>On Thu, Jan 2, 2025, at 5:49 PM, Amul Sul wrote:</div><bl=
ockquote type=3D"cite" id=3D"qt" style=3D""><div>When adding a new FK co=
nstraint or attaching a partitioned table, where<br></div><div>matching =
FK constraints are merged, we allow the parent constraint to be NOT<br><=
/div><div>VALID while the child constraint remains VALID, which is harml=
ess. However, the<br></div><div>reverse scenario -- where the parent con=
straint is VALID and the child is NOT<br></div><div>VALID -- is incorrec=
t. To address this, when merging a NOT VALID FK constraint<br></div><div=
>from the child with a VALID parent constraint, it implicitly validates =
the<br></div><div>child constraint against its existing data and marks i=
t as VALID. This behavior<br></div><div>aligns with adding a new FK cons=
traint directly to the child table, which would<br></div><div>also valid=
ate the existing data.<br></div></blockquote><div><br></div><div>Hmm, I'=
m not sure about this, which may cause surprising delays. Maybe it would=
 be better that the operation fails with an error, so that the user can =
do VALIDATE CONSTRAINT explicitly and retry the ATTACH once all the part=
itions have been so processed.</div></body></html>
--f99149d167d64e39b0e57ad003c00e69--