Re: Foreign key validation failure in 18beta1

Tender Wang <tndrwang@gmail.com> 于2025年5月28日周三 20:38写道：

>
>
> Alvaro Herrera <alvherre@alvh.no-ip.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