From 3231fd0455220194d6f75323c83819a38fc0a382 Mon Sep 17 00:00:00 2001 From: Álvaro Herrera Date: Sat, 11 Oct 2025 20:30:12 +0200 Subject: Stop creating constraints during DETACH CONCURRENTLY Commit 71f4c8c6f74b (which implemented DETACH CONCURRENTLY) added code to create a separate table constraint when a table is detached concurrently, identical to the partition constraint, on the theory that such a constraint was needed in case the optimizer had constructed any query plans that depended on the constraint being there. However, that theory was apparently bogus because any such plans would be invalidated. For hash partitioning, those constraints are problematic, because their expressions reference the OID of the parent partitioned table, to which the detached table is no longer related; this causes all sorts of problems (such as inability of restoring a pg_dump of that table, and the table no longer working properly if the partitioned table is later dropped). We'd like to get rid of all those constraints. In fact, for branch master, do that -- no longer create any substitute constraints. However, out of fear that some users might somehow depend on these constraints for other partitioning strategies, for stable branches (back to 14, which added DETACH CONCURRENTLY), only do it for hash partitioning. (If you repeatedly DETACH CONCURRENTLY and then ATTACH a partition, then with this constraint addition you don't need to scan the table in the ATTACH step, which presumably is good. But if users really valued this feature, they would have requested that it worked for non-concurrent DETACH also.) Author: Haiyang Li Reported-by: Fei Changhong Reported-by: Haiyang Li Backpatch-through: 14 Discussion: https://postgr.es/m/18371-7fef49f63de13f02@postgresql.org Discussion: https://postgr.es/m/19070-781326347ade7c57@postgresql.org --- src/test/regress/expected/alter_table.out | 28 ++++++++-------------------- src/test/regress/sql/alter_table.sql | 15 +++++++++------ 2 files changed, 17 insertions(+), 26 deletions(-) (limited to 'src/test') diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index a08f115b0e5..5e98bbf2425 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -4470,27 +4470,15 @@ ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY; Partition key: RANGE (a) Number of partitions: 0 --- constraint should be created -\d part_rp - Table "public.part_rp" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | -Check constraints: - "part_rp_a_check" CHECK (a IS NOT NULL AND a >= 0 AND a < 100) - -CREATE TABLE part_rp100 PARTITION OF range_parted2 (CHECK (a>=123 AND a<133 AND a IS NOT NULL)) FOR VALUES FROM (100) to (200); -ALTER TABLE range_parted2 DETACH PARTITION part_rp100 CONCURRENTLY; --- redundant constraint should not be created -\d part_rp100 - Table "public.part_rp100" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | -Check constraints: - "part_rp100_a_check" CHECK (a >= 123 AND a < 133 AND a IS NOT NULL) - DROP TABLE range_parted2; +-- Test that hash partitions continue to work after they're concurrently +-- detached (bugs #18371, #19070) +CREATE TABLE hash_parted2 (a int) PARTITION BY HASH(a); +CREATE TABLE part_hp PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +ALTER TABLE hash_parted2 DETACH PARTITION part_hp CONCURRENTLY; +DROP TABLE hash_parted2; +INSERT INTO part_hp VALUES (1); +DROP TABLE part_hp; -- Check ALTER TABLE commands for partitioned tables and partitions -- cannot add/drop column to/from *only* the parent ALTER TABLE ONLY list_parted2 ADD COLUMN c int; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 90bf5c17682..417202430a5 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2826,14 +2826,17 @@ DROP TABLE part_rpd; -- works fine ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY; \d+ range_parted2 --- constraint should be created -\d part_rp -CREATE TABLE part_rp100 PARTITION OF range_parted2 (CHECK (a>=123 AND a<133 AND a IS NOT NULL)) FOR VALUES FROM (100) to (200); -ALTER TABLE range_parted2 DETACH PARTITION part_rp100 CONCURRENTLY; --- redundant constraint should not be created -\d part_rp100 DROP TABLE range_parted2; +-- Test that hash partitions continue to work after they're concurrently +-- detached (bugs #18371, #19070) +CREATE TABLE hash_parted2 (a int) PARTITION BY HASH(a); +CREATE TABLE part_hp PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 0); +ALTER TABLE hash_parted2 DETACH PARTITION part_hp CONCURRENTLY; +DROP TABLE hash_parted2; +INSERT INTO part_hp VALUES (1); +DROP TABLE part_hp; + -- Check ALTER TABLE commands for partitioned tables and partitions -- cannot add/drop column to/from *only* the parent -- cgit v1.2.3