Dropping partition with CASCADE drops constraints on partitioned table

From: Vladyslav Hutych <vladyslav(dot)hutych(at)ipfabric(dot)io>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: Petr Bruna <petr(dot)bruna(at)ipfabric(dot)io>, Jan Vojacek <jan(dot)vojacek(at)ipfabric(dot)io>
Subject: Dropping partition with CASCADE drops constraints on partitioned table
Date: 2024-11-28 12:03:56
Message-ID: DU0PR08MB9027634E94DA0BA372C5FB308D292@DU0PR08MB9027.eurprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

I'd like to report behavior of dropping a partition that I find unexpected.

Example DDL:
```sql
CREATE TABLE IF NOT EXISTS city (
id TEXT PRIMARY KEY
);
INSERT INTO city (id) VALUES ('Prague');
INSERT INTO city (id) VALUES ('London');

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Thomas Munro 2024-11-28 12:22:49 Re: BUG #18711: Attempting a connection with a database name longer than 63 characters now fails
Previous Message Bertrand Drouvot 2024-11-28 11:27:02 Re: BUG #18728: Inconsistency between pg_wait_events.name and pg_stat_activity.wait_event for LWLocks