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?
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