Should a partition be detached before dropping?

user_11148169 0
Viewed 1283

I'm using PostgreSQL 12, in which there is a partitioned table. This table has old partitions that need to be deleted. I've seen the code where the old partitions are firstly detached and only then dropped:

ALTER TABLE partitioned_table DETACH PARTITION partitioned_table_1;
DROP TABLE partitioned_table_1;

Is there any reason to detach a partition before dropping? Does just dropping a partition without detaching impact on other queries to a database?

partitioning postgresql
1 Answers

from the manual.

  • DROP TABLE partitioned_table_1; means drop table. ACCESS EXCLUSIVE lock on the parent table.

  • ALTER TABLE partitioned_table DETACH PARTITION partitioned_table_1; means that partitioned_table_1 will still exists. ACCESS EXCLUSIVE lock on the parent table

The detached partition continues to exist as a standalone table, but no longer has any ties to the table from which it was detached.

In postgresql 14, DETACH PARTITION partitioned_table_1 CONCURRENTLY SHARE UPDATE EXCLUSIVE lock on the parent table. more info: https://www.postgresql.org/docs/12/sql-altertable.html
https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION https://www.postgresql.org/docs/current/ddl-partitioning.html

user_156034770
Related