Re: Foreign key validation failure in 18beta1

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. 

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);
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
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

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?