Re: Foreign key validation failure in 18beta1

On Fri, May 30, 2025 at 1:37 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Thu, May 29, 2025 at 8:58 PM Amul Sul <sulamul(at)gmail(dot)com> wrote:
> > >
> > > I just realized we have the same problem with ALTER FOREIGN KEY ENFORCED.
> > > for example:
> >
> > Yeah, I think adding a "currcon->confrelid == pkrelid" check before
> > enqueueing validation in ATExecAlterConstrEnforceability() would
> > address the issue, though I still need to test it thoroughly.
> >
>
> adding a "currcon->confrelid == pkrelid" will fix the problem.
> I have used the attached scripts to test foreign key functionality:
> ALTER TABLE VALIDATE CONSTRAINT and
> ALTER TABLE ALTER CONSTRAINT ENFORCED.
>

Thanks for the checking.

>
> v3-0001-foreign_key_validation-WIP also works fine.
> but, I have one minor issue with the comment.
>
> + /*
> + * When the referenced table is partitioned, the referencing table
> + * may have multiple foreign key constraints -- one for each child
> + * table. These individual constraints do not require separate
> + * validation, as validating the constraint on the root partitioned
> + * table is sufficient.
>
> The last sentence doesn't seem to explain why.
> The following is what I came up with (my understanding):
> ""
> If the primary key (PK) is partitioned, we *can't* queue validation (add a
> NewConstraint on PK partition and validate it in phase3) for its partitions.
> Validating foreign key constraints between primary key (PK) partitions and
> foreign keys (FK) can fail. This is because some PK partitions may legitimately
> lack corresponding FK values, which is acceptable but causes validation errors.
> ""

I would prefer to use 'referenced' and 'referencing' tables instead of
'PK' and 'FK' tables, to stay consistent with the existing style. I
tried to improve the comment in the attached version with the
appropriate reason.

Kindly take a look at the attached version. I've also added the tests.
Thanks for your script -- all tests are passing with this patch.

Regards,
Amul