Re: Foreign key validation failure in 18beta1

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

>
>
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2025年5月28日周三 20:26写道：
>
>> On 2025-May-28, Tender Wang wrote:
>>
>> > 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.
>>
>> Yeah, I reached the same conclusion and this is the preliminary fix I
>> had written for it. I don't like that I had to duplicate a few lines of
>> code, but maybe it's not too bad. Also the comments need to be
>> clarified a bit more.
>>
>
> If the child table is still a partitioned table, the patch seems not work.
>

I found a case that proves what I said above.
create table pk(i int, j int, primary key(i,j)) partition by range (i);
create table pk_1 partition of pk for values from (0) to (1) partition by
list(j);
create table pk_2 partition of pk for values from (1) to (2) partition by
list(j);
create table pk_1_1 partition of pk_1 for values in (1);
create table pk_2_1 partition of pk_2 for values in (2);
create table fk(i int, j int);
alter table fk add foreign key(i, j) references pk not valid;
postgres=# select oid ,conname , contype,
convalidated,conrelid,conparentid,confrelid from pg_constraint where oid >=
16384;
oid | conname | contype | convalidated | conrelid | conparentid |
confrelid
-------+---------------+---------+--------------+----------+-------------+-----------
16422 | fk_i_j_fkey | f | f | 16419 |
0 | 16384
16425 | fk_i_j_fkey_1 | f | f | 16419 | 16422 |
16391
16428 | fk_i_j_fkey_2 | f | f | 16419 | 16425 |
16405
16431 | fk_i_j_fkey_3 | f | f | 16419 | 16422 |
16398
16434 | fk_i_j_fkey_4 | f | f | 16419 | 16431 |
16412

alter table fk validate constraint fk_i_j_fkey;
postgres=# select oid ,conname , contype,
convalidated,conrelid,conparentid,confrelid from pg_constraint where oid >=
16384;
oid | conname | contype | convalidated | conrelid | conparentid |
confrelid
-------+---------------+---------+--------------+----------+-------------+-----------
16428 | fk_i_j_fkey_2 | f | f | 16419 | 16425 |
16405
16434 | fk_i_j_fkey_4 | f | f | 16419 | 16431 |
16412
16425 | fk_i_j_fkey_1 | f | t | 16419 | 16422 |
16391
16431 | fk_i_j_fkey_3 | f | t | 16419 | 16422 |
16398
16422 | fk_i_j_fkey | f | t | 16419 |
0 | 16384

The fk_i_j_fkey_2 and fk_i_j_fkey_4 are still invalid with your patch.

> I figure out a quick fix as the attached. I add a bool argument into
> the QueueFKConstraintValidation().
> If it is true, it means we recursively call QueueFKConstraintValidation(),
> then we don't add the newconstraint to the queue.
>
> I'm not sure about this fix. Any thoughts?
>

--
Thanks,
Tender Wang