Horton

Foreign key validation failure in 18beta1

Antonin Houska <ah@cybertec.at>

View thread
From: Antonin Houska <ah@cybertec.at>
To: pgsql-hackers@lists.postgresql.org
Date: 2025-05-28T07:51:15Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Avoid bogus scans of partitions when marking FKs enforced

  2. Avoid bogus scans of partitions when validating FKs to partitioned tables

  3. Allow NOT VALID foreign key constraints on partitioned tables

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