pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedSelf referential foreign keys in partitioned table not working as expected
5+ messages / 3 participants
[nested] [flat]
* Self referential foreign keys in partitioned table not working as expected
@ 2025-04-01 09:06 Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:18 ` Re: Self referential foreign keys in partitioned table not working as expected Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 5+ messages in thread
From: Luca Vallisa @ 2025-04-01 09:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
As mentioned at
https://www.postgresql.org/message-id/18156-a44bc7096f0683e6%40postgresql.org
this is a regression introduced in version 15.X and still present in 17.4.
I'm running the postgres:17.4-alpine docker official image.
-------------------------------------------------------------------------------------
REPRO
-------------------------------------------------------------------------------------
drop table if exists test;
create table test (
id_1 int4 not null,
id_2 int4 not null,
parent_id_1 int4 null,
primary key (id_1, id_2),
foreign key (parent_id_1, id_2) references test(id_1, id_2)
) partition by list(id_1);
create table test_1 partition of test for values in (1);
create table test_9 partition of test for values in (9);
insert into test values (1, 1, null), (1, 2, 1);
-- doesn't trigger an error
-- delete from test where (id_1, id_2) = (1, 1);
-- doesn't trigger an error
-- update test set id_1 = 9 where (id_1, id_2) = (1, 1);
-------------------------------------------------------------------------------------
Both deletion and update runs smoothly.
Also, potential cascade delete and cascade update rules are ignored.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Self referential foreign keys in partitioned table not working as expected
2025-04-01 09:06 Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
@ 2025-04-01 10:18 ` Christoph Berg <myon@debian.org>
2025-04-01 10:24 ` Re: Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Christoph Berg @ 2025-04-01 10:18 UTC (permalink / raw)
To: Luca Vallisa <luca.vallisa@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Re: Luca Vallisa
> create table test (
> id_1 int4 not null,
> id_2 int4 not null,
> parent_id_1 int4 null,
> primary key (id_1, id_2),
> foreign key (parent_id_1, id_2) references test(id_1, id_2)
> ) partition by list(id_1);
>
> insert into test values (1, 1, null), (1, 2, 1);
Multi-column foreign keys where one column is NULL effectively disable
the FK, this is not specific to partitioned tables. They works as
designed, but best avoid them.
Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Self referential foreign keys in partitioned table not working as expected
2025-04-01 09:06 Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:18 ` Re: Self referential foreign keys in partitioned table not working as expected Christoph Berg <myon@debian.org>
@ 2025-04-01 10:24 ` Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:46 ` Re: Self referential foreign keys in partitioned table not working as expected Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 5+ messages in thread
From: Luca Vallisa @ 2025-04-01 10:24 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: pgsql-bugs@lists.postgresql.org
Thanks for the reply.
I've realized I messed up with the script.
Please refer to the following one.
---------------------------------------------------------------------------------------
drop table if exists test;
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); /** uncomment this line **/
--create table test_1 partition of test for values in (1); /** uncomment
this line **/
--create table test_9 partition of test for values in (9) /** uncomment
this line **/
;
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
--update test set id_2 = 9 where (id_1, id_2) = (1, 1);
select * from test;
drop table if exists test;
---------------------------------------------------------------------------------------
The provided version throws an error.
If you uncomment the 3 lines (working with partition) the error is not
thrown during the delete (or the update).
Luca
Il giorno mar 1 apr 2025 alle ore 12:18 Christoph Berg <myon@debian.org> ha
scritto:
> Re: Luca Vallisa
> > create table test (
> > id_1 int4 not null,
> > id_2 int4 not null,
> > parent_id_1 int4 null,
> > primary key (id_1, id_2),
> > foreign key (parent_id_1, id_2) references test(id_1, id_2)
> > ) partition by list(id_1);
> >
> > insert into test values (1, 1, null), (1, 2, 1);
>
> Multi-column foreign keys where one column is NULL effectively disable
> the FK, this is not specific to partitioned tables. They works as
> designed, but best avoid them.
>
> Christoph
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Self referential foreign keys in partitioned table not working as expected
2025-04-01 09:06 Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:18 ` Re: Self referential foreign keys in partitioned table not working as expected Christoph Berg <myon@debian.org>
2025-04-01 10:24 ` Re: Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
@ 2025-04-01 10:46 ` Christoph Berg <myon@debian.org>
2025-07-23 09:27 ` Re: Self referential foreign keys in partitioned table not working as expected Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 1 reply; 5+ messages in thread
From: Christoph Berg @ 2025-04-01 10:46 UTC (permalink / raw)
To: Luca Vallisa <luca.vallisa@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Re: Luca Vallisa
> The provided version throws an error.
Ok, I can confirm this.
This throws an error like it should:
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)
);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
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);
Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Self referential foreign keys in partitioned table not working as expected
2025-04-01 09:06 Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:18 ` Re: Self referential foreign keys in partitioned table not working as expected Christoph Berg <myon@debian.org>
2025-04-01 10:24 ` Re: Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:46 ` Re: Self referential foreign keys in partitioned table not working as expected Christoph Berg <myon@debian.org>
@ 2025-07-23 09:27 ` Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 0 replies; 5+ messages in thread
From: Álvaro Herrera @ 2025-07-23 09:27 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Luca Vallisa <luca.vallisa@gmail.com>; pgsql-bugs@lists.postgresql.org
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-07-23 09:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-01 09:06 Self referential foreign keys in partitioned table not working as expected Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:18 ` Christoph Berg <myon@debian.org>
2025-04-01 10:24 ` Luca Vallisa <luca.vallisa@gmail.com>
2025-04-01 10:46 ` Christoph Berg <myon@debian.org>
2025-07-23 09:27 ` Álvaro Herrera <alvherre@kurilemu.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox