Thread: Re: Allow NOT VALID foreign key constraints on partitioned tables.

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.

On Fri, Jan 3, 2025 at 12:11 AM Álvaro 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. 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
withan error, so that the user can do VALIDATE CONSTRAINT explicitly and retry the ATTACH once all the partitions have
beenso 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