Horton

Re: Foreign key validation failure in 18beta1

amul sul <sulamul@gmail.com>

View thread
From: Amul Sul <sulamul@gmail.com>
To: jian he <jian.universality@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-30T10:31:59Z
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 Fri, May 30, 2025 at 1:37 PM jian he <jian.universality@gmail.com> wrote:
>
> 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.
>

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