Horton

Re: Foreign key validation failure in 18beta1

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Amul Sul <sulamul@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers@lists.postgresql.org, Antonin Houska <ah@cybertec.at>
Date: 2025-05-30T08:06:53Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Avoid bogus scans of partitions when marking FKs enforced

  2. Avoid bogus scans of partitions when validating FKs to partitioned tables

  3. Allow NOT VALID foreign key constraints on partitioned tables

Attachments

On Thu, May 29, 2025 at 8:58 PM Amul Sul <sulamul@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.


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.
""