Horton

Re: Self referential foreign keys in partitioned table not working as expected

Álvaro Herrera <alvherre@kurilemu.de>

View thread
From: Álvaro Herrera <alvherre@kurilemu.de>
To: Christoph Berg <myon@debian.org>
Cc: Luca Vallisa <luca.vallisa@gmail.com>, pgsql-bugs@lists.postgresql.org
Date: 2025-07-23T09:27:04Z
Lists: pgsql-bugs, 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. Handle self-referencing FKs correctly in partitioned tables

  2. Restructure foreign key handling code for ATTACH/DETACH

  3. Fix self-referencing foreign keys with partitioned tables

On 2025-Apr-01, Christoph Berg wrote:

> Re: Luca Vallisa
> > The provided version throws an error.
> 
> Ok, I can confirm this.

> On a partitioned table, it does not throw the error:
> 
> create table test (
>     id_1 int4 not null,
>     id_2 int4 not null,
>     parent_id_2 int4 null,
>     primary key (id_1, id_2),
>     foreign key (id_1, parent_id_2) references test (id_1, id_2)
> ) partition by list (id_1);
> create table test_1 partition of test for values in (1);
> insert into test values (1, 1, null), (1, 2, 1);
> delete from test where (id_1, id_2) = (1, 1);

FWIW I didn't give closure on this thread, but AFAICT this is the same
bug that was reported in
https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org
and
https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com

That was fixed a couple of months ago.  The above script throws an error
as it should.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/