pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
3+ messages / 3 participants
[nested] [flat]
* BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
@ 2021-03-01 17:06 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2021-03-01 17:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: alex.stilwell@macquarie.com
The following bug has been logged on the website:
Bug reference: 16908
Logged by: alex stilwell
Email address: alex.stilwell@macquarie.com
PostgreSQL version: 12.6
Operating system: Unix
Description:
Lets say I have the following PostgresSQL Tables:
```
CREATE TABLE measurement
(
city_id BIGSERIAL not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
ALTER TABLE measurement
ADD PRIMARY KEY (city_id, logdate);
CREATE TABLE measurement_drilldown
(
measurement_id BIGINT NOT NULL,
logdate date not null,
info_one int,
info_two int,
CONSTRAINT measurement_drilldown
FOREIGN KEY(measurement_id, logdate)
REFERENCES measurement(city_id, logdate)
) PARTITION BY RANGE(logdate);
```
I create 2 partitions on each table:
```
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE measurement_drilldown_y2006m02 PARTITION OF
measurement_drilldown
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_drilldown_y2006m03 PARTITION OF
measurement_drilldown
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
```
I then insert some simple data into each table within the partitions I made
before:
```
INSERT INTO measurement
VALUES (10, '2006-02-04', 35, 35);
INSERT INTO measurement
VALUES (11, '2006-02-07', 35, 35);
INSERT INTO measurement
VALUES (15, '2006-03-04', 322, 3335);
INSERT INTO measurement_drilldown
VALUES (10, '2006-02-04', 66, 66);
INSERT INTO measurement_drilldown
VALUES (15, '2006-03-04', 77, 77);
```
I now detach the February Partition in both measurement and
measurement_drilldown:
```
ALTER TABLE measurement_drilldown
DETACH PARTITION measurement_drilldown_y2006m02;
ALTER TABLE measurement
DETACH PARTITION measurement_y2006m02;
```
I can then reattach the partition of measurement_drilldown_y2006m02 whilst
the measurement partition is still detached - thus violating the FK
constraint on the measurement_drilldown table:
```
ALTER TABLE measurement_drilldown
ATTACH PARTITION measurement_drilldown_y2006m02
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
```
This results in a situation where the measurement_drilldown table contains
data with the foreign key constraint not enforced -
```
select * from measurement;
```
| city_id | logdate | peaktemp | unitsales |
|:---- |:------:| -----:| -----:|
| 15| 2006-03-04 | 322 | 3335 |
```
select * from measurement_drilldown;
```
| measurement_id | logdate | info_one | info_two |
|:---- |:------:| -----:| -----:|
| 10| 2006-02-04 | 66 | 66 |
| 15| 2006-03-04 | 77 | 77 |
So the '10' row which is in the Feb 2006 partition is now happily (according
to this), violating the FK constraint. Indeed, if I try to add an additional
record to measurement_drilldown, to reference the '11' value in measurement
(that we inserted and detached) -
```
INSERT INTO measurement_drilldown
VALUES (11, '2006-02-07', 88, 88);
```
This fails (as expected) with:
[23503] ERROR: insert or update on table "measurement_drilldown_y2006m02"
violates foreign key constraint "measurement_drilldown" Detail: Key
(measurement_id, logdate)=(11, 2006-02-07) is not present in table
"measurement".
So the question I have is, is this by design? If I try to detach a partition
from measurement, whilst the FK in measurement_drilldown exists, then it
would not let me. However, it seems it is possible to detach the FK
relationship, remove the parent and then rejoin the measurment_drilldown
partition? Should the FK constraints be re-checked prior to attaching a
partition?
Interestingly, if I were to try and remove the February partition directly
from measurement (without touching the drilldown table), it would fail -
saying that it would violate the FK constaints.
Best,
Alex
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
@ 2021-03-02 00:07 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2021-03-02 00:07 UTC (permalink / raw)
To: alex.stilwell@macquarie.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Alvaro Herrera <alvherre@alvh.no-ip.org>
On Tue, 2 Mar 2021 at 10:27, PG Bug reporting form
<noreply@postgresql.org> wrote:
> I can then reattach the partition of measurement_drilldown_y2006m02 whilst
> the measurement partition is still detached - thus violating the FK
> constraint on the measurement_drilldown table:
>
> ```
> ALTER TABLE measurement_drilldown
> ATTACH PARTITION measurement_drilldown_y2006m02
> FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
> ```
>
> This results in a situation where the measurement_drilldown table contains
> data with the foreign key constraint not enforced -
> So the question I have is, is this by design?
It looks like a bug to me.
Here's a simplifed test case:
create table p(a int primary key) partition by list(a);
create table p1 partition of p for values in(1);
create table z(a int primary key references p) partition by list(a);
create table z1 partition of z for values in(1);
insert into p values(1);
insert into z values(1);
alter table z detach partition z1;
alter table p detach partition p1;
alter table z attach partition z1 for values in(1); -- shouldn't be allowed.
But it is allowed as CloneFkReferencing() calls
tryAttachPartitionForeignKey() which checks for an existing foreign
key to the table. It happens to find one.
\d z1
Table "public.z1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
Indexes:
"z1_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
"z_a_fkey" FOREIGN KEY (a) REFERENCES p(a)
I imagine that this foreign key should have been removed when z1 was
detached from z. If it's not then since when we detach p1 from p, we
can only check currently attached partitions on referencing tables to
see if there would be any FK violations.
I've copied in Alvaro to see what his thoughts are.
David
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
@ 2021-04-30 17:56 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2021-04-30 17:56 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: alex.stilwell@macquarie.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 2021-Mar-02, David Rowley wrote:
> Here's a simplifed test case:
>
> create table p(a int primary key) partition by list(a);
> create table p1 partition of p for values in(1);
> create table z(a int primary key references p) partition by list(a);
> create table z1 partition of z for values in(1);
> insert into p values(1);
> insert into z values(1);
> alter table z detach partition z1;
> alter table p detach partition p1;
> alter table z attach partition z1 for values in(1); -- shouldn't be allowed.
Hmm, I agree that this is misbehaving. However ...
> But it is allowed as CloneFkReferencing() calls
> tryAttachPartitionForeignKey() which checks for an existing foreign
> key to the table. It happens to find one.
>
> \d z1
> Table "public.z1"
> Column | Type | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
> a | integer | | not null |
> Indexes:
> "z1_pkey" PRIMARY KEY, btree (a)
> Foreign-key constraints:
> "z_a_fkey" FOREIGN KEY (a) REFERENCES p(a)
>
> I imagine that this foreign key should have been removed when z1 was
> detached from z. If it's not then since when we detach p1 from p, we
> can only check currently attached partitions on referencing tables to
> see if there would be any FK violations.
... I'm not sure that we need to remove the FK from 'z1'; we know well
enough that the constraint still stands. The problem IMO occurs when we
detach p1 -- that's the step that causes the FK go wrong. I think that
DETACH step should throw the error that there are tables referencing the
values in the partition. This works correctly when 'z' is a plain
table, so what is different regarding the constraint in 'z1' (which is
now a plain table)? I'll have a look.
--
Álvaro Herrera 39°49'30"S 73°17'W
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-04-30 17:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-01 17:06 BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints? PG Bug reporting form <noreply@postgresql.org>
2021-03-02 00:07 ` David Rowley <dgrowleyml@gmail.com>
2021-04-30 17:56 ` 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