pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Stop creating constraints during DETACH CONCURRENTLY 6+ messages / 1 participants [nested] [flat]
* pgsql: Stop creating constraints during DETACH CONCURRENTLY @ 2025-10-11 18:31 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-10-11 18:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 <mohen.lhy@alibaba-inc.com> Reported-by: Fei Changhong <feichanghong@qq.com> Reported-by: Haiyang Li <mohen.lhy@alibaba-inc.com> Backpatch-through: 14 Discussion: https://postgr.es/m/18371-7fef49f63de13f02@postgresql.org Discussion: https://postgr.es/m/19070-781326347ade7c57@postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/3231fd0455220194d6f75323c83819a38fc0a382 Modified Files -------------- src/backend/commands/tablecmds.c | 50 ------------------------------- src/test/regress/expected/alter_table.out | 28 +++++------------ src/test/regress/sql/alter_table.sql | 15 ++++++---- 3 files changed, 17 insertions(+), 76 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Stop creating constraints during DETACH CONCURRENTLY @ 2025-10-11 18:31 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-10-11 18:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 <mohen.lhy@alibaba-inc.com> Reported-by: Fei Changhong <feichanghong@qq.com> Reported-by: Haiyang Li <mohen.lhy@alibaba-inc.com> Backpatch-through: 14 Discussion: https://postgr.es/m/18371-7fef49f63de13f02@postgresql.org Discussion: https://postgr.es/m/19070-781326347ade7c57@postgresql.org Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/08c037dff96103f607d30c487d11d3bf43a531ea Modified Files -------------- src/backend/commands/tablecmds.c | 14 +++++++++----- src/test/regress/expected/alter_table.out | 8 ++++++++ src/test/regress/sql/alter_table.sql | 9 +++++++++ 3 files changed, 26 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Stop creating constraints during DETACH CONCURRENTLY @ 2025-10-11 18:31 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-10-11 18:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 <mohen.lhy@alibaba-inc.com> Reported-by: Fei Changhong <feichanghong@qq.com> Reported-by: Haiyang Li <mohen.lhy@alibaba-inc.com> Backpatch-through: 14 Discussion: https://postgr.es/m/18371-7fef49f63de13f02@postgresql.org Discussion: https://postgr.es/m/19070-781326347ade7c57@postgresql.org Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/ea06f97eeb335ca8dd70be5f8c11a32ec4f3eb12 Modified Files -------------- src/backend/commands/tablecmds.c | 14 +++++++++----- src/test/regress/expected/alter_table.out | 8 ++++++++ src/test/regress/sql/alter_table.sql | 9 +++++++++ 3 files changed, 26 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Stop creating constraints during DETACH CONCURRENTLY @ 2025-10-11 18:31 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-10-11 18:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 <mohen.lhy@alibaba-inc.com> Reported-by: Fei Changhong <feichanghong@qq.com> Reported-by: Haiyang Li <mohen.lhy@alibaba-inc.com> Backpatch-through: 14 Discussion: https://postgr.es/m/18371-7fef49f63de13f02@postgresql.org Discussion: https://postgr.es/m/19070-781326347ade7c57@postgresql.org Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b835759ec7e8357c341f051df91168c04f032025 Modified Files -------------- src/backend/commands/tablecmds.c | 14 +++++++++----- src/test/regress/expected/alter_table.out | 8 ++++++++ src/test/regress/sql/alter_table.sql | 9 +++++++++ 3 files changed, 26 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Stop creating constraints during DETACH CONCURRENTLY @ 2025-10-11 18:31 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-10-11 18:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 <mohen.lhy@alibaba-inc.com> Reported-by: Fei Changhong <feichanghong@qq.com> Reported-by: Haiyang Li <mohen.lhy@alibaba-inc.com> Backpatch-through: 14 Discussion: https://postgr.es/m/18371-7fef49f63de13f02@postgresql.org Discussion: https://postgr.es/m/19070-781326347ade7c57@postgresql.org Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/bdae98495660439655f1c89fb550a75d9ffb10ef Modified Files -------------- src/backend/commands/tablecmds.c | 14 +++++++++----- src/test/regress/expected/alter_table.out | 8 ++++++++ src/test/regress/sql/alter_table.sql | 9 +++++++++ 3 files changed, 26 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Stop creating constraints during DETACH CONCURRENTLY @ 2025-10-11 18:31 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-10-11 18:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 <mohen.lhy@alibaba-inc.com> Reported-by: Fei Changhong <feichanghong@qq.com> Reported-by: Haiyang Li <mohen.lhy@alibaba-inc.com> Backpatch-through: 14 Discussion: https://postgr.es/m/18371-7fef49f63de13f02@postgresql.org Discussion: https://postgr.es/m/19070-781326347ade7c57@postgresql.org Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b753be38a4a239b7cd4d2c289d8737c955c46f6f Modified Files -------------- src/backend/commands/tablecmds.c | 14 +++++++++----- src/test/regress/expected/alter_table.out | 8 ++++++++ src/test/regress/sql/alter_table.sql | 9 +++++++++ 3 files changed, 26 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-10-11 18:31 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-11 18:31 pgsql: Stop creating constraints during DETACH CONCURRENTLY Álvaro Herrera <alvherre@kurilemu.de> 2025-10-11 18:31 pgsql: Stop creating constraints during DETACH CONCURRENTLY Álvaro Herrera <alvherre@kurilemu.de> 2025-10-11 18:31 pgsql: Stop creating constraints during DETACH CONCURRENTLY Álvaro Herrera <alvherre@kurilemu.de> 2025-10-11 18:31 pgsql: Stop creating constraints during DETACH CONCURRENTLY Álvaro Herrera <alvherre@kurilemu.de> 2025-10-11 18:31 pgsql: Stop creating constraints during DETACH CONCURRENTLY Álvaro Herrera <alvherre@kurilemu.de> 2025-10-11 18:31 pgsql: Stop creating constraints during DETACH CONCURRENTLY Álvaro Herrera <alvherre@kurilemu.de>
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