Обсуждение: Foreign key validation failure in 18beta1

Поиск
Список
Период
Сортировка

Foreign key validation failure in 18beta1

От
Antonin Houska
Дата:
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;

-- 
Antonin Houska
Web: https://www.cybertec-postgresql.com

Re: Foreign key validation failure in 18beta1

От
Tender Wang
Дата:


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

Re: Foreign key validation failure in 18beta1

От
Tender Wang
Дата:


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


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 


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