Horton

Re: Foreign key validation failure in 18beta1

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Amul Sul <sulamul@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers@lists.postgresql.org, Antonin Houska <ah@cybertec.at>
Date: 2025-05-29T12:26:31Z
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

On Thu, May 29, 2025 at 8:12 PM Amul Sul <sulamul@gmail.com> wrote:
>
> > >> > [...]
> > The attached *draft* patch is based on your idea.
> >
> > The idea is that we only need to conditionally do
> > ``tab->constraints = lappend(tab->constraints, newcon);`` within
> > QueueFKConstraintValidation.
> > but the catalog update needs to be done recursively.
>
> I like this approach, but I don’t think the flag name "recursing" is
> appropriate, as the flag is meant to indicate whether we want to
> enqueue constraints for validation or not.
>

Later, I came up with "need_validate", but it seems "queueValidation"
is better.

I just realized we have the same problem with ALTER FOREIGN KEY ENFORCED.
for example:
begin;
drop table if exists fk;
drop table if exists pk;
create table pk(i int, b int, primary key(i ,b)) partition by range (i);
create table pk_1 partition of pk for values from (0) to (10)
partition by list(b);
CREATE TABLE pk_1_p1 PARTITION OF pk_1 FOR VALUES IN (0, 1, 2);
create table pk_2 partition of pk for values from (10) to (12);
insert into pk values (0, 1), (1,2);
create table fk(i int, b int);
insert into fk values (0, 1), (1,3);
-- alter table fk add foreign key(i) references pk;
alter table fk add constraint fk_i_fkey foreign key(i, b) references
pk not enforced;
commit;

alter table fk alter constraint fk_i_fkey enforced; --error
delete from fk where i = 1 and b = 3;
alter table fk alter constraint fk_i_fkey enforced; --ok