Dropping PostgreSQL partition is blocked as long as a client is reading data from any partition

[zymir]

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_1 blocked, but even alter table entity detach partition entity_1 concurrently is blocked! I cannot see any relevant lock in pg_locks for the "detach concurrently" statement (nothing with granted=false), but the pgAdmin dashboard shows Wait event: Lock: virtualxid and 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?

[obi]

ALTER TABLE ... DETACH PARTITION CONCURRENTLY is 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 TABLE are finished.