Re: Foreign key validation failure in 18beta1

Tender Wang <tndrwang(at)gmail(dot)com> 于2025年5月28日周三 18:54写道：

>
>
> Antonin Houska <ah(at)cybertec(dot)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(at)alvh(dot)no-ip(dot)org>
> Date: Thu Jan 23 15:54:38 2025 +0100
>
> Allow NOT VALID foreign key constraints on partitioned tables
>
>
I dided the codes, in QueueFKConstraintValidation(), we add three
newconstraint for the
fk rel, because the pk rel is partition table.

During phase 3 of AlterTable, in ATRewriteTables(),
call validateForeignKeyConstraint() three times.
The first time the pk rel is pk, and it's ok.
The second time the pk rel is only pk_1, and the type(1) is not in pk_1, so
an error is reported.

In this case, the two children newconstraint should not be added to the
queue.
--
Thanks,
Tender Wang