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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply