On Fri, May 30, 2025 at 1:37 PM jian he <jian.universal...@gmail.com> wrote: > > On Thu, May 29, 2025 at 8:58 PM Amul Sul <sula...@gmail.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
v4-0001-Skip-adding-action-based-foreign-key-constraints-.patch
Description: Binary data
