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
Last activity: Jun 25, 2024, 02:37 PM