pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
3+ messages / 3 participants
[nested] [flat]
* BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
@ 2023-10-13 14:26 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2023-10-13 14:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: fastcat@gmail.com
The following bug has been logged on the website:
Bug reference: 18156
Logged by: Matthew Gabeler-Lee
Email address: fastcat@gmail.com
PostgreSQL version: 16.0
Operating system: Linux
Description:
When a partitioned table has a self-referential foreign key, I'm finding
that the key is not enforced during deletes from the table.
A simple repro script:
create table x (
p int4 not null,
i int4 not null,
f int4 null,
primary key (p, i),
foreign key (p, f) references x (p, i)
)
partition by list (p);
create table x1 partition of x for values in (0);
insert into x values (0,1,null), (0,2,1);
delete from x where (p,i) = (0,1);
select * from x;
The final select shows the one row with the clearly violated foreign key
values:
p | i | f
---+---+---
0 | 2 | 1
(1 row)
I've reproduced this with 15.4 and 16.0 using the official docker images,
specifically `postgres/15-alpine` and `postgres/16-alpine`. I haven't tested
older versions as my application requires features new to 15.x. I've tried
with both list and hash partitioning, and it reproduces with both. I can
only reproduce it with self-referential foreign keys and only with
partitioned tables.
I discovered this with a slightly more complex situation involving an `on
delete set null` clause in the FK, but as seen above that is not required to
reproduce the issue.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
@ 2024-01-24 17:52 Matthew Gabeler-Lee <fastcat@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Matthew Gabeler-Lee @ 2024-01-24 17:52 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
I've since discovered that this is a regression in PG 15.x, with 14.x the
delete command correctly fails with an FK violation error.
Is there anything I can do to help investigating a root cause / fix for
this issue?
On Fri, Oct 13, 2023 at 10:27 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18156
> Logged by: Matthew Gabeler-Lee
> Email address: fastcat@gmail.com
> PostgreSQL version: 16.0
> Operating system: Linux
> Description:
>
> When a partitioned table has a self-referential foreign key, I'm finding
> that the key is not enforced during deletes from the table.
>
> A simple repro script:
>
> create table x (
> p int4 not null,
> i int4 not null,
> f int4 null,
> primary key (p, i),
> foreign key (p, f) references x (p, i)
> )
> partition by list (p);
> create table x1 partition of x for values in (0);
>
> insert into x values (0,1,null), (0,2,1);
> delete from x where (p,i) = (0,1);
> select * from x;
>
> The final select shows the one row with the clearly violated foreign key
> values:
> p | i | f
> ---+---+---
> 0 | 2 | 1
> (1 row)
>
> I've reproduced this with 15.4 and 16.0 using the official docker images,
> specifically `postgres/15-alpine` and `postgres/16-alpine`. I haven't
> tested
> older versions as my application requires features new to 15.x. I've tried
> with both list and hash partitioning, and it reproduces with both. I can
> only reproduce it with self-referential foreign keys and only with
> partitioned tables.
>
> I discovered this with a slightly more complex situation involving an `on
> delete set null` clause in the FK, but as seen above that is not required
> to
> reproduce the issue.
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
@ 2024-07-12 12:36 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2024-07-12 12:36 UTC (permalink / raw)
To: fastcat@gmail.com; pgsql-bugs@lists.postgresql.org
Hello, I hadn't noticed this report before; I'll have a look at it
soon.
On 2023-Oct-13, PG Bug reporting form wrote:
> When a partitioned table has a self-referential foreign key, I'm finding
> that the key is not enforced during deletes from the table.
Hmm, yeah, I broke this in this commit
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Branch: master Release: REL_16_BR [614a406b4] 2022-10-07 19:37:48 +0200
Branch: REL_15_STABLE Release: REL_15_0 [6083132ab] 2022-10-07 19:37:48 +0200
Branch: REL_14_STABLE Release: REL_14_6 [483d26930] 2022-10-07 19:37:48 +0200
Branch: REL_13_STABLE Release: REL_13_9 [7d520e68e] 2022-10-07 19:37:48 +0200
Branch: REL_12_STABLE Release: REL_12_13 [669803af0] 2022-10-07 19:37:48 +0200
Fix self-referencing foreign keys with partitioned tables
Clearly this area needs a lot more work.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"No nos atrevemos a muchas cosas porque son difíciles,
pero son difíciles porque no nos atrevemos a hacerlas" (Séneca)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-07-12 12:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-13 14:26 BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes PG Bug reporting form <noreply@postgresql.org>
2024-01-24 17:52 ` Matthew Gabeler-Lee <fastcat@gmail.com>
2024-07-12 12:36 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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