Dropping PostgreSQL partition is blocked as long as a client is reading data from any partition
I have a simple partitioned table in PostgreSQL 14, like this:
create table entity ( dataset_id integer not null references dataset (id), ... ) partition by list (dataset_id);
create table entity_1
like entity including constraints including defaults,
constraint entity_1_pkey primary key (entity_id),
alter table entity attach partition entity_1 for values in (1);
The reason for creating a partition a partition "detached" first and then attaching it in a separate statement is to avoid taking an exclusive lock on the parent table (
entity), which would block creating a partition as long as a client was reading from any other partition. This was the solution suggested by Laurenz Albe in https://stackoverflow.com/a/67016755/1536933 and it works great for creating partitions.
Unfortunately, I have the same problem when dropping partitions: as long as a client is reading data from any partition I cannot drop any other partition: not only is
drop table entity_1blocked, but even
alter table entity detach partition entity_1 concurrentlyis blocked! I cannot see any relevant lock in
pg_locksfor the "detach concurrently" statement (nothing with
granted=false), but the pgAdmin dashboard shows
Wait event: Lock: virtualxidand
Blocking PIDs: (pid of the reading process)
What can I do to drop partitions while data is being read from other partitions? Surely this should be possible?
ALTER TABLE ... DETACH PARTITION CONCURRENTLYis the best tool you have. Yes, it will wait until all transactions that read the table are done, but that shouldn't be a problem, because concurrent reads are not blocked at any time. Simply wait until the reading transactions are done (all transactions must end some day), and you are good.
Note that you need not wait until there is no active transaction on the table: you only have to wait until all transactions that were active when you started
ALTER TABLEare finished.