Sample Header Ad - 728x90

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

1 vote
1 answer
5824 views
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 "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?
Asked by EM0 (250 rep)
May 31, 2022, 02:08 PM
Last activity: Jun 25, 2024, 02:37 PM