I'd like to report behavior of dropping a partition that I find unexpected.
CREATE TABLE IF NOT EXISTS district (
id TEXT NOT NULL,
city_id TEXT NOT NULL REFERENCES city (id) ON DELETE CASCADE,
PRIMARY KEY (id, city_id)
) PARTITION BY LIST (city_id);
CREATE TABLE IF NOT EXISTS district_default PARTITION OF district DEFAULT;
CREATE TABLE IF NOT EXISTS street (
id TEXT,
city_id TEXT NOT NULL REFERENCES city (id) ON DELETE CASCADE,
district_id TEXT NOT NULL,
FOREIGN KEY (district_id, city_id) REFERENCES district (id, city_id) ON DELETE CASCADE,
PRIMARY KEY (id, city_id)
) PARTITION BY LIST (city_id);
CREATE TABLE IF NOT EXISTS street_default PARTITION OF street DEFAULT;
CREATE TABLE IF NOT EXISTS district_prague PARTITION OF district FOR VALUES IN ('Prague');
CREATE TABLE IF NOT EXISTS street_prague PARTITION OF street FOR VALUES IN ('Prague');
CREATE TABLE IF NOT EXISTS district_london PARTITION OF district FOR VALUES IN ('London');
CREATE TABLE IF NOT EXISTS street_london PARTITION OF street FOR VALUES IN ('London');
INSERT INTO district (city_id, id) VALUES ('Prague', 'Vinohrady');
INSERT INTO district (city_id, id) VALUES ('Prague', 'Troja');
INSERT INTO district (city_id, id) VALUES ('London', 'City of Westminster');
INSERT INTO district (city_id, id) VALUES ('London', 'Covent Garden');
INSERT INTO street (city_id, district_id, id) VALUES ('Prague', 'Vinohrady', 'Boleslavska');
INSERT INTO street (city_id, district_id, id) VALUES ('Prague', 'Troja', 'U trojskeho zamecku');
INSERT INTO street (city_id, district_id, id) VALUES ('London', 'City of Westminster', 'The mall');
INSERT INTO street (city_id, district_id, id) VALUES ('London', 'Covent Garden', 'Floral st.');
```
If we observe FK constraints on the partitioned table "street", we see the following: ```
street_city_id_fkey
street_district_id_city_id_fkey
street_district_id_city_id_fkey1 -- default
street_district_id_city_id_fkey2 -- prague
street_district_id_city_id_fkey3 -- london
```
However, when I drop a partition using the following command:
```sql
DROP TABLE IF EXISTS district_london CASCADE;
```
I observe that the foreign key constraints on the "street" table referencing other partitions are removed (including default one):
```
street_city_id_fkey -- the only FK left on "street"
```
The "street_district_id_city_id_fkey" constraint, which references partitioned table "district" is gone as well.
Is this correct behavior? Why does running CASCADE on one partition affect other partitions?
My goal is to drop all partitions for a particular value (e.g. "prague") without affecting others.
Thank you in advance!
Best regards,
Vladyslav
