Horton

Re: Foreign key validation failure in 18beta1

Tender Wang <tndrwang@gmail.com>

View thread
From: Tender Wang <tndrwang@gmail.com>
To: Antonin Houska <ah@cybertec.at>
Cc: pgsql-hackers@lists.postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: 2025-05-28T10:54:30Z
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

Antonin Houska <ah@cybertec.at> 于2025年5月28日周三 15:51写道：

> I've come across an unexpected ERROR during validation of FK constraint in
> PG
> 18beta1. The same works in PG 17:
>
> drop table if exists fk;
> drop table if exists pk;
> create table pk(i int primary key) partition by range (i);
> create table pk_1 partition of pk for values from (0) to (1);
> create table pk_2 partition of pk for values from (1) to (2);
> insert into pk values (0), (1);
> create table fk(i int);
> insert into fk values (1);
>
> -- Works if the FK constraint is created as valid.
> --alter table fk add foreign key(i) references pk;
>
> -- Fails if the FK constraint is created as NOT VALID and validated
> -- afterwards.
> alter table fk add foreign key(i) references pk not valid;
> alter table fk validate constraint fk_i_fkey;
>

git bisect shows since below commit, the failure started.

commit b663b9436e7509b5e73c8c372539f067cd6e66c1
Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Date:   Thu Jan 23 15:54:38 2025 +0100

    Allow NOT VALID foreign key constraints on partitioned tables


-- 
Thanks,
Tender Wang