Thread: attach partition to parent table with default partition accessexclusive
attach partition to parent table with default partition accessexclusive
Hi ,
We have a table wbxdata (xxxx…) partition by range(starttime) , and have a default partition, now we want to add a new partition and move the data from default partition to new added partition.
Begin;
Alter table only wbxdata … detach partition wbxdata_pdefault; ---from here, accessexclusive lock on wbxdata_pdefault default partition, and it block all query on parent table wbxdata.
Insert into wbxdata_p2305 select * from wbxdata_pdefault where starttime >= ‘2023-05-01 00:00:00’::date and starttime < ‘2023-06-01 00:00:00’;
Delete from wbxdata_pdefault where starttime >= ‘2023-05-01 00:00:00’::date and starttime < ‘2023-06-01 00:00:00’;
Alter table only wbxdata attach partition wbxdata_p2305 for values from ‘‘2023-05-01 00:00:00’ TO ‘2023-06-01 00:00:00’;
Alter table attach default partition;
End;
Is it expected to see all query even select on parent table wbxdata got blocked , when detach default partition and attach default partition ? Postgresql 14.7 version.
Thanks,
James
On Wed, 2023-04-12 at 03:03 +0000, James Pang (chaolpan) wrote: > We have a table wbxdata (xxxx…) partition by range(starttime) , and have a > default partition, now we want to add a new partition and move the data from > default partition to new added partition. > > Begin; > Alter table only wbxdata … detach partition wbxdata_pdefault; ---from here, accessexclusive lock on wbxdata_pdefaultdefault partition, and it block all query on parent table wbxdata. > Insert into wbxdata_p2305 select * from wbxdata_pdefault where starttime >= ‘2023-05-01 00:00:00’::date and starttime< ‘2023-06-01 00:00:00’; > Delete from wbxdata_pdefault where starttime >= ‘2023-05-01 00:00:00’::date and starttime < ‘2023-06-01 00:00:00’; > Alter table only wbxdata attach partition wbxdata_p2305 for values from ‘‘2023-05-01 00:00:00’ TO ‘2023-06-01 00:00:00’; > Alter table attach default partition; > End; > Is it expected to see all query even select on parent table wbxdata got blocked , > when detach default partition and attach default partition ? > Postgresql 14.7 version. Yes, that is expected. I recommend not to use a default partition if you want to attach more partitions later. Yours, Laurenz Albe