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

From: Amul Sul <[email-masked]>
Date: Mon, 6 Jan 2025 09:53:46 +0530

On Fri, Jan 3, 2025 at 12:11 AM Álvaro Herrera <[email-masked]> 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 with an error, so that the user can do VALIDATE CONSTRAINT explicitly and retry the ATTACH once all the partitions 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

---

From: Amul Sul <[email-masked]>
Date: Wed, 15 Jan 2025 11:04:51 +0530

On Mon, Jan 6, 2025 at 9:53 AM Amul Sul <[email-masked]> wrote:

I made the minor changes to the attached version and rebased it
against the latest master(9a45a89c38f).

Regards,
Amul

---

From: Álvaro Herrera <[email-masked]>
Date: Tue, 21 Jan 2025 22:06:14 +0100

Suppose I have a hierarchy like this

      parent
        |
      child
       /\
      /  \
     /    grandchild2
    /
  grandchild1

and I have a validated constraint on grandchild1 and an invalid
constraint on child.  What happens if I add a constraint on parent?  In
my understanding, it should not attempt to revalidate the constraint on
grandchild1, because it's known valid; but I don't think I see code that
would skip validation there.  That is, QueueFKConstraintValidation does
its thing unconditionally (esp. recursing to children), which seems
wrong.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this."                               (Fotis)
              https://postgr.es/m/[email-masked]

---

From: Amul Sul <[email-masked]>
Date: Wed, 22 Jan 2025 11:42:15 +0530

> its thing unconditionally (esp. recursing to children), which seems

You’re correct; it’s fixed in the attached version, along with an
assert(!convalidated) in QueueFKConstraintValidation(), and I’ve
included tests to cover the change. Thanks for reviewing this patch.

Regards,
Amul