MIME-Version: 1.0
References: <CAAJ_b96Bp=-ZwihPPtuaNX=SrZ0U6ZsXD3+fgARO0JuKa8v2jQ@mail.gmail.com>
 <c2dcf304-45d6-47c1-8cd3-6ed15aca4da8@app.fastmail.com>
In-Reply-To: <c2dcf304-45d6-47c1-8cd3-6ed15aca4da8@app.fastmail.com>
From: Amul Sul <sulamul@gmail.com>
Date: Mon, 6 Jan 2025 09:53:46 +0530
Message-ID: <CAAJ_b95NeSuJwXo8qrhxsCFtXg6Oq0LAg5HhDSF2wubCWM5tGg@mail.gmail.com>
Subject: Re: Allow NOT VALID foreign key constraints on partitioned tables.
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAAJ_b95NeSuJwXo8qrhxsCFtXg6Oq0LAg5HhDSF2wubCWM5tGg%40mail.gmail.com>
Precedence: bulk

On Fri, Jan 3, 2025 at 12:11=E2=80=AFAM =C3=81lvaro Herrera <alvherre@alvh.=
no-ip.org> wrote:
>
>
>
> 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. Howeve=
r, 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 const=
raint
> from the child with a VALID parent constraint, it implicitly validates th=
e
> child constraint against its existing data and marks it as VALID. This be=
havior
> 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 c=
an do VALIDATE CONSTRAINT explicitly and retry the ATTACH once all the part=
itions have been so processed.

Error reporting would have made this straightforward, but the delay is
not a new, and the patch does not introduce any additional delay.
Setting the patch aside for a moment, consider the current behavior on
the master branch: if you have a partitioned table(see e.g. below)
where one of the child tables has a NOT VALID foreign key constraint,
adding a new VALID foreign key constraint to the partitioned table
will ignore the existing constraint on the child table. Instead, it
creates a new constraint, which ultimately triggers a scan of the
child table to validate the new constraint. E.g.

create table bar(i int primary key);
create table foo(i int) partition by range(i);
create table foo_p1 partition of foo for values from (0) to (10);
insert into foo_p1 values(1); -- value doesn't exists in bar
alter table foo_p1 add constraint fk_con foreign key(i) references bar
NOT VALID; -- ok

-- following triggers the validation and fails.
alter table foo add constraint fk_con foreign key(i) references bar;

The behavior with the patch remains the same, but instead of creating
a new foreign key constraint, it merges with the existing one and
validates it.

Regards,
Amul