From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: alex.stilwell@macquarie.com
Subject: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
Date: Mon, 01 Mar 2021 17:06:01 +0000
Message-ID: <16908-5b53ef9b31fb94c9@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, alex.stilwell@macquarie.com
Subject: Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
In-Reply-To: <16908-5b53ef9b31fb94c9@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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