BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?

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".
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